how to group records in a new worksheet
 

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

how to group records in a new worksheet
 

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