Hello to all,
I have the table below. Maybe someone could help with this with a VBA macro. I have an Array formula that ouputs almost correct the desired output, but not completely.
In column A I have the Country Codes values and in column B corresponding description for each value (ZONE).
In Column D I have a list of numbers for which I want to look for the closest value in column A and if found put in column E the corresponding description (ZONE). The values in column E (ZONE) are the output desired for the input NUMBERS in D.
The basic condition is look the prefix, if a number in A is the prefix of a number in D (or vice versa), so the ZONE for the number in D should be the corresponding to that number in A.
Is there are more than one similar or exact matches, then the zone to select would be the first sorted ascending and if there is an exact match then zone would be that match exactly.
I hope make sense
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TH]COUNTRY CODE[/TH]
[TH]ZONE[/TH]
[TH][/TH]
[TH]NUMBERS[/TH]
[TH]ZONE[/TH]
[/TR]
[TR]
[TD]599[/TD]
[TD]ANT[/TD]
[TD][/TD]
[TD]124[/TD]
[TD]BRB45[/TD]
[/TR]
[TR]
[TD]61[/TD]
[TD]AUS[/TD]
[TD][/TD]
[TD]12462[/TD]
[TD]BRB5[/TD]
[/TR]
[TR]
[TD]124[/TD]
[TD]BRB45[/TD]
[TD][/TD]
[TD]1246[/TD]
[TD]BRB7[/TD]
[/TR]
[TR]
[TD]124622[/TD]
[TD]BRB5[/TD]
[TD][/TD]
[TD]33[/TD]
[TD]FRA1[/TD]
[/TR]
[TR]
[TD]1246[/TD]
[TD]BRB7[/TD]
[TD][/TD]
[TD]3375012[/TD]
[TD]FRA1[/TD]
[/TR]
[TR]
[TD]124623[/TD]
[TD]BRB8[/TD]
[TD][/TD]
[TD]187635[/TD]
[TD]JAM[/TD]
[/TR]
[TR]
[TD]124624[/TD]
[TD]BRB92[/TD]
[TD][/TD]
[TD]18763044[/TD]
[TD]JAM[/TD]
[/TR]
[TR]
[TD]237[/TD]
[TD]CMR[/TD]
[TD][/TD]
[TD]23092[/TD]
[TD]MUS[/TD]
[/TR]
[TR]
[TD]358[/TD]
[TD]FIN[/TD]
[TD][/TD]
[TD]3162[/TD]
[TD]NLD[/TD]
[/TR]
[TR]
[TD]33750[/TD]
[TD]FRA1[/TD]
[TD][/TD]
[TD]49185[/TD]
[TD]NOT FOUND[/TD]
[/TR]
[TR]
[TD]33751[/TD]
[TD]FRA2[/TD]
[TD][/TD]
[TD]51078[/TD]
[TD]NOT FOUND[/TD]
[/TR]
[TR]
[TD]334[/TD]
[TD]FRA3[/TD]
[TD][/TD]
[TD]1721[/TD]
[TD]SXM2[/TD]
[/TR]
[TR]
[TD]1876[/TD]
[TD]JAM[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]230[/TD]
[TD]MUS[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]31[/TD]
[TD]NLD[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]65[/TD]
[TD]SGP[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1721[/TD]
[TD]SXM2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1721[/TD]
[TD]SXM4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]USA[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
I have the table below. Maybe someone could help with this with a VBA macro. I have an Array formula that ouputs almost correct the desired output, but not completely.
In column A I have the Country Codes values and in column B corresponding description for each value (ZONE).
In Column D I have a list of numbers for which I want to look for the closest value in column A and if found put in column E the corresponding description (ZONE). The values in column E (ZONE) are the output desired for the input NUMBERS in D.
The basic condition is look the prefix, if a number in A is the prefix of a number in D (or vice versa), so the ZONE for the number in D should be the corresponding to that number in A.
Is there are more than one similar or exact matches, then the zone to select would be the first sorted ascending and if there is an exact match then zone would be that match exactly.
I hope make sense
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TH]COUNTRY CODE[/TH]
[TH]ZONE[/TH]
[TH][/TH]
[TH]NUMBERS[/TH]
[TH]ZONE[/TH]
[/TR]
[TR]
[TD]599[/TD]
[TD]ANT[/TD]
[TD][/TD]
[TD]124[/TD]
[TD]BRB45[/TD]
[/TR]
[TR]
[TD]61[/TD]
[TD]AUS[/TD]
[TD][/TD]
[TD]12462[/TD]
[TD]BRB5[/TD]
[/TR]
[TR]
[TD]124[/TD]
[TD]BRB45[/TD]
[TD][/TD]
[TD]1246[/TD]
[TD]BRB7[/TD]
[/TR]
[TR]
[TD]124622[/TD]
[TD]BRB5[/TD]
[TD][/TD]
[TD]33[/TD]
[TD]FRA1[/TD]
[/TR]
[TR]
[TD]1246[/TD]
[TD]BRB7[/TD]
[TD][/TD]
[TD]3375012[/TD]
[TD]FRA1[/TD]
[/TR]
[TR]
[TD]124623[/TD]
[TD]BRB8[/TD]
[TD][/TD]
[TD]187635[/TD]
[TD]JAM[/TD]
[/TR]
[TR]
[TD]124624[/TD]
[TD]BRB92[/TD]
[TD][/TD]
[TD]18763044[/TD]
[TD]JAM[/TD]
[/TR]
[TR]
[TD]237[/TD]
[TD]CMR[/TD]
[TD][/TD]
[TD]23092[/TD]
[TD]MUS[/TD]
[/TR]
[TR]
[TD]358[/TD]
[TD]FIN[/TD]
[TD][/TD]
[TD]3162[/TD]
[TD]NLD[/TD]
[/TR]
[TR]
[TD]33750[/TD]
[TD]FRA1[/TD]
[TD][/TD]
[TD]49185[/TD]
[TD]NOT FOUND[/TD]
[/TR]
[TR]
[TD]33751[/TD]
[TD]FRA2[/TD]
[TD][/TD]
[TD]51078[/TD]
[TD]NOT FOUND[/TD]
[/TR]
[TR]
[TD]334[/TD]
[TD]FRA3[/TD]
[TD][/TD]
[TD]1721[/TD]
[TD]SXM2[/TD]
[/TR]
[TR]
[TD]1876[/TD]
[TD]JAM[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]230[/TD]
[TD]MUS[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]31[/TD]
[TD]NLD[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]65[/TD]
[TD]SGP[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1721[/TD]
[TD]SXM2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1721[/TD]
[TD]SXM4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]USA[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]