Excel Help!

mattyh

mattyh

Suspended / Banned
Messages
7,021
Name
Matt
Edit My Images
Yes
Hi all,

Need a bit of help to save a colleague about 3 days of work - and make me a hero in the process!

I'm trying to compare data between two columns to see whether they're a match or not. Easy, you might say, using VLOOKUP. Not so, because the data isn't formatted the same, as the info has come from about 4 different sources.

For example.

Column A - Customer Name

Column D - Customer name & Address

So what I want is to compare the data and if some of the data matches, return a result, but, what I currently have is returning too much data.

I.E. All Round Holidays is returning something starting Alice, because some of the data matches (Al, at the start).

Is there a way of telling Excel how many characters to match?

Or any other way of doing it?

I can supply a sample of the data to search, but obviously I can't supply the whole DB!

Thanks
 
Matt

I hope I've understood what you want to achieve correctly: would adding True to your VLOOKUP do what you want as the formula then only returns complete matches?
 
Do the rows represent the same person or is the data jumbled?

If you want to send me an example I can have a look, in a previous life I was an Excel developer...but now very rusty.
 
mattyh said:
Hi all,

Need a bit of help to save a colleague about 3 days of work - and make me a hero in the process!

I'm trying to compare data between two columns to see whether they're a match or not. Easy, you might say, using VLOOKUP. Not so, because the data isn't formatted the same, as the info has come from about 4 different sources.

For example.

Column A - Customer Name

Column D - Customer name & Address

So what I want is to compare the data and if some of the data matches, return a result, but, what I currently have is returning too much data.

I.E. All Round Holidays is returning something starting Alice, because some of the data matches (Al, at the start).

Is there a way of telling Excel how many characters to match?

Or any other way of doing it?

I can supply a sample of the data to search, but obviously I can't supply the whole DB!

Thanks
Click to expand...

You can use the =LEFT function to return a specified number of characters from the beginning of the text string (or MID or RIGHT depending on where you want to pull the data from within the string).

Alternatively (or perhaps in conjunction with left), you may be able to use the LEN function which counts the length of a test string as part of your VLOOKUP.

If you're still struggling, let me know via private message and I'll let you have my email address. If you can provide a sample (along with the results you intend to get), I'll happily have a look for you.
 
Another thought that has just occurred to me (while lying in the bath as with all good "eureka" moments!) is that you may have missed the logical check off your VLOOKUP.

The full syntax of the formula is:
=VLOOKUP(lookupvalue,tablearray,columnindex,logicalcheck)

If you have the logicalcheck set to TRUE (or omitted all together), then the VLOOKUP will find the closest match. To force it to find exact matches, you need to finish the formula with FALSE

e.g =VLOOKUP(C2,$A$1 : $B$10,2,FALSE)
(ignore the spaces either side of the colon, the post was putting a smiley in!
 
I've done something similar in the past using a bit of VBA and crunching through the data. Alternatively would MATCH help, returning a value if it found your shorter section of text inside a longer string.

You can also use it in conjunction with LEFT, RIGHT and MID to chop bits out if for example you know there is always a comma after the name
 
Hi all,

We've tried various permutations of the VLOOKUP string, with varying degrees of success.. The data is jumbled and generally messy, so it's very hard to get anything sensible from Excel - for me, anyway.

I can email a copy of the spreadsheet over, for those that would like a copy (with some of the data stripped, of course!)

Thanks for your help - I thought LEFT would've done it, but there's no logical order to how the names are ordered, so it's not going to have it :(
 
There are two potentially useful commands called Find and Search, but you would need to be able to break up the seperate strings within the primary search column to apply them effectively.

The best online suggestion I can find is this one.

Take the column with the fewest strings (probably the "name" column") and seperate this into two or three columns (depending on the entry with the most strings), then run a Search function to check for matches.

How much is three days worth to your friend? ;)
 
Done some testing with a similar database of my own and the method I've described above works very well. The only awkward bit that prevents an emailable blank template being created is the maximum number of strings in the Name column. It could be accounted for with a macro, but for a one-off job it would take longer to write the VBA script than to do it!

To take into account common false matches (e.g. "the", "of", "co", "ltd", "street", etc) would need a lot more effort, and unless the database is thousands of entries long it may be easier to do manually. Hmm.. I wonder if that could be done with an array?
 
is the customer & address split by the '&' ?

if so just do Data - Text to Columns - and delimit by &
 
I'd be surprised if the text was seperated by an ampersand.. but there's no need to seperate all the columns.. if you seperate just the name column using a space as deliminator and then run Search for each seperated name string across the longer string in the name and address column.
 
Hi all,

Sorry for the non-reply today, been a bit of a mental day :D

I've had a couple of offers of help via PM/email, and I'm pleased to say that both of the guys managed to get a working solution, which I've passed onto my colleague!

Thanks to all for posting, and to the guys that Pm'd me for the solutions (and I didn't pass it off as my work either :nono:
 
You must log in or register to reply here.
Back
Top