I have a large excel file made up of invoiced amounts. I need to make a MUS selection on absolute (abs) values; my issue is the total abs value is really big because there are duplicate amounts/items of the same abs value (see pic). As an example, I want to get rid of the items that net to zero (red font) and keep whichever value remains (green highlight)...does anyone have a solution? My population is roughly 500k rows so simply highlighting and deleting isn't efficient.

Post Photo
like
Posting as :
works at
You are currently posting as works at

Can you make some dummy data for the columns to the left? 500K rows using just formula means you have to be pretty strategic with your approach. Here’s how I would do it.

Assuming the left hand side has a column that names the row, eg unique client name…
Add a countifs formula that counts the number of times the client name has come up. In P2, =Countifs(a$2:a2, a2) . Pay special attention to the anchor. This will cumulatively count the number of times that name has uniquely shown up. 1, 2, 3… times. Then add add a new column in Q2 with an If statement, =if(p2=1, abs(l2),””)

If you can show more data (dummy data), I can provide a better solution.

like

Will the values that you want to net off always follow sequentially in the next row?

Post Photo
Post Photo

500k rows of data though... I've got a messy power query solution that your laptop would process in a fraction of the time.

A pivot table to get rid of the amts that net and then do this formula thing

Related Posts

Any advice for final round interviews for PE associate roles? Any unique questions you’ve seen? Any tips on how to standout?

like

Hi Folks , ey hire for technical domain and then assign non technical projects. Does it happens with everyone or some people ?

And is there any way to shorten the 3 months notice period in ey ?

like
like

Do Optum offers some relocation benefits. Like inital days staying at some guest house/hotels.?
If yes then to whom we have to reach out?

like

I am expecting an offer from Master Card for Senior Software Engineer position in O Fallon MO. Please let me know what kind of base salary I can expect with 11 yrs experience . I don’t wanna undersell but also trying to avoid being aggressive but quote a decent number which will be achievable. Any suggestions will be appreciated. Is 125k $ base a fair value for or I can pitch higher? They have a 15 to 20 % bonus on top of base pay and a 10% match on 401k which the recruiter keep highlighting.

like

Anyone make the switch out to IT type work? Just not enjoying the BS in accounting and want something more chill.

like

When did you decide you wanted to be a police officer?

like

Made it through the mothers, fathers, and in laws Christmas parties without any fighting, drunken yelling, or political discussion. Best Christmas gift of all.

like

what is the pay range for role - manager 1 assurance at pwc sdc kolkata?i have 6.5 yoe and joined at 21lpa + 10% vp, four months back. managers from other big 4 start around 28lpa. does sdc kolkata pay m1 this much only or have they underpaid me? i am considering dropping paper and looking out again.

like

Have been offered 07B grade in ibm..
Wat will be the roles and responsibilities.?
Stack : Oracle ERP
YOE : 7.5

like
like

Has anyone seen the changes to United’s loyalty program? Any thoughts on the PlusPoints or new qualifying rules?

What are your thoughts on Selfmade (Netflix) as a viewer and as a producer/marketer/creator?

Post Photo
like

Has anyone made a move from a “cushy” lifestyle to move to NYC? I’ve had a desire to move for a while but the logical part of my brain is holding back. Also insight into PwC NYM would be great!

like

With the way the economy seems to be going/progressing, what do you see as the future of fintech? How will it be impacted by a potential economic downturn?

like

Hi everyone,
I have a fully furnished room in an already set and functional flat to sublet. It is located in sector 38 gurgaon, near Medanta.
PS: it’s a girls flat

Hi fishes, any openings for SAP consultant in American Express in India

like

I feel like I'm stuck in limbo.

Not enough experience to become a Senior designer, but too much to be a junior.

And there hardly any intermediate roles out there.

like

Hi fishes, DM for refferal.

Business Systems Analyst - Cloud Renewal Operations
Location: Lowell - Massachusetts - United States, Weston - Florida - United States
Function:: Customer Success & Renewals

Post Photo

Additional Posts in Excel Genius

What’s the keyboard shortcut to get into the “insert options” dialogue when you’ve inserted a new row with Ctrl+Shift+Plus?

like

Hello. Quick question about formatting. I have an excel sheet with bunch of negative numbers, but some are percentage values and some are dollar values. Is there a way to make all of them have a parenthesis for being negative while keeping them still percentage and dollar value separately or do I have to target them separately?

like

HELP!
I have raw sales data on one tab, which includes a sales rep ID number. My second tab has that sales rep ID number in column 1 and the rep name in column 2. How can I get the rep name added to the correct line items in the raw data?

For a linear regression analysis… the equation of a line is Y=MX+B, where M is the slope. So if I’m measuring a line across 365 days and M=.2, can I say that I have increased production by 20% over a year. Or is production increasing 20% each day?

like

Does anyone know what purple tables mean in Excel?

I know about green and orange, but never saw purple before and trying to figure it out.

likefunny

Hey, I have been tasked with teaching some of my coworkers excel from the very basics to more advanced. Does anyone have recommendations on the skills to teach them about? Thank you all in advance!

helpful

How can I add multiple characteristics within one cell and make sure that a pivot table can read both values independently if needed? E.g., I have a column titled “color” and in one cell I write “red” and the other “red, blue”. I’d like a pivot table / formula to be able to count that there are technically two “reds” in this data set. Any tips?

like

How do I look up values from a column in a row? E.g. Values in A1:A6 in B2:F2?

like

Is there anything similar to the INDIRECT function that I can use to build a formula reference to another workbook? INDIRECT creates errors when the referenced workbook is closed..

like

Anybody use a personal computer for work? How's your experience been, any horror stories/watch-outs?

like

Vlook up isn't recognizing a couple of cells even though it's feeding off a pivot and when I check with an " if =" it comes up as being the same.
Any advice on what I need to check?
Thanks

like

I’m helping my GF with excel and need help lol. She works at a hospital and is trying to make an excel schedule for doctors to reserve rooms. She wants drop downs for days of the week (column K) and also different drop down menus in column P for the reserved time slots.

Is it possible to do this with two sets of drop downs (days of week + time slots) and not use index match here? She could make 5 tables for each day of the week, but prefers one table that can be easily navigated by doctors.

Post Photo
like

Best free data sites to tap into to build a city analysis model? Basically I want to "discover" cities with certain dimensions, like: population, elevation, climate, and proximity to other things (ski resorts, particularly)?

like

Best way to lookup a value with multiple criteria across rows and Columns, for 3 criteria I usually do sum product. Any other tips?

like

Recently made a job switch and have been using a lot more excel. Any free resources to help build a solid foundation / fee based courses to help improve my skills?

like

After I set a print area, how do I showcase the page 1 - page x in the excel sheet and have the background be grey?

Client sent over a 90 MB model this evening. Took 10 minutes to open and a fraction of that to crash. Can’t wait for tomorrow 😭

like

Does 2-finger touchpad scroll not work on Excel sheets anymore? I used to be able to swipe quickly on my laptop's touchpad to navigate sheets but now it doesn't work. Based on the handful of posts I've read online about it, the problem seems unresolved. Has anyone dealt with the same issue and fixed it?

Need help building an electric schedule for a production schedule with a Gantt chart style view (sort of)

Currently have an excel list with resource (production line), start date and time, and then also the name of the item being produced.

Any ideas how to tackle this??

I’m thinking a stacked bar chart with the vertical categories to be my resources and horizontal the date/time and then each individual bar to be the name of the item being produced.

Thank you for the help Excel Gods 🙏

like

New to Fishbowl?

Download the Fishbowl app to
unlock all discussions on Fishbowl.
That was just a preview…
Sign Up to see all discussions
  • Discover what it’s like to work at companies from real professionals
  • Get candid advice from people in your field in a safe space
  • Chat and network with other professionals in your field
Sign up in seconds to unlock all discussions on Fishbowl.

Already a user?
Login here

Share

Embed this post

Copy and paste embed code on your site

Preview

Download the
Fishbowl app

See what’s happening in your industry
from the palm of your hand.

A phone with Fishbowl app

Scan your QR code to download
Fishbowl app on your mobile

Download app

Sign up for free to view this conversation on Fishbowl

By continuing you agree to Terms of Use and Privacy Policy

Already have an account? Log in

Sign up for free to continue using Fishbowl

By continuing you agree to Terms of Use(New) and Privacy Policy(New)
Messaging rates may apply

Already have an account? Log in

For account settings, visit Fishbowl on Desktop Browser or

General

Legal