Match/vlookup similar data

[CCST]

Hello all,

I am trying take attendance, however, the raw data is very different (also similar) from the database. I've tried using wildcards, vlookup, match but still not quite nail the job. At the moment, I am using conditional formatting > Highlight cells > Duplicate values to find the exact matches, then manually pair up the rests.


Screenshot 2020-11-18 at 11.45.08.png

Thank you!

[Glenn Kennedy]

Will you please attach a SMALL sample Excel workbook (10-20 rows of data is usually enough)? However, please give us an indication of the approximate number of rows of data you want the solution to work with (100, 1000, 100,000 or whatever). Please DO NOT attach a picture of an Excel sheet (I do not have the patience to re-type any/all your stuff before starting).

1. It does NOT have to be your real sheet - mock up a SAMPLE if you need to. But not 1000's of rows!!! It makes manual checking so tedious. Whatever you do... make sure that all confidential information is removed first!!

2. Make sure that your sample data are truly REPRESENTATIVE of your real data. For example, don't show text in a column if it's really a number. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

3. Make sure that your desired solution is also shown (mock up the results manually). To be honest, I am not interested in seeing a non-working formula... or a pile of blank cells. However, I am very interested in seeing your EXPECTED results in their EXPECTED location.

4. Try not to use merged cells. They cause lots of problems and are DEFINITELY best avoided!

Unfortunately the attachment icon doesn't work at the moment. So,... instead click on Go Advanced (below the Edit Window) while composing your reply, then scroll down to and click on Manage Attachments and the Upload window will open. Click on Browse and navigate to (and double-click) the file icon that you want to attach, then click on Upload and then on Close this Window to return to the Edit window. When you have finished composing your post, click on Submit Post.

[CCST]

I have attached the mock up. In reality, I will have 150 names in the database, and approximately 100 names from each session. There will be 7 sessions in total.

My main difficulty is the variations of names. For example

• Abdullah Al Ateeq vs Abdulleh Ateeq

• Stephanie Tucker vs Stephanie M Tucker

Thank you.

[Glenn Kennedy]

Sorry, I misunderstood what you wnated..

So. Best way forward is a free add-in from Microsoft, called fuzzy lookup. Google it. It's easy to use, etc, etc.