dividing on same number after dropping 2 digit from right

aminexcel

Board Regular
Joined
May 2, 2009
Messages
63
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
 

Some videos you may like

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

Watch MrExcel Video

Forum statistics

Threads
1,122,709
Messages
5,597,697
Members
414,164
Latest member
ARTW

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top