-
How to create a summary out of 2 columns
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
-
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.
-
Could you utilize a pivot table to summarize the information?
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules