combining data using a unique identifier

YUAN_DUMALE

New Member
Joined
Oct 23, 2014
Messages
11
Hi,

I am currently working on a report. And what i wanted to do was to add all the amount paid in one row per Employee ID.

Please refer below:

Employee IDCompanyEmployee NameApplicable MonthClaim TypeAmount Paid
1235testDoe, JohnAprilEducation100.00
1235testMurdock, FrankAprilGeneral Utilities987.00
1241testSantos, MayAprilWellness & Welfare500.00
1234testCruz, JuanAprilTravel and Holidays599.00
1241testSantos, MayAprilTravel and Holidays658.00

<colgroup><col><col><col><col><col><col></colgroup><tbody>
</tbody>


Output should be:

Employee IDCompanyEmployee NameApplicable MonthClaim TypeAmount Paid
1235testDoe, JohnAprilEducation1087.00
1241testSantos, MayAprilWellness & Welfare1158.00
1234testCruz, JuanAprilTravel and Holidays599.00

<colgroup><col><col><col><col><col><col></colgroup><tbody>
</tbody>

Needed to use macro for this one.

Thanks in advance! ^_^
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

radian89

Board Regular
Joined
Nov 12, 2015
Messages
112
Hi Yuan,

No need for using macro for this one. there's a lot of ways to do that

1. using pivot table, if you want make the table like that, use classic pivot table, you can also add slicer, to made filtering more fun
2. without pivot table, you can put only Employee ID, while field
- Company, employee name, applicable month, claim type, you can use VLOOKUP formula
- amount paid, you can use SUMIFS formula

i see in the example, there's 2 identical employee ID while the name is different, is that the current condition or it's just a dummy?

thanks
Adrian
 

YUAN_DUMALE

New Member
Joined
Oct 23, 2014
Messages
11
Hi Adrian,

Unfortunately, my boss is asking for something automated so I need to use macro since I wont be able to tell him to do a pivot every time he needs the report.

^
:(
 

mubashiraziz

Board Regular
Joined
Apr 2, 2009
Messages
175
You can prepare a summary sheet in Sheet2 which will be automatically updated when you will enter the amounts in others sheet.


Employee IDCompanyEmployee NameApplicable MonthClaim TypeAmount Paid
1235testDoe, JohnAprilEducation=SUMIFS(Sheet1!F2:F6,Sheet1!A2:A6,Sheet1!A2)
1241testSantos, MayAprilWellness & Welfare=SUMIFS(Sheet1!F3:F7,Sheet1!A3:A7,Sheet1!A3)
1234testCruz, JuanAprilTravel and Holidays=SUMIFS(Sheet1!F4:F8,Sheet1!A4:A8,Sheet1!A4)

<colgroup><col><col><col><col><col><col></colgroup><tbody>
</tbody>
 

YUAN_DUMALE

New Member
Joined
Oct 23, 2014
Messages
11

ADVERTISEMENT

Hi,

Im not quite sure if this can identify the Employee ID as a unique identifier. I tried to test it but it does not combine all the entries with the same Employee ID in one row.
 

mubashiraziz

Board Regular
Joined
Apr 2, 2009
Messages
175
Hi,

Im not quite sure if this can identify the Employee ID as a unique identifier. I tried to test it but it does not combine all the entries with the same Employee ID in one row.

Of course it will. If you want sum with condition of Employee ID then countif() will work but if you want to put condition on Month etc. as well then countifs() will work
 

radian89

Board Regular
Joined
Nov 12, 2015
Messages
112

Watch MrExcel Video

Forum statistics

Threads
1,130,310
Messages
5,641,450
Members
417,210
Latest member
rins

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
Top