Approximate match

[giddyup43]

Hi. I have a list of upc codes, which should be 12 digits. Unfortunately, some digits have been dropped while doing data entry.
I would like to compare these 11 digit upc's to a 'good' list, which contains the upcs they way they're supposed to be - basically doing a vlookup where one character is not exact.
Once I finish with the 11 digit list, I also have a list of 10 digit upcs which could have dropped 2 characters, I'm not sure. I would like to check it against the 'good' list.
Can someone please help me with this?
I tried using the fuzzy lookup add-in from excel, but either I don't know exactly how to use it, or my files were too large, and it froze.
Any help would be much appreciated!

 

[T. Valko]

Are the dropped digits always from the right side of the string?

123456789012

Becomes

1234567890

 

[giddyup43]

It could be either from the right side or left side, but not from the middle.
and I think it's maximum only one digit dropped from either side. We wouldn't have the 12 falling away like in the example you gave above, but just the 2 from the right end or the 1 from the left end, or both ends falling off at once.

 

[T. Valko]

It could be either from the right side or left side, but not from the middle.
and I think it's maximum only one digit dropped from either side. We wouldn't have the 12 falling away like in the example you gave above, but just the 2 from the right end or the 1 from the left end, or both ends falling off at once.

Man, that sounds like a nightmare!

Any chance you can make up a SMALL sample file that shows us what you're dealing with and what results you expect?

You can't upload files directly to this site but you can upload to any other site (like a free file hosting site) and then insert a link to that file in your post.

We don't need to see 1000's of rows and dozens of columns worth of data. Just enough to demonstrate the data and layout. 20 rows and a few columns worth of data is usually plenty. In the *.xlsx file format the file would be about 10kb in size.

 

[giddyup43]

yeah! thanks for sympathizing! Hope you can figure out an ez solution for me.
To compound the problem (!) some upcs have leading zeroes. (That's the way upcs are made...gives me enough headaches)
I just double checked. there aren't that many that drop 2 digits, so for now we can leave it on the side. It's either from the front or back, not both.
Here is my table details. I would love to an approximate lookup that would bring back the values of the correct upcs into sheet 1, column B.
Sheet 1 Column A: 11 Digit
[TABLE="width: 84"]
<colgroup><col></colgroup><tbody>[TR]
[TD]00307006514[/TD]
[/TR]
[TR]
[TD]01307000052[/TD]
[/TR]
[TR]
[TD]01307000076[/TD]
[/TR]
[TR]
[TD]01307000083[/TD]
[/TR]
[TR]
[TD]01307000113[/TD]
[/TR]
[TR]
[TD]01307000229[/TD]
[/TR]
[TR]
[TD]01307000267[/TD]
[/TR]
[TR]
[TD]01307000281[/TD]
[/TR]
[TR]
[TD]01307000298[/TD]
[/TR]
[TR]
[TD]01307000328[/TD]
[/TR]
[TR]
[TD]01307000359[/TD]
[/TR]
[TR]
[TD]01307000380[/TD]
[/TR]
[TR]
[TD]01307000403[/TD]
[/TR]
[TR]
[TD]01307000427[/TD]
[/TR]
[TR]
[TD]01307000519[/TD]
[/TR]
[TR]
[TD]01307000571[/TD]
[/TR]
[TR]
[TD]01307000588[/TD]
[/TR]
[TR]
[TD]01307000632[/TD]
[/TR]
[TR]
[TD]01307000724[/TD]
[/TR]
</tbody>[/TABLE]

Sheet 1 Column B: Corrected UPC (here is where I want the correct values to go)

Sheet 2 Column A: 12 Digit
[TABLE="width: 92"]
<colgroup><col></colgroup><tbody>[TR]
[TD]003070065145[/TD]
[/TR]
[TR]
[TD]013070000526[/TD]
[/TR]
[TR]
[TD]013070000767[/TD]
[/TR]
[TR]
[TD]013070000838[/TD]
[/TR]
[TR]
[TD]013070001139[/TD]
[/TR]
[TR]
[TD]013070002291[/TD]
[/TR]
[TR]
[TD]077245111266[/TD]
[/TR]
[TR]
[TD]201307000267[/TD]
[/TR]
[TR]
[TD]301307000281[/TD]
[/TR]
[TR]
[TD]401307000298[/TD]
[/TR]
[TR]
[TD]501307000328[/TD]
[/TR]
[TR]
[TD]601307000359[/TD]
[/TR]
[TR]
[TD]604680085434[/TD]
[/TR]
[TR]
[TD]610708578147[/TD]
[/TR]
[TR]
[TD]610708578178[/TD]
[/TR]
[TR]
[TD]653694102507[/TD]
[/TR]
[TR]
[TD]653694102606[/TD]
[/TR]
[TR]
[TD]653694935242[/TD]
[/TR]
[TR]
[TD]653694935273[/TD]
[/TR]
[TR]
[TD]653694935358[/TD]
[/TR]
[TR]
[TD]653694936010[/TD]
[/TR]
[TR]
[TD]701307000380[/TD]
[/TR]
[TR]
[TD]801307000427[/TD]
[/TR]
[TR]
[TD]801307000519[/TD]
[/TR]
[TR]
[TD]801307000571[/TD]
[/TR]
[TR]
[TD]801307000588[/TD]
[/TR]
[TR]
[TD]801307000632[/TD]
[/TR]
[TR]
[TD]801307000724[/TD]
[/TR]
[TR]
[TD]859494009019[/TD]
[/TR]
[TR]
[TD]859494009033[/TD]
[/TR]
[TR]
[TD]859494009071[/TD]
[/TR]
[TR]
[TD]897811001155[/TD]
[/TR]
[TR]
[TD]901307000403[/TD]
[/TR]
</tbody>[/TABLE]

 

[T. Valko]

The samples you posted look like the last digit has been dropped from the 11 digit numbers.

Maybe you can test the first 11 digits of the 12 digit number for a match with the 11 digit numbers?

 

[giddyup43]

I made up this sample. It's mixed. Some are dropped from the first and some are dropped from the last.
as to your suggestion, how do I check only the first 11 digits or only the last 11 digits? whats the formula for that?

 

[T. Valko]

Something like this to check the first 11 digits...

Data Range
[Table="class: grid"][tr][td="bgcolor: #c0c0c0"][/td][td="bgcolor: #c0c0c0"]

A​

[/td][td="bgcolor: #c0c0c0"]

B​

[/td][td="bgcolor: #c0c0c0"]

C​

[/td][td="bgcolor: #c0c0c0"]

D​

[/td][td="bgcolor: #c0c0c0"]

E​

[/td][/tr]
[tr][td="bgcolor: #c0c0c0"]

1​

[/td][td]

12 Digits​

[/td][td][/td][td]

-----​

[/td][td]

11 Digits​

[/td][td][/td][/tr]

[tr][td="bgcolor: #c0c0c0"]

2​

[/td][td]

003070065145​

[/td][td]

Data1​

[/td][td][/td][td]

00307006514​

[/td][td]

Data1​

[/td][/tr]

[tr][td="bgcolor: #c0c0c0"]

3​

[/td][td]

013070000526​

[/td][td]

Data2​

[/td][td][/td][td]

01307000052​

[/td][td]

Data2​

[/td][/tr]

[tr][td="bgcolor: #c0c0c0"]

4​

[/td][td]

013070000767​

[/td][td]

Data3​

[/td][td][/td][td]

01307000076​

[/td][td]

Data3​

[/td][/tr]

[tr][td="bgcolor: #c0c0c0"]

5​

[/td][td]

013070000838​

[/td][td]

Data4​

[/td][td][/td][td]

01307000083​

[/td][td]

Data4​

[/td][/tr]

[tr][td="bgcolor: #c0c0c0"]

6​

[/td][td]

013070001139​

[/td][td]

Data5​

[/td][td][/td][td]

01307000113​

[/td][td]

Data5​

[/td][/tr]

[tr][td="bgcolor: #c0c0c0"]

7​

[/td][td]

013070002291​

[/td][td]

Data6​

[/td][td][/td][td]

01307000229​

[/td][td]

Data6​

[/td][/tr]

[tr][td="bgcolor: #c0c0c0"]

8​

[/td][td]

077245111266​

[/td][td]

Data7​

[/td][td][/td][td]

01307000267​

[/td][td]

No Match​

[/td][/tr]

[tr][td="bgcolor: #c0c0c0"]

9​

[/td][td][/td][td][/td][td][/td][td]

01307000281​

[/td][td]

No Match​

[/td][/tr]

[tr][td="bgcolor: #c0c0c0"]

10​

[/td][td][/td][td][/td][td][/td][td]

01307000298​

[/td][td]

No Match​

[/td][/tr]

[tr][td="bgcolor: #c0c0c0"]

11​

[/td][td][/td][td][/td][td][/td][td]

01307000328​

[/td][td]

No Match​

[/td][/tr]

[tr][td="bgcolor: #c0c0c0"]

12​

[/td][td][/td][td][/td][td][/td][td]

01307000359​

[/td][td]

No Match​

[/td][/tr]

[tr][td="bgcolor: #c0c0c0"]

13​

[/td][td][/td][td][/td][td][/td][td]

01307000380​

[/td][td]

No Match​

[/td][/tr]

[tr][td="bgcolor: #c0c0c0"]

14​

[/td][td][/td][td][/td][td][/td][td]

01307000403​

[/td][td]

No Match​

[/td][/tr]

[tr][td="bgcolor: #c0c0c0"]

15​

[/td][td][/td][td][/td][td][/td][td]

01307000427​

[/td][td]

No Match​

[/td][/tr]

[tr][td="bgcolor: #c0c0c0"]

16​

[/td][td][/td][td][/td][td][/td][td]

01307000519​

[/td][td]

No Match​

[/td][/tr]

[tr][td="bgcolor: #c0c0c0"]

17​

[/td][td][/td][td][/td][td][/td][td]

01307000571​

[/td][td]

No Match​

[/td][/tr]

[tr][td="bgcolor: #c0c0c0"]

18​

[/td][td][/td][td][/td][td][/td][td]

01307000588​

[/td][td]

No Match​

[/td][/tr]

[tr][td="bgcolor: #c0c0c0"]

19​

[/td][td][/td][td][/td][td][/td][td]

01307000632​

[/td][td]

No Match​

[/td][/tr]

[tr][td="bgcolor: #c0c0c0"]

20​

[/td][td][/td][td][/td][td][/td][td]

01307000724​

[/td][td]

No Match​

[/td][/tr]
[/table]

This array formula** entered in E2 and copied down:

=IFERROR(INDEX(B$2:B$8,MATCH(D2,LEFT(A$2:A$8,11),0)),"No Match")

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

 

[giddyup43]

great! that works really well! is there a way to do the right side in the same formula? or do I have to do it separately? What would that formula be?

 

[T. Valko]

great! that works really well! is there a way to do the right side in the same formula? or do I have to do it separately? What would that formula be?

If I understand the relationship I think you just need to replace:

LEFT(A$2:A$8,11)

with:

RIGHT(A$2:A$8,11)