PDA

View Full Version : How to create a summary out of 2 columns



Audax
04-24-2007, 09:35 AM
:hi: Hi Guys
Please somebody can help me?

Assuming I have 2 columns, let's say Column C with 50 names from top to bottom and Column D with 50 Amounts, one to each name. There could be names repeated among the 50 names. I want to create a "Summary", say in column E and F with E containing the names and column F with a Summary of amounts put together according to the names in E. Below an example with 7 names and respectives amounts and the result in Columns E and F.

C D E F
1 Mike 300 Joe 400
2 Tony 600 Mike 675
3 Joe 300 Tony 950
4 Tony 350
5 Mike 125
6 Mike 250
7 Joe 100

Using VBA is there any way to solve this problem?

Thank you

Shazam
04-24-2007, 10:49 AM
I know you ask for a vba solution but here is a formula solution. Input formula in cell F2 and copy down.

=LARGE(IF($D$2:$D$30<>"",IF(MATCH($C$2:$C$30,$C$2:$C$30,0)=ROW($C$2:$C$30)-ROW($C$2)+1,SUMIF($C$2:$C$30,$C$2:$C$30,$D$2:$D$30))),ROWS(F$2:F2))

Input formula in cell E2 and copy down.

=INDEX($C$2:$C$30,SMALL(IF($C$2:$C$30<>"",IF(MATCH($C$2:$C$30,$C$2:$C$30,0)=ROW($C$2:$C$30)-ROW($C$2)+1,IF(SUMIF($C$2:$C$30,$C$2:$C$30,$D$2:$D$30)=F2,ROW($C$2:$C$30)-ROW($C$2)+1))),COUNTIF($F$2:F2,F2)))

Both formulas are arrays need to hold down:

Ctrl,Shift,Enter

Look at the sample below.

feathers212
04-24-2007, 12:27 PM
Could you utilize a pivot table to summarize the information?