An easy macro needed!

[gameover]

Hi All,

I have a datasheet which have 2 columns Col 1 has Customers name and Col 2 has been the cost spent by him. e.g.

Col 1 Row 1: Cathy Col 2 Row 1: 34
Col 1 Row 2: Cathy Col 2 Row 2: 0
Col 1 Row 3: Cathy Col 2 Row 3: -9
Col 1 Row 4: Cathy Col 2 Row 4:

Col 1 Row 5: James Col 2 Row 5:

Col 1 Row 6: Mike Col 2 Row 6: 30
Col 1 Row 7: Mike Col 2 Row 7: 20


and so on.....

I need a macro which takes all the name put it in another sheet with total amount sent. e.g. in 2nd sheet it should look like:

Col 1 Row 1: Cathy Col 2 Row 1: 25

Col 1 Row 2: James Col 2 Row 2:

Col 1 Row 3: Mike Col 2 Row 3: 50

The values which are not present should remain blank only and 0 should not be put there.

I hope I have made clear myself but if you have any questions please revert.

Thanks to all of you in advance!!!!

 

[onlyadrafter]

Hello,

have you looked at pivot tables?

 

[ravishankar]

Hi
You can use data filter to get unique names. list them in col A of sheet2. insert the formula
=sumif(Sheet1!A1:A50,A1,Sheet1!B1:B50) drag the formula till the end of col A.Change the range to suit yourself
Ravi

 

[gameover]

Hi Ravi,

Thanks for the answer and sorry for replying so late!! I was covered up with some other work!! I have tried your formula and it seems not to be working. Can you please elaborate a little more where should I put the formula in sheet 1 or sheet 2?

Thanks again for your help!!!

 

[gameover]

Hey, I have tried pivot table and it worked!!! Thanks again for your help!!! But Ravi please do tell me about that formula!!!!

 

[Weaver]

What Ravi is suggesting requires you to copy and paste your list of names into another sheet, then use data -> filter -> advanced filter to create a list of non duplicated entries elsewhere on the sheet. Ideally you'd make this new list into column A and then insert Ravi's formula in column B all the way down your list.

Using this method can create neater and more easily formatted reports but if you've got what you need from the pivot then I'd save this technique for another time.

 

[ravishankar]

Hi
The formula will go to sheet 2 col B (customer name will be in col A-sheet2) and formula pulls his/her expense from sheet1.
Ravi