Multiple rows to single row by criteria

[Max Endoso]

Greetings everyone. I hope you can help me with this
problem. When I get my data from a mainframe query report
it looks like this:
Person Charge Amount
A 1 100.00
A 2 100.00
B 1 100.00
B 2 100.00

I need to format my report like this:
Person Charge Total Amount
A 1,2 200.00
B 1,2 200.00

Thank you in advance for your help.

 

[Craig]

This is on the assumption that each person has exactly two
entries;
Create a new sheet. Use the following formulae;
Cell A2 =sheet1!A2
Cell B2 =concatenate(sheet1!B2,",",sheet1!B3)
Cell C2 =sheet1!C2+sheet1!C3
Then copy row 2, leave a blank row(row 3) and paste into
row 4. Repeat paste ad inifium, leaving a blank row
between each.
When done, run a sort on column A to eliminate the blank
lines.

Good luck.

 

[Max Endoso]

Hi Craig, thank you for your response. Unfortunately, the
number of entries varies from person to person. Some have
more than the others. I hope you have another solution to
my problem.

Have a nice day.

 

[Guest]

Max,

I have a not very elegant but effective solution:

1. Make a Pivot table out of your data. Use persons as
rows and charges as columns. It will look like the
following:
A B C D F
1 1 2 3 ... Total
2 A 100 0 100 200
3 B 0 100 0 200
4 C 100 100 0 200

2. Assuming Total is in column F, you can type the
following formula in column G (wich will list all charge
codes):
=IF(B2<>0,B$1,"")&","&IF(C2<>0,C$1,"")&","&IF(D2<>0,D$1,"")
&","&IF(E2<>0,E$1,"")

Copy this formula down. You can delete unneeded columns
once you paste the data as values. (i.e. keep columns B, F
and G)

This approach could be useful if the number of charge
codes is small, let me know if that is not your case.

Regards,
Felipe