combining data using a unique identifier

[YUAN_DUMALE]

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 ID	Company	Employee Name	Applicable Month	Claim Type	Amount Paid
1235	test	Doe, John	April	Education	100.00
1235	test	Murdock, Frank	April	General Utilities	987.00
1241	test	Santos, May	April	Wellness & Welfare	500.00
1234	test	Cruz, Juan	April	Travel and Holidays	599.00
1241	test	Santos, May	April	Travel and Holidays	658.00

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

Output should be:

Employee ID	Company	Employee Name	Applicable Month	Claim Type	Amount Paid
1235	test	Doe, John	April	Education	1087.00
1241	test	Santos, May	April	Wellness & Welfare	1158.00
1234	test	Cruz, Juan	April	Travel and Holidays	599.00

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

Needed to use macro for this one.

Thanks in advance! ^_^

 

[radian89]

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]

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]

You can prepare a summary sheet in Sheet2 which will be automatically updated when you will enter the amounts in others sheet.

Employee ID	Company	Employee Name	Applicable Month	Claim Type	Amount Paid
1235	test	Doe, John	April	Education	=SUMIFS(Sheet1!F2:F6,Sheet1!A2:A6,Sheet1!A2)
1241	test	Santos, May	April	Wellness & Welfare	=SUMIFS(Sheet1!F3:F7,Sheet1!A3:A7,Sheet1!A3)
1234	test	Cruz, Juan	April	Travel and Holidays	=SUMIFS(Sheet1!F4:F8,Sheet1!A4:A8,Sheet1!A4)

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

 

[YUAN_DUMALE]

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]

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]

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.

^

Hi Yuan,

try to impress your boss with this. an easy work, for reporting & pivoting. here's my example with dummy data
http://s000.tinyupload.com/?file_id=98144562147089843503

thanks a lot,

Adrian