An easy macro needed!

gameover

Active Member
Joined
Jan 12, 2009
Messages
292
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!!!!
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
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
 
Upvote 0
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!!!
 
Upvote 0
Hey, I have tried pivot table and it worked!!! Thanks again for your help!!! But Ravi please do tell me about that formula!!!!
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,953
Members
448,535
Latest member
alrossman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top