Solved

How to match 2 lists in excel with differences

Posted on 2016-09-23
11
51 Views
Last Modified: 2016-09-29
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
0
Comment
Question by:Joshua Smailes
11 Comments
 
LVL 19

Expert Comment

by:helpfinder
ID: 41812479
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
0
 
LVL 31

Expert Comment

by:Rob Henson
ID: 41812635
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
0
 
LVL 23

Expert Comment

by:Brian B
ID: 41812648
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.
0
 

Author Comment

by:Joshua Smailes
ID: 41816392
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
0
 
LVL 31

Expert Comment

by:Rob Henson
ID: 41817393
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.
0
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

Author Comment

by:Joshua Smailes
ID: 41821403
This has returned a #REF and I am not sure why
0
 
LVL 31

Expert Comment

by:Rob Henson
ID: 41821424
Which one??

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

Author Comment

by:Joshua Smailes
ID: 41821427
=VLOOKUP("*"&B2&"*",$A:$A,2,FALSE)
0
 
LVL 31

Accepted Solution

by:
Rob Henson earned 500 total points
ID: 41821432
Thought so, I was about to put another post on what could be the issue.

Replace with:

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

The lookup is only looking in one column (col A) and the offset was telling it to return results from column 2
1
 

Author Comment

by:Joshua Smailes
ID: 41821443
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
0
 
LVL 31

Expert Comment

by:Rob Henson
ID: 41821448
=IFERROR(VLOOKUP("*"&B2&"*",$A:$A,1,FALSE),"")
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

What is a Form List Box? (skip if you know this) The forms List Box is the alternative to the ActiveX list box. If you are using excel 2007, you first make sure you have a developer tab (click the Orb)->"Excel Options"->Popular->"Show Developer tab…
Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now