Summing appropriate cells if last entry of a unique id is encountered

[chicity26]

I need some help summing costs for a unique ID (which can have 1 or multiple B numbers). I will walk through the scenarios to help understand what I am looking for.

ID = 1: The B Number is the same for entries of ID =1. Thus where ID = 1 and Last is indicated, Cost = 10.

ID = 2: The B Number changes four times. In cell G20 (Last of all ID =2) Cost = 20+30+40+30 = 120. You are basically summing at each instance the B Number changes.

ID = 3: Cell G24 = 100 +30 (two instances of B Number changing)

ID = 4: B Number is always the same. Cost = 50

ID = 5: B number changes 3 times, Cost = 50+120+140 = 310

In essence, the idea is that if the ID matches for all rows of particular client, the total cost = individual cost. However, if the ID changes multiple times for a single client, the total cost becomes the sum of changed costs, but not the entire column, just summing at each instance it changes. And this summed cost must be entered whenever we see the last instance of a unique ID (This is indicated using Column F).

The problem that I'm running into is that currently I am doing this manually with my actual data set (has nearly 200,000 rows). What excel function or VBA code could I write to automate this entire process?

Thanks for the help.

[ajryan88]

Hi,

Please take a look at the worksheet "AJRYAN88" in the attachment. Is this the sort of thing you're looking for?

Hope this helps

[chicity26]

Yes, that is what I am looking for.

[chicity26]

Quick thing though. The ID's I am working with are 6-8 digits long and vary. So I don't think using this particular method will work. Additionally, I am trying to get the values on the same sheet as the original data, in the appropriate cells.

[ajryan88]

This method will work for varying ID lengths, as it is only looking for an ID that isn't the same as the ID on the previous row.

As for getting the formula in the appropriate place, you will have to take the formulas that I have written and transfer them one at a time to the appropriate cells. The reason for this is because you have the cost in the same column, which I presume is manual entry, so copying the formula down the column would screw around with the cost values.

One alternative is to place the "Last" cost in the next column, and rewrite the formula so that it displays a blank value if "Not Last" and displays the appropriate cost if "Last". Let me know if you want me to implement this for you.

Thanks

[chicity26]

Yes, let's try the alternative. I am trying to work on it now.

[ajryan88]

No problems

[chicity26]

I am getting "Invalid ID" with my actual set and when I change the ID to an arbritrary number in the sample set. It seems to return "Invalid ID" for the last entry of each ID when I do this.

[ajryan88]

Can you please upload your workbook with the erroneous Invalid IDs?

[chicity26]

It happens when changing the ID.

[ajryan88]

Oh I see now. Well the formula is setup for IDs 1 to 5 as in your original post.

You will need to tell me how many IDs there are likely to be in total, what they actually are, and what the "Last" cost should be in each instance...

The formula will need to be rewritten for the new IDs

[chicity26]

Well there's over 2000 ID's and they are all very different, but I can't give those out, sorry.

Thanks for the help!

[ajryan88]

Hmmm. Is it fair to say that for ANY ID, the "Last" cost will just be the sum of the costs each time the B Number changes? Because if so, then a single formula will work and I don't need to know the IDs, and I can simplify the formula too...

[chicity26]

Yes, that is what I am looking for. It is that sum (for each unique ID) each time the B Number changes (but the sum is only at one instance, i.e. if you include the sum for B Number = 1 for a particular ID, you should not include it again.) You'll find that the cost is same for the same B Number, no matter what.

[ajryan88]

Here is a formula that produces the right results regardless of what the ID is. Please let me know if this doesn't work for your original data set

[chicity26]

Hey, the formula works great. I made a mistake myself, though. As I was going through the data, I encountered a situation where cost can be different for the same B Number. In those instances, I will just have to take the higher number and add it to any other values with different B Numbers or just have it return the higher number for the "Last" cost if no other B Numbers exist.

Thanks for all your incredible help!

[ajryan88]

No problems

That complicates things a little bit. I have had to add an extra column which returns Cost 1 if this is the max value for the specified B Number, and 0 otherwise. I then changed the Cost 2 formula to use this new column instead of the Cost 1 column.

I'm not sure how desirable this is for you, but the helper column could always be hidden anyway if it's an issue.

I hope this helps

[chicity26]

Great, thanks!