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:

[TABLE="width: 200"]
<tbody>[TR]
[TD]Column A
[/TD]
[TD]Column B
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1111
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1222
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]122224
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I want the following outcome:

[TABLE="width: 200"]
<tbody>[TR]
[TD]Column A
[/TD]
[TD]Column B
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]1111
[/TD]
[TD]11
[/TD]
[/TR]
[TR]
[TD]12
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]1222
[/TD]
[TD]12
[/TD]
[/TR]
[TR]
[TD]122224
[/TD]
[TD]1222
[/TD]
[/TR]
</tbody>[/TABLE]

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:

[TABLE="width: 200"]
<tbody>[TR]
[TD]Column A[/TD]
[TD]Column B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]1111[/TD]
[TD]11[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]1222[/TD]
[TD]12[/TD]
[/TR]
[TR]
[TD]122224[/TD]
[TD]1222[/TD]
[/TR]
</tbody>[/TABLE]

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