# An easy macro needed!

#### gameover

##### Active Member
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

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

##### Well-known Member
Hello,

have you looked at pivot tables?

#### ravishankar

##### Well-known Member
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

##### Active Member
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

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

#### Weaver

##### Well-known Member
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

##### Well-known Member
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

Replies
11
Views
329
Replies
2
Views
171
Replies
1
Views
207
Replies
4
Views
246
Replies
1
Views
307

1,195,619
Messages
6,010,736
Members
441,567
Latest member
Flitbee

### 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?

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