Data reorganisation using ratios

[AusGirl]

Hi,

I need to do conversions on some data using ratios. (It's based on the reorganisation of spatial boundaries). I cannot figure out how to apply the different ratios to the same case/code (it's a very large database, so doing it individually is not possible)

An example of my dataset:
[TABLE="width: 500"]
<tbody>[TR]
[TD]Code[/TD]
[TD]Variable1[/TD]
[TD]Variable 2[/TD]
[TD]Variable 3[/TD]
[/TR]
[TR]
[TD]1001[/TD]
[TD]115[/TD]
[TD]600[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]1002[/TD]
[TD]30[/TD]
[TD]204[/TD]
[TD]36[/TD]
[/TR]
[TR]
[TD]1003[/TD]
[TD]20[/TD]
[TD]54[/TD]
[TD]15[/TD]
[/TR]
[TR]
[TD]1004[/TD]
[TD]6[/TD]
[TD]361[/TD]
[TD]26[/TD]
[/TR]
[TR]
[TD]1005[/TD]
[TD]12[/TD]
[TD]214[/TD]
[TD]69[/TD]
[/TR]
</tbody>[/TABLE]

An example of the conversion table (FROM Case*ratio = TO Case):
[TABLE="width: 500"]
<tbody>[TR]
[TD]FROM Code[/TD]
[TD]TO Code[/TD]
[TD]Ratio[/TD]
[/TR]
[TR]
[TD]1001[/TD]
[TD]3311[/TD]
[TD].982[/TD]
[/TR]
[TR]
[TD]1001[/TD]
[TD]3314[/TD]
[TD].018[/TD]
[/TR]
[TR]
[TD]1002[/TD]
[TD]8350[/TD]
[TD].856[/TD]
[/TR]
[TR]
[TD]1002[/TD]
[TD]3314[/TD]
[TD].004[/TD]
[/TR]
[TR]
[TD]1002[/TD]
[TD]3700[/TD]
[TD].140[/TD]
[/TR]
[TR]
[TD]1003[/TD]
[TD]3314[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]1004[/TD]
[TD]3700[/TD]
[TD].560[/TD]
[/TR]
[TR]
[TD]1004[/TD]
[TD]3640[/TD]
[TD].440[/TD]
[/TR]
</tbody>[/TABLE]

I need to use the ratio to convert the data in the first table to the new coding. I've looked into Vlookup and Index-match, but still not sure how to go about it. Any advice would be appreciated!

Thanks!

 

[SteveO59L]

Assuming your second table resides in cells a21:c30, and your first in a1:c10

in d1
=vlookup(a1,$a$21:$c$30,2,false)
and filled down
will return for the value in a1, the revised coding from a1:c30

but in your 2nd table you have duplicate values?

 

[AusGirl]

Hi,

It's not just recoding the values, it's also multiplying it by the ratio in the second table. Here is actually what I need to figure out. Currently, I have the census data for table one, based on the spatial areas 1001, 1002, 1003, 1004. I'm making a time series, but the boundaries were re-arranged in later years to make the new spatial areas (3311, 3314, 8350, 3700, 3640). Table 2 says how much of the old categories are in each new category

e.g. 98.2% of area 1001 is in area 3311, 1.8% is in area 3314
Area 1002 is split between 3314, 8350 and 3700
etc...

So, instead of simply re-coding 1001 to 3311, I need to do this:
[TABLE="width: 500"]
<tbody>[TR]
[TD]Code -> new code[/TD]
[TD]Variable 1[/TD]
[TD]Variable2[/TD]
[TD]Variable 3[/TD]
[/TR]
[TR]
[TD]1001 -> 3311[/TD]
[TD]115*.982[/TD]
[TD]600*.982[/TD]
[TD]0*.982[/TD]
[/TR]
[TR]
[TD]1001 -> 3314[/TD]
[TD]115*.018[/TD]
[TD]600*.018[/TD]
[TD]0*.018[/TD]
[/TR]
[TR]
[TD]1002 -> 8350[/TD]
[TD]30*.856[/TD]
[TD]204*.856[/TD]
[TD]36*.856[/TD]
[/TR]
[TR]
[TD]1002 -> 3314[/TD]
[TD]30*.004[/TD]
[TD]204*.004[/TD]
[TD]36*.004[/TD]
[/TR]
[TR]
[TD]1002 -> 3700[/TD]
[TD]30*.14[/TD]
[TD]204*.14[/TD]
[TD]36*.14[/TD]
[/TR]
[TR]
[TD]1003 -> 3314[/TD]
[TD]20*1[/TD]
[TD]54*1[/TD]
[TD]15*1[/TD]
[/TR]
</tbody>[/TABLE]


I then have to add all the values for the new coding, so variable 1 for code 3314 would be:
115*.018 (from 1001) + 30 *.856 (from 1002) + 20*1 (from 1003)


Any ideas? Hopefully that clears it up a bit!