I have a big datasheet in Excel which includes customer/product/sales
columns. In a new sheet I would like to consolidate all sales per customer
and in another per product. Then I would like to show the top20 customers
resp. top20 products. What is the best way to do this in Excel?
Thanks for your help.
Gunther

Although you could do it with a SUMIF function however I do get mixed
results on occasion when using this. It seems when a list is unsorted
it misses records. I would make a sheet with the list of all the
customers in column A and add the following VBA:

I am making an assumption that the amount of sales is in column B of
the BigDatasheet.

Sub CustomerRun
Dim i as interger
Dim Cust as string

Cust = Sheets("Customer").Range("A" & i).Value
For i = 1 to 500 <======== or higher if there are more customers
If Cust = Sheets("BigDataSheet").Range("A" & i).Value Then
Sheets("BigDataSheet").Range("B" & i).Copy
Sheets("Customer").Range("B" & i).Selection.PasteSpecial
Paste:=xlPasteValues, Operation:=xlAdd
End If
Next i

and the same process could be used for products.

After you have these lists it is just a sorting or filter job to get
the Top 20


--
patrickcairns
------------------------------------------------------------------------
patrickcairns's Profile: http://www.excelforum.com/member.php...o&userid=31790
View this thread: http://www.excelforum.com/showthread...hreadid=515133