Sum and merge values based on 2 ID's

[SWEARB1]

Please help. I have to manage dozens of these files a day ranging from a few dozen rows to several thousand rows.

I need a macro that will add a specific value based upon another value at each change in another ID. Example below. There are columns at various places within this data that I didn't add to the example for simplicity. So I need to be able to easily make changes to account. The file must remain in the same format. Please please please help me. These files are killing me!!

For example: I need this

ID LN FN TF ALLC
11 Carrie Mariah A 33
11 Carrie Mariah S 33
11 Carrie Mariah F 34
22 Smith Will A 100
33 Underwood Carrie A 25
33 Underwood Carrie A 5
33 Underwood Carrie A 10
33 Underwood Carrie F 12
33 Underwood Carrie D 13
33 Underwood Carrie K 20
33 Underwood Carrie E 15

To look like:

11 Carrie Mariah A 37
11 Carrie Mariah S 33
22 Smith Will A 100
33 Underwood Carrie F 12
33 Underwood Carrie D 13
33 Underwood Carrie A 60
33 Underwood Carrie E 15

Thank you kindly
BS

[Solus Rankin]

Maybe I'm missing something but I'm struggling to discern your pattern. I can't tell what is summed, merged, or how it correlates to your sample workbook (workbook doesn't have headers).

[SWEARB1]

This is what I get for posting in such a hurry. Basically, each entry can have multiple lines and it's very random. The only thing set is that the ALLC column must add up to 100. The issue we have is that there can be multiple duplicates based on the TF column that need to be added together. So for example. Mariah Carrie has 3 rows 2 of which are designated with the TF = A. This means that both the ALLC need to be added and one of the rows must be deleted. Resulting in 2 rows, 1 with a TF = A and one TF = S. Another example would be Carrie Underwood. She has 7 rows but since 4 of those have a TF = A, then the ALLC should be added together resulting in only 1 TF A column, and a TF F, D and E column. Sorry, this is very convulated but there must be a fairly simplistic approach (preferably macro) to simplify this. I was going to use a pivot table but I couldn't get the format to remain exactly the same, there were always added rows or headers that blocked the file from uploading.

ID LN FN TF ALLC
11 Carrie Mariah A 33
11 Carrie Mariah S 33
11 Carrie Mariah A 34
22 Smith Will A 100
33 Underwood Carrie A 25
33 Underwood Carrie A 5
33 Underwood Carrie A 10
33 Underwood Carrie F 12
33 Underwood Carrie D 13
33 Underwood Carrie A 20
33 Underwood Carrie E 15

To look like:


ID LN FN TF ALLC
11 Carrie Mariah A 67
11 Carrie Mariah S 33
22 Smith Will A 100
33 Underwood Carrie A 60
33 Underwood Carrie F 12
33 Underwood Carrie D 13
33 Underwood Carrie E 15

[SWEARB1]

Sorry, failed to update the spreadsheet. Sheet one shows what a file will kinda look like when I receive it. Sheet two shows what I have to turn it into.

[Solus Rankin]

Try:

Please Login or Register  to view this content.

[Solus Rankin]

BTW Brittney only adds up to 90

[SWEARB1]

Sorry for the length of time inbetween comments. I just had time to run the macro. It deleted all duplicates for the names. The second time I ran the file, it deleted most people, but none of the ALLOC added up to 100 except for for the ones where there was only one entry and no duplicates.

This is what the file looks like when I receive it (All actual information below is made up, but the format is identical) Meaning the duplicates are based off of column I at each change in column D while adding value in column J where there exists duplicates in column I:
ENTRY 9 99999 1 MOSS MOSS 77777 15BTFZ15 1234 100
ENTRY 9 99999 3 BEN BEN 77777 15BTFZ15 1234 100
ENTRY 9 99999 2 KILE KILE 77777 TF15Z15 89 50
ENTRY 9 99999 2 KILE KILE 77777 T15BTF15 89 25
ENTRY 9 99999 2 KILE KILE 77777 WST15 2 25
ENTRY 9 99999 8 MEGAN MEGAN 77777 90LKP 94 15
ENTRY 9 99999 8 MEGAN MEGAN 77777 15BTFZ15 1234 30
ENTRY 9 99999 8 MEGAN MEGAN 77777 WST15 2 5
ENTRY 9 99999 8 MEGAN MEGAN 77777 15PX15 94 50


This is what the file kinda looks like when I am done:
ENTRY 9 99999 1 MOSS MOSS 77777 15BTFZ15 1234 100
ENTRY 9 99999 3 BEN BEN 77777 15BTFZ15 1234 100
ENTRY 9 99999 2 KILE KILE 77777 TF15Z15 89 75
ENTRY 9 99999 2 KILE KILE 77777 WST15 2 25
ENTRY 9 99999 8 MEGAN MEGAN 77777 90LKP 94 65
ENTRY 9 99999 8 MEGAN MEGAN 77777 15BTFZ15 1234 30
ENTRY 9 99999 8 MEGAN MEGAN 77777 WST15 2 5


This is what the file looks like after running the macro:
ENTRY 9 99999 1 MOSS MOSS 77777 15BTFZ15 1234 100
ENTRY 9 99999 3 BEN BEN 77777 15BTFZ15 1234 100
ENTRY 9 99999 2 KILE KILE 77777 WST15 2 25 0
ENTRY 9 99999 8 MEGAN MEGAN 77777 15PX15 94 50 0
0

Thank you for taking the time to help me with this.
BS

[AB33]

I think it would be easier if you could specify which columns do you want to use for comparison purposes.

If I look at sheet 1, it could be any column from A-K. Which of these columns need to match so as to add column L?

[SWEARB1]

I created a new spreadsheet from scratch that may help more. Sheet one has the headers as well as a brief description above as to what each column actually is. Sheet two shows what the file will look like after summing the duplicates while deleting the excess rows.

so what is actually needed?

We are converting people from one system to another and we need to bring over their allocation based on from/to codes. So, these people could have many codes assigned to them but it always must add up to 100. Often times with these conversions, FROM codes could convert to the same TO codes. Such as FROM codes AB and HU both converting to TO codes 2568. In this case, the allocation assigned to those to codes must be added and one of the rows must be deleted. So if the row containing AB has an allocation of 20 and the row containing HU has an allocation of 15, one of the rows should be deleted and the other row should have the allocation value of the 2 rows equaling 35. It's a little convoluted I know, but someone's gotta do it, and I was hoping a macro or something could help

Thanks again for helping with this

[AB33]

I used columns 5 and 11 to compare the duplicates.