Hi everyone, I have a data manipulation question that seems simple but I can't figure it out! I have a large list of properties which details how (%) the rental cost (Amount) should be allocated/split between various locations (LOC). The data looks like this:
A B C D E (D*B) F G H (G*B) I J K (J*B) ...
1 Property Amount LOC1 LOC1% LOC1Amount LOC2 LOC2% LOC2Amount LOC3 LOC3% LOC3Amount ...
2 Property 1 193.66
50% 96.83
30% 58.10
20% 38.73 ...
3 Property 4 600.00
20% 120.00
80% 480.00 ...
4 Property 5 593.22
100% 593.22 ...
... ...
I need the above data to be in the following format so that it can be uploaded into the accounting system (separate line for each Property/LOC combination):
Property LOC LOCAmount
Property 1 HK050 96.83
Property 1 LM020 58.10
Property 1 RU010 38.73
Property 4 HK050 120.00
Property 4 LM020 480.00
Property 5 HK050 593.22
Is there any way the original data can be converted to the above template without the use of macros (e.g. using pivot tables)? This is a monthly task and I'm trying to avoid the use of macros as the other members of my team don't know how to use macros in case the template needs updating in the future. Thanks for everyone's help!