Avatar of Joshua Smailes
Joshua Smailes
 asked on

How to match 2 lists in excel with differences

I am trying to match 2 lists in excel to find unique values. One list has data after the data I am trying to match and I am having an issue trying to add a wildcard "*" to the data. I have tried a conditional formatting approach but the lists are very large over (19000 rows) and excel freezes when I try to filter it.

Any help much appreciated
Microsoft Excel

Avatar of undefined
Last Comment
Rob Henson

8/22/2022 - Mon
helpfinder

if you want to  comapare original data to new data (so you want to find where is difference) you can use VLOOKUP formula.

Best would be if you can post sample excel file (e.g. with 10 rows of original data and new data) so we can look at the content and suggest best approach
Rob Henson

One list has data after the data I am trying to match and I am having an issue trying to add a wildcard "*" to the data.

I assume you mean you have lists something like:

List 1:    "Original text"
List 2:    "Original text with some more"

Finding the List 1 value in List 2 should be fairly simple:

=VLOOKUP("Original text"&"*",List2,Offset,False)

Going the other way will be more difficult unless there is a common point at which the text changes, eg after the first 15 characters. For something like that:

=VLOOKUP(LEFT("Original text with some more",15),List1,Offset,False)

Thanks
Rob H
Brian B

Instead of a wildcard, you might be able to simplify the problem by adding another column that strips out the extra data for you. So instead of searching for "abc*" in column A, you create another column that only displays LEFT(<column A>, 3) and search that column for just "abc".

The above can be hidden if required. It could also be rolled into a larger vlookup type of formula, but perhaps this make it easier to get a working formula built.
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
Joshua Smailes

ASKER
One list has spaces after the data I am trying to match. There can also be data before the data I am trying to match. The data is chassis VIN numbers i.e XLRTGH4300G239385 and I am only trying to match the G239385 as this is the part that will be common in both lists. After the G239385 there is 12 spaces in list 1
Rob Henson

With which value are you trying to do the lookup, the longer or the shorter value?

As I mentioned, finding the shorter value of G239385 in the longer value list is relatively simple:

=VLOOKUP("*"&"G239385"&"*",LongValueList,2,False)   replace the italic with cell reference containing short VIN.

If doing the other way round, looking for the longer value in the shorter value list, if the required text is always the last 7 characters then:

=VLOOKUP(RIGHT("XLRTGH4300G239385",7),ShortValueList,2,False)   replace the italic with cell reference containing long VIN.

If the lookup value has spare spaces use TRIM function to remove. If the list that you are looking at has spare spaces then that is more tricky.
Joshua Smailes

ASKER
This has returned a #REF and I am not sure why
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Rob Henson

Which one??

Copy and paste the formula here exactly how you have it and I can see if I can spot the error.
Joshua Smailes

ASKER
=VLOOKUP("*"&B2&"*",$A:$A,2,FALSE)
ASKER CERTIFIED SOLUTION
Rob Henson

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Joshua Smailes

ASKER
That's great thanks is there a way I can filter out the blanks or make the N/As returns blanks so I can filter
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
Rob Henson

=IFERROR(VLOOKUP("*"&B2&"*",$A:$A,1,FALSE),"")