trying to detect look-alike 8-digit numbers

[radia2020]

My problem
Hi - I've a list of unique 8-digit numbers (identities). While I know that each of them is unique, some of them are very similar. For example only one digit might be different, or two digits might be in the opposite order, at any location within the 8-digit sequence.

People will be manually entering data against these 8-digit numbers. Some are not great at data entry, and I'm concerned that they may enter it against the wrong identity.
E.g. one identity is 12345678 and another is 12346578. The inattentive worker may put the data belonging to the former against the ID number for the latter.

Therefore I want to draw up a list of 'look-alikes' - pairs (or perhaps more - there could be clusters of three or four IDs similar to each other) of numbers within the database that I can highlight as needing special attention when entering data against them (and that can be double-checked afterwards).

I know there are definitely some look-alikes within the database because I've identified a few of them manually.
(Note - the data entry by the workers has to be manual. It's first time entry of new data that doesn't yet exist in any database. Therefore it's not simply a question of using vlookup or similar to get it from one place to another. And so being able to spotlight likely error points will be important.)

Any suggestions you have would be great

Failed attempts with Excel fuzzy lookup add-in
I tried myself using the Excel fuzzy lookup add-in from Microsoft, and set it up to compare the table of ID numbers with a copy of itself, but reporting a relatively low similarity threshold and a high number of matches. But even though I brought the similarity down to 0.25 and the number of matches up to 10 for experimental purposes, it still only ever reported one result for each number - the perfect similarity matches with the same ID numbers in the second copy of the table. Whereas what I'm really interested in are the ones with slightly lower similarity scores in order to pick out the non-identical but look-alike IDs.

• Tried this with actual numbers, numbers formatted as text, and numbers with a letter added to ensure they weren't seen as numbers - same result.
• Tried going into 'advanced' and setting the option to include exact matches to FALSE. That just gave me zero similarity reports for all entries - it didn't pick up numbers which had only one different digit.
• I was able to use the tool successfully for the sample database supplied, and used the same steps on my test data, so I don't think my technique was completely wrong - but there was clearly some problem!

So maybe the fuzzy lookup isn't the right approach for this job and there's a better formula I could use?

Many thanks for any help.

[AliGW]

Welcome to the forum.

There are instructions at the top of the page explaining how to attach your sample workbook.

[radia2020]

Hi AliGW,

Thanks for the welcome.

Attached is some sample data. Data 1 and Data 2 are the same dataset.

• Name 5 and Name 6 have similar numbers (2 digits transposed).
• Name 11 and Name 12 have similar numbers (2nd last digit different).
• Name 7 and Name 13 have similar numbers (second digit different).

I'd like to be able to identify these similarities.
In the real (large) dataset, there could be pairs of similar numbers, or clusters of three or more that are similar.

The real data just has 8-digit numbers but here I added an x to each sample number to be absolutely certain it was being treated as text in case that made a difference.

The results tab contains the output from using fuzzy lookup with number of matches = 10 and similarity threshold = 0.05. However, you can see that it has only picked out the exact matches where similarity is 100%.

It may be that I should be using an entirely different approach and not fuzzy lookup at all. I'd welcome suggestions!
Thanks.

[oeldere]

For equal ID match for the same name in the 2 dataset you can use.

f2 =if(countif($E$2:$E$27;$E2)>=2,"","no")


For equal ID match for differant names in the 2 datasest you can

g2 =if(countif($D$2:$D$27,$D2)>2,"yes","")


For the similarities match I have no solution.
I think you have to need VBA for that.
I can't help you with a VBA solutions.

See the attached file, sheet oeldere.

[radia2020]

Thanks oeldere for your work on this.

I think maybe my sample spreadsheet confused matters rather than helping, because it was just illustrating my failed attempts using fuzzy lookup and so the format reflected that.

In reality there is only one database, in which I know all the ID numbers are unique. I just had two copies in the example because that's what's needed for fuzzy lookup.
The "names" (really descriptors) and any similarity or difference in them can also be ignored here. While they are in the dataset they won't be visbile to data entry staff. I only included them in the sample database to make it clearer what row was matching to what other one in the fuzzy lookup example.

The actual problem focuses specifically on the ID numbers, so it might be better just to think of it as a dataset of ID numbers alone and forget all the ancillary data.

• I know that all the ID numbers in my dataset are actually unique.
• However, I know some are nearly identical.
• For these look-alikes, the differences may occur at any point in the 8-digit number (e.g.transposed digits, one or two digits different).
• I want to identify these look-alikes - i.e. the pairs or clusters that are nearly identical.

I've attached the sample single dataset reflecting this better.

So really what I need to do is to compare each number with all the others in the database and see if any are similar to it, and if so, to point out which one(s) is/are similar.