divide to columns with a macro with dropping two digit from right

[aminexcel]

i ask a question here that was long and knotty (here). i try to ask a simple question now:

i have two columns with more that 200,000 rows same this:
<table style="border-collapse: collapse; width: 108pt;" border="0" cellpadding="0" cellspacing="0" width="144"><col style="width: 54pt;" span="2" width="72"> <tbody><tr style="height: 14.25pt;" height="19"> <td class="xl64" style="height: 14.25pt; width: 54pt;" height="19" width="72">column1</td> <td class="xl64" style="width: 54pt;" width="72">column2</td> </tr> <tr style="height: 14.25pt;" height="19"> <td class="xl64" style="height: 14.25pt;" height="19">10501</td> <td class="xl64">1050103</td> </tr> <tr style="height: 14.25pt;" height="19"> <td class="xl64" style="height: 14.25pt;" height="19">10502</td> <td class="xl64">1050203</td> </tr> <tr style="height: 14.25pt;" height="19"> <td class="xl64" style="height: 14.25pt;" height="19">30102</td> <td class="xl64">1050204</td> </tr> <tr style="height: 14.25pt;" height="19"> <td class="xl64" style="height: 14.25pt;" height="19">30301</td> <td class="xl64">1050205</td> </tr> <tr style="height: 14.25pt;" height="19"> <td class="xl64" style="height: 14.25pt;" height="19">
</td> <td class="xl64">1050206</td> </tr> <tr style="height: 14.25pt;" height="19"> <td class="xl64" style="height: 14.25pt;" height="19">
</td> <td class="xl64">1050207</td> </tr> <tr style="height: 14.25pt;" height="19"> <td class="xl64" style="height: 14.25pt;" height="19">
</td> <td class="xl64">1050208</td> </tr> <tr style="height: 14.25pt;" height="19"> <td class="xl64" style="height: 14.25pt;" height="19">
</td> <td class="xl64">3010200</td> </tr> <tr style="height: 14.25pt;" height="19"> <td class="xl64" style="height: 14.25pt;" height="19">
</td> <td class="xl64">3030100</td> </tr> </tbody></table>i want a macro that divide cells in column2 on a cell in column1 that be equal to column2 after dropping two digit from right.
for example in this sample divide:
105103 to 10501
1050203, 1050204, 1050205,1050206,1050207, 1050208 to 10502
3010200 to 30102
3030100 to 30101

thanks a lot

 

[Peter_SSs]

i ask a question here that was long and knotty (here). i try to ask a simple question now:

i have two columns with more that 200,000 rows same this:
<table style="border-collapse: collapse; width: 108pt;" border="0" cellpadding="0" cellspacing="0" width="144"><col style="width: 54pt;" span="2" width="72"> <tbody><tr style="height: 14.25pt;" height="19"> <td class="xl64" style="height: 14.25pt; width: 54pt;" height="19" width="72">column1</td> <td class="xl64" style="width: 54pt;" width="72">column2</td> </tr> <tr style="height: 14.25pt;" height="19"> <td class="xl64" style="height: 14.25pt;" height="19">10501</td> <td class="xl64">1050103</td> </tr> <tr style="height: 14.25pt;" height="19"> <td class="xl64" style="height: 14.25pt;" height="19">10502</td> <td class="xl64">1050203</td> </tr> <tr style="height: 14.25pt;" height="19"> <td class="xl64" style="height: 14.25pt;" height="19">30102</td> <td class="xl64">1050204</td> </tr> <tr style="height: 14.25pt;" height="19"> <td class="xl64" style="height: 14.25pt;" height="19">30301</td> <td class="xl64">1050205</td> </tr> <tr style="height: 14.25pt;" height="19"> <td class="xl64" style="height: 14.25pt;" height="19">
</td> <td class="xl64">1050206</td> </tr> <tr style="height: 14.25pt;" height="19"> <td class="xl64" style="height: 14.25pt;" height="19">
</td> <td class="xl64">1050207</td> </tr> <tr style="height: 14.25pt;" height="19"> <td class="xl64" style="height: 14.25pt;" height="19">
</td> <td class="xl64">1050208</td> </tr> <tr style="height: 14.25pt;" height="19"> <td class="xl64" style="height: 14.25pt;" height="19">
</td> <td class="xl64">3010200</td> </tr> <tr style="height: 14.25pt;" height="19"> <td class="xl64" style="height: 14.25pt;" height="19">
</td> <td class="xl64">3030100</td> </tr> </tbody></table>i want a macro that divide cells in column2 on a cell in column1 that be equal to column2 after dropping two digit from right.
for example in this sample divide:
105103 to 10501
1050203, 1050204, 1050205,1050206,1050207, 1050208 to 10502
3010200 to 30102
3030100 to 30101

thanks a lot

I still don't really understand.

1. Where does the red number come from? Was that a typing error?

2. How does the green example work? The third digit is 3 in one part and 1 in the other part.

3. Can you explain more about how column1 impacts the problem/solution? Are you simply trying to remove the last two digits from column2?

4. Should the results be going in a new column, or overwriting an existing column? Perhaps you could post just what the results would look like for your sample data posted above?

 

[Travis]

do you have to have a macro or will a function work?

you could do =column2/trunc(column2/100)

if you need a macro then something like this

Sub test()
    For Each c In Range("B2:B50")
        c.Offset(0, 20).Value = c.Value / Int(c.Value / 100)
    Next
End Sub

use offset to put the values in another column of your choice (this puts it in column V 20 columns to the right of column B)

 

[Peter_SSs]

do you have to have a macro or will a function work?

you could do =column2/trunc(column2/100)

if you need a macro then something like this

Sub test()
    For Each c In Range("B2:B50")
        c.Offset(0, 20).Value = c.Value / Int(c.Value / 100)
    Next
End Sub

use offset to put the values in another column of your choice (this puts it in column V 20 columns to the right of column B)

Did you test those suggestions?

For the first value in column B (1050103) for me both methods return 100.000285687077 which doesn't seem to bear any resemblance to the requirement.

The code also errors out (overflow - due to div by zero) if there is a blank (or zero value) cell in the range.

 

[aminexcel]

sorry for deficiencies in my question. columns 1 and 2 are not numbers and are export codes fore some goods or services and have values in dollar same table below. in better words, column 1 and 2 values are export value in two different levels and any 5 digit code containing more that nearly fifty 7 digit codes. so i want to compute 7 digit code weights in their 5 digit corresponding code (upper level of aggregation) in column3:

<table style="border-collapse: collapse; width: 291pt;" border="0" cellpadding="0" cellspacing="0" width="387"><col style="width: 58pt;" span="2" width="77"> <col style="width: 63pt;" width="84"> <col style="width: 58pt;" width="77"> <col style="width: 54pt;" width="72"> <tbody><tr style="height: 14.25pt;" height="19"> <td class="xl66" style="height: 14.25pt; width: 58pt;" height="19" width="77">5 digit code</td> <td class="xl66" style="width: 58pt;" width="77">column1</td> <td class="xl66" style="width: 63pt;" width="84">7 digit code</td> <td class="xl66" style="width: 58pt;" width="77">column2</td> <td class="xl66" style="width: 54pt;" width="72">column3</td> </tr> <tr style="height: 14.25pt;" height="19"> <td class="xl68" style="height: 14.25pt;" height="19">10501</td> <td class="xl67">3957.1</td> <td class="xl68">1050103</td> <td class="xl67">3957.1</td> <td class="xl69">1.00000</td> </tr> <tr style="height: 14.25pt;" height="19"> <td class="xl68" style="height: 14.25pt;" height="19">10502</td> <td class="xl67">15030.4</td> <td class="xl68">1050203</td> <td class="xl67">1268.6</td> <td class="xl69">0.08440</td> </tr> <tr style="height: 14.25pt;" height="19"> <td class="xl68" style="height: 14.25pt;" height="19">30102</td> <td class="xl67">27738.4</td> <td class="xl68">1050204</td> <td class="xl67">317.1</td> <td class="xl69">0.02110</td> </tr> <tr style="height: 14.25pt;" height="19"> <td class="xl68" style="height: 14.25pt;" height="19">30301</td> <td class="xl67">312924.2</td> <td class="xl68">1050205</td> <td class="xl67">3171.4</td> <td class="xl69">0.21100</td> </tr> <tr style="height: 14.25pt;" height="19"> <td class="xl67" style="height: 14.25pt;" height="19">
</td> <td class="xl67">
</td> <td class="xl68">1050206</td> <td class="xl67">951.4</td> <td class="xl69">0.06330</td> </tr> <tr style="height: 14.25pt;" height="19"> <td class="xl67" style="height: 14.25pt;" height="19">
</td> <td class="xl67">
</td> <td class="xl68">1050207</td> <td class="xl67">5264.3</td> <td class="xl69">0.35024</td> </tr> <tr style="height: 14.25pt;" height="19"> <td class="xl67" style="height: 14.25pt;" height="19">
</td> <td class="xl67">
</td> <td class="xl68">1050208</td> <td class="xl67">4057.5</td> <td class="xl69">0.26996</td> </tr> <tr style="height: 14.25pt;" height="19"> <td class="xl67" style="height: 14.25pt;" height="19">
</td> <td class="xl67">
</td> <td class="xl68">3010200</td> <td class="xl67">27738.4</td> <td class="xl69">1.00000</td> </tr> <tr style="height: 14.25pt;" height="19"> <td class="xl67" style="height: 14.25pt;" height="19">
</td> <td class="xl67">
</td> <td class="xl68">3030100</td> <td class="xl67">312924.2</td> <td class="xl69">1.00000</td> </tr> </tbody></table>
my problem is computing column3 values (weights) from dividing each 7 digit code (in column 2) to corresponding 5 digit code category (in column 1) that is same 7 digit code only after dropping 2 digit from right

sincerely yours
amin

 

[Travis]

Did you test those suggestions?

For the first value in column B (1050103) for me both methods return 100.000285687077 which doesn't seem to bear any resemblance to the requirement.

The code also errors out (overflow - due to div by zero) if there is a blank (or zero value) cell in the range.

I did not test the sub but it should work with an error trap
I had trouble understanding what the opp wanted but I thought he was wanting to divide the number in column 2 buy the left 5 characters of that column (which is what is in column 1) The function I posted would do that...but yes would through a divide by 0 error on a blank cell.

if thats not what was desired...my bad...

 

[Peter_SSs]

II had trouble understanding what the opp wanted ...

Fair enough, I was in the same boat.

Amin

See if either formula or code method does what you want.

If the codes in column A are text rather than numeric, then remove the +0 from the formula in each case.

Excel Workbook
	A	B	C	D	E
1	5 digit code	column1	7 digit code	column2	column3
2	10501	3957.1	1050103	3957.1	1.00000
3	10502	15030.4	1050203	1268.6	0.08440
4	30102	27738.4	1050204	317.1	0.02110
5	30301	312924.2	1050205	3171.4	0.21100
6			1050206	951.4	0.06330
7			1050207	5264.3	0.35024
8			1050208	4057.5	0.26995
9			3010200	27738.4	1.00000
10			3030100	312924.2	1.00000
11
aminexcel

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> Amin()<br>    Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br>    Application.EnableEvents = <SPAN style="color:#00007F">False</SPAN><br>    <SPAN style="color:#00007F">With</SPAN> Range("C2", Range("C" & Rows.Count).End(xlUp)).Offset(, 2)<br>        .Formula = "=D2/VLOOKUP(LEFT(C2,5)+0,A:B,2,0)"<br>        .NumberFormat = "0.00000"<br>        .Value = .Value<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN><br>    Application.EnableEvents = <SPAN style="color:#00007F">True</SPAN><br>    <br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

 

[aminexcel]

dear peter
many thanks
ray:
it works

sincerely
cheers
amin