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:

[TABLE="width: 710"]
<colgroup><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Employee ID[/TD]
[TD]Company[/TD]
[TD]Employee Name[/TD]
[TD]Applicable Month[/TD]
[TD]Claim Type[/TD]
[TD]Amount Paid[/TD]
[/TR]
[TR]
[TD="align: right"]1235[/TD]
[TD]test[/TD]
[TD]Doe, John[/TD]
[TD]April[/TD]
[TD]Education[/TD]
[TD="align: right"]100.00[/TD]
[/TR]
[TR]
[TD="align: right"]1235[/TD]
[TD]test[/TD]
[TD]Murdock, Frank[/TD]
[TD]April[/TD]
[TD]General Utilities[/TD]
[TD="align: right"]987.00[/TD]
[/TR]
[TR]
[TD="align: right"]1241[/TD]
[TD]test[/TD]
[TD]Santos, May[/TD]
[TD]April[/TD]
[TD]Wellness & Welfare[/TD]
[TD="align: right"]500.00[/TD]
[/TR]
[TR]
[TD="align: right"]1234[/TD]
[TD]test[/TD]
[TD]Cruz, Juan[/TD]
[TD]April[/TD]
[TD]Travel and Holidays[/TD]
[TD="align: right"]599.00[/TD]
[/TR]
[TR]
[TD="align: right"]1241[/TD]
[TD]test[/TD]
[TD]Santos, May[/TD]
[TD]April[/TD]
[TD]Travel and Holidays[/TD]
[TD="align: right"]658.00[/TD]
[/TR]
</tbody>[/TABLE]


Output should be:

[TABLE="width: 710"]
<colgroup><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Employee ID[/TD]
[TD]Company[/TD]
[TD]Employee Name[/TD]
[TD]Applicable Month[/TD]
[TD]Claim Type[/TD]
[TD]Amount Paid[/TD]
[/TR]
[TR]
[TD="align: right"]1235[/TD]
[TD]test[/TD]
[TD]Doe, John[/TD]
[TD]April[/TD]
[TD]Education[/TD]
[TD="align: right"]1087.00[/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"]1241[/TD]
[TD]test[/TD]
[TD]Santos, May[/TD]
[TD]April[/TD]
[TD]Wellness & Welfare[/TD]
[TD="align: right"]1158.00[/TD]
[/TR]
[TR]
[TD="align: right"]1234[/TD]
[TD]test[/TD]
[TD]Cruz, Juan[/TD]
[TD]April[/TD]
[TD]Travel and Holidays[/TD]
[TD="align: right"]599.00[/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[/TR]
</tbody>[/TABLE]

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.


[TABLE="width: 780"]
<colgroup><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Employee ID[/TD]
[TD]Company[/TD]
[TD]Employee Name[/TD]
[TD]Applicable Month[/TD]
[TD]Claim Type[/TD]
[TD]Amount Paid[/TD]
[/TR]
[TR]
[TD]1235[/TD]
[TD]test[/TD]
[TD]Doe, John[/TD]
[TD]April[/TD]
[TD]Education[/TD]
[TD]=SUMIFS(Sheet1!F2:F6,Sheet1!A2:A6,Sheet1!A2)[/TD]
[/TR]
[TR]
[TD]1241[/TD]
[TD]test[/TD]
[TD]Santos, May[/TD]
[TD]April[/TD]
[TD]Wellness & Welfare[/TD]
[TD]=SUMIFS(Sheet1!F3:F7,Sheet1!A3:A7,Sheet1!A3)[/TD]
[/TR]
[TR]
[TD]1234[/TD]
[TD]test[/TD]
[TD]Cruz, Juan[/TD]
[TD]April[/TD]
[TD]Travel and Holidays[/TD]
[TD]=SUMIFS(Sheet1!F4:F8,Sheet1!A4:A8,Sheet1!A4)[/TD]
[/TR]
</tbody>[/TABLE]

 

[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