Need help Urgently - vba looping

[franciscocorrea]

Hello everyone,

I want Column B to reflect the parent of Column A, using VBA. I think i should make an iteration, removing digits of Column A until the value is found in Column A. Here an example:

Column A	Column B
1
11
1111
12
1222
122224

<tbody>
</tbody>

I want the following outcome:

Column A	Column B
1
11	1
1111	11
12	1
1222	12
122224	1222

<tbody>
</tbody>

Thanks!!

Best regards,
Francisco

 

[Rick Rothstein]

Hello everyone,

I want Column B to reflect the parent of Column A, using VBA. I think i should make an iteration, removing digits of Column A until the value is found in Column A. Here an example:

I want the following outcome:

Column A	Column B
1
11	1
1111	11
12	1
1222	12
122224	1222

<tbody>
</tbody>

I think this will do what you want. Put the following formula in cell B2 and copy it down to the end of your data...

=IF(LEN(A2)<=LEN(A1),1,A1)

 

[franciscocorrea]

Thanks for your help!

but I need to do it iteratly...because sometimes I may have to remove 3, 4, 5 .... digits. So thats why i want to use VBA, 1) have to remove one digit, 2) see if that new number is on Column A 3) if it is not, remove another digit, until it finds an equal number on Column A.

 

[Rick Rothstein]

but I need to do it iteratly...because sometimes I may have to remove 3, 4, 5 .... digits. So thats why i want to use VBA, 1) have to remove one digit, 2) see if that new number is on Column A 3) if it is not, remove another digit, until it finds an equal number on Column A.

I am not following you on this... can you show what you mean with an original short list followed by one, two or whatever number of follow-up lists that show what results you are looking for given the changes you are trying to describe?

 

[franciscocorrea]

Thanks for helping, i'll try a different approach