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: [TABLE="class: grid, width: 761, align: left"] <tbody>[TR] [TD][/TD] [TD]A[/TD] [TD]B[/TD] [TD]C[/TD] [TD]D[/TD] [TD]E (D*B)[/TD] [TD]F[/TD] [TD]G[/TD] [TD]H (G*B)[/TD] [TD]I[/TD] [TD]J[/TD] [TD]K (J*B)[/TD] [TD]...[/TD] [/TR] [TR] [TD="align: right"]1[/TD] [TD]
Property [/TD] [TD]
Amount [/TD] [TD]
LOC1 [/TD] [TD]
LOC1%[/TD] [TD]
LOC1Amount[/TD] [TD]
LOC2[/TD] [TD]
LOC2%[/TD] [TD]
LOC2Amount [/TD] [TD]
LOC3[/TD] [TD]
LOC3%[/TD] [TD]
LOC3Amount [/TD] [TD]...[/TD] [/TR] [TR] [TD="align: right"]2[/TD] [TD]Property 1[/TD] [TD] 193.66[/TD] [TD][TABLE="width: 81"] <colgroup><col width="81"></colgroup><tbody>[TR] [TD="width: 81"]HK050[/TD] [/TR] </tbody>[/TABLE] [/TD] [TD="align: right"]50%[/TD] [TD] 96.83 [/TD] [TD]
<colgroup><col width="46"></colgroup><tbody>
[TD="width: 46"]LM020[/TD]
</tbody>
[/TD] [TD="align: right"]30%[/TD] [TD] 58.10[/TD] [TD][TABLE="width: 60"] <colgroup><col width="60"></colgroup><tbody>[TR] [TD="width: 60"]RU010[/TD] [/TR] </tbody>[/TABLE] [/TD] [TD="align: right"]20%[/TD] [TD] 38.73[/TD] [TD]...[/TD] [/TR] [TR] [TD="align: right"]3[/TD] [TD]Property 4[/TD] [TD] 600.00[/TD] [TD][TABLE="width: 81"] <colgroup><col width="81"></colgroup><tbody>[TR] [TD="width: 81"]HK050[/TD] [/TR] </tbody>[/TABLE] [/TD] [TD="align: right"]20%[/TD] [TD] 120.00[/TD] [TD]
<colgroup><col width="46"></colgroup><tbody>
[TD="width: 46"]LM020[/TD]
</tbody>
[/TD] [TD="align: right"]80%[/TD] [TD] 480.00[/TD] [TD][/TD] [TD][/TD] [TD][/TD] [TD]...[/TD] [/TR] [TR] [TD="align: right"]4[/TD] [TD]Property 5[/TD] [TD] 593.22[/TD] [TD][TABLE="width: 81"] <colgroup><col width="81"></colgroup><tbody>[TR] [TD="width: 81"]HK050[/TD] [/TR] </tbody>[/TABLE] [/TD] [TD="align: right"]100%[/TD] [TD] 593.22[/TD] [TD][/TD] [TD][/TD] [TD][/TD] [TD][/TD] [TD][/TD] [TD][/TD] [TD]...[/TD] [/TR] [TR] [TD="align: right"]...[/TD] [TD]...[/TD] [TD][/TD] [TD][/TD] [TD="align: right"][/TD] [TD][/TD] [TD][/TD] [TD][/TD] [TD][/TD] [TD][/TD] [TD][/TD] [TD][/TD] [TD][/TD] [/TR] </tbody>[/TABLE] 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): [TABLE="class: grid, width: 500"] <tbody>[TR] [TD="width: 71"]
Property [/TD] [TD="width: 60"]
LOC[/TD] [TD="width: 81"]
LOCAmount[/TD] [/TR] [TR] [TD]Property 1[/TD] [TD]HK050[/TD] [TD="align: right"]96.83[/TD] [/TR] [TR] [TD]Property 1[/TD] [TD]LM020[/TD] [TD="align: right"]58.10[/TD] [/TR] [TR] [TD]Property 1[/TD] [TD]RU010[/TD] [TD="align: right"]38.73[/TD] [/TR] [TR] [TD]Property 4[/TD] [TD]HK050[/TD] [TD="align: right"]120.00[/TD] [/TR] [TR] [TD]Property 4[/TD] [TD]LM020[/TD] [TD="align: right"]480.00[/TD] [/TR] [TR] [TD]Property 5[/TD] [TD]HK050[/TD] [TD="align: right"]593.22[/TD] [/TR] </tbody>[/TABLE] 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!
