Mapping data from multiple rows into one

[pma.tang]

Hi guys,

I'm new to the forum so if I make any mistakes in my post then please feel free to let me know and I will endeavour to change it to comply with forum rules immediately.

Just to explain the problem a little:

Every month I need to consolidate numbers from 2 separate business entities. In Entity 1, Cost X and Cost Y will be classified in two separate cost codes, 'A' and 'B'. Whereas in Entity 2, Cost X and Cost Y will be classified together in 1 cost code, 'C'.

Currently I would just add together the two cost codes in entity 1 to get the equivalent in entity 2. However, cost codes in my company are ever changing and today it might be A + B = C but tomorrow it might be A + D = C. Therefore, I need a dynamic way of easily mapping the data by changing only the mapping fields and excel would automatically map the data for me.

I have attached a sample excel file just for you to get the gist of what I am currently doing.


Really appreciate any and all available help and suggestions.

How_To_Auto_Map.xlsx

[arlu1201]

It will be good if you explain with the help of your file. You said entity1 as 2 cost codes A & B, but i see 4, A B C and D.

Why dont you upload a sample output file to show us what you want the output to look like?

[pma.tang]

It will be good if you explain with the help of your file. You said entity1 as 2 cost codes A & B, but i see 4, A B C and D.

Why dont you upload a sample output file to show us what you want the output to look like?

Hi Arlette,

Thanks for your quick response.

Attached is an example file that I am actually currently using.
Entity 1 codes 202, 206 and 210 are collated under code 610001 in the reporting template.

You may see this relationship on the 'Map' worksheet.

Ideally what I would like is for that in the future, I would be able to adjust the values on the map sheet, and the 'Report' sheet would automatically sum up the correct numbers from the Entity 1 sheet.

I'm sorry if it is not too clear as I'm finding it a little difficult to explain myself fully. I hope looking at the file will help.

How_To_Auto_Map_Live.xlsx