i have nearly 200000 rows same this in 2 column:
t10d dollar
0105010300 3957.06
0105020301 1268.57
0105020402 317.14
0105020503 3171.43
0105020604 951.43
0105020705 5264.29
0105020806 4057.53
0301020000 27738.37
0303010000 312924.21
t10d=tariff in 10 digit for u.s export
dollar=us export in dollar
i want to making price index of export and for 2 digit tarrifs. so i need these dollar weights (same table below):
w4in2 = 4 digit tarrif in 2 digit
w6in4 = 6 digit tarrif in 4 digit
w8in6 = 8 digit tarrif in 6 digit
w10in8= 10 digit tarrif in 8 digit
i did this process:
1- insert 4 column before column A naming t2d, t4d, t6d and t8d.
A2=TRUNC(E2/100000000)
B2=TRUNC(E2/1000000)
C2=TRUNC(E2/10000)
D2=TRUNC(E2/100)
2- unique list from column A to F: made a unique list from column G, I, L, O by advance filtering
3- sumif for lowest level tariffs
H2=SUMIF($A$1:$F$10;G2;$F$1:$F$10)
J2=SUMIF($B$2:$F$10;I2;$F$2:$F$10)
M2=SUMIF($C$2:$F$10;L2;$F$2:$F$10)
P2=SUMIF($D$2:$F$10;O2;$F$2:$F$10)
4- weighting: this is my problem that is most important step. i want to divid each lowest level export dollar with upper level that is in same stratum. for example in must divide 01050103 export value to 010501 export value and divide 01050203, 01050204, 01050205,01050206,01050207 and 01050208 export value to 010502 export value.
in sum; i think i must follow this step
[trunc + unique list + sumif + weighting]
sorry, i am not an expert excel user and so i dont know how can if divid each value in columns J, M, P and S to values on H, J, M and P that are the same after droping 2 digit from the right
can any one do this with a macro?
cheers
amin
my sheet is here in 2003 and 2007 excel version:
http://www.4shared.com/file/128121839/e796a036/sheet.html
http://www.4shared.com/file/128121833/7434928/sheet.html
with all regards i asked save question here:
http://www.excelforum.com/newthread.php?do=newthread&f=7
t10d dollar
0105010300 3957.06
0105020301 1268.57
0105020402 317.14
0105020503 3171.43
0105020604 951.43
0105020705 5264.29
0105020806 4057.53
0301020000 27738.37
0303010000 312924.21
t10d=tariff in 10 digit for u.s export
dollar=us export in dollar
i want to making price index of export and for 2 digit tarrifs. so i need these dollar weights (same table below):
w4in2 = 4 digit tarrif in 2 digit
w6in4 = 6 digit tarrif in 4 digit
w8in6 = 8 digit tarrif in 6 digit
w10in8= 10 digit tarrif in 8 digit
i did this process:
1- insert 4 column before column A naming t2d, t4d, t6d and t8d.
A2=TRUNC(E2/100000000)
B2=TRUNC(E2/1000000)
C2=TRUNC(E2/10000)
D2=TRUNC(E2/100)
2- unique list from column A to F: made a unique list from column G, I, L, O by advance filtering
3- sumif for lowest level tariffs
H2=SUMIF($A$1:$F$10;G2;$F$1:$F$10)
J2=SUMIF($B$2:$F$10;I2;$F$2:$F$10)
M2=SUMIF($C$2:$F$10;L2;$F$2:$F$10)
P2=SUMIF($D$2:$F$10;O2;$F$2:$F$10)
4- weighting: this is my problem that is most important step. i want to divid each lowest level export dollar with upper level that is in same stratum. for example in must divide 01050103 export value to 010501 export value and divide 01050203, 01050204, 01050205,01050206,01050207 and 01050208 export value to 010502 export value.
in sum; i think i must follow this step
[trunc + unique list + sumif + weighting]
sorry, i am not an expert excel user and so i dont know how can if divid each value in columns J, M, P and S to values on H, J, M and P that are the same after droping 2 digit from the right
can any one do this with a macro?
cheers
amin
my sheet is here in 2003 and 2007 excel version:
http://www.4shared.com/file/128121839/e796a036/sheet.html
http://www.4shared.com/file/128121833/7434928/sheet.html
with all regards i asked save question here:
http://www.excelforum.com/newthread.php?do=newthread&f=7
