Related Posts
Additional Posts in Excel Genius
New to Fishbowl?
Download the Fishbowl app to
unlock all discussions on Fishbowl.
unlock all discussions on Fishbowl.
This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.
Download the Fishbowl app to unlock all discussions on Fishbowl.
Copy and paste embed code on your site

Scan your QR code to download
Fishbowl app on your mobile

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.
Will the values that you want to net off always follow sequentially in the next row?
Boston Consulting Group 1
Boston Consulting Group 1
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