Why is it so difficult to match company names

VLOOKUP comparison of customer lists

The short way to the goal: job programming


Applies to: VLOOKUP comparison of customer lists
by: Günter F
Written on: 04/28/2003 - 12:48:36

Hello,
Unfortunately, I still don't have a satisfactory solution to my problem:

I am currently trying to match 2 customer lists using VLOOKUP.
It is strange that it worked when comparing company names with company names if the company name was spelled exactly the same;

but if I now compare the street and house number (a cell!), it doesn't work.

I have already tried various things: rearranging columns, transferring the format to ensure that the information is identical in terms of format; Comparison of spelling, e.g. number of spaces between street and house number - I can't find any difference.
I took out the column headings, although that shouldn't matter !?
It's not the formula! I checked; i.e. "FALSE" is included! I also sorted the street and house number in ascending order after a new tip from Klaus!

Does anyone know what it might be?
Does anyone have a knack? - With numbers it often helps if you take the numbers times 1 and insert them via "insert values"!
Would be nice!



  

Re: VLOOKUP comparison of customer lists
by: Christian
Written on: 04/28/2003 - 13:05:03

This will be a bit difficult because the function should actually work if you wrote both at the same time.
Maybe I would do a few checks to make sure that the text is really written the same way.
There are functions for this, e.g. = IDENTICAL (TEXT1; TEXT2) should a wrong appear here, you have to look where the spelling is different. Maybe delete the spaces by smoothing and then compare the number of characters by length. You will not be able to avoid revising your text again if IDENTICAL = FALSE.


  

Re: VLOOKUP comparison of customer lists
by: Günter F.
Written on: 04/28/2003 - 13:37:33

Thank you very much Christian !!!!
When comparing "Ídentisch" he brought "FALSE"
But the smoothing brought the desired success!

Learned something again! I didn’t know either function and I’m sure they will be useful again at some point!
I wish you a pleasant working day!


  

Re: VLOOKUP comparison of customer lists
by: Christian
Written on: 04/28/2003 - 13:49:22

I'm glad to have helped you. In the meantime I have thought about something that you no longer need but can help you with such problems in the future. With the following problems you exclude the errors of different lowercase / uppercase letters and the number of spaces and the last possible error is only different types of spelling.
Your address is in cell A1, in cell B1: = GLÄTTEN (LEFT (A1; SUCHEN (""; A1; 1) -1))
in cell C1: = SMOOTH (RIGHT (A1, LENGTH (A1) -SEARCH (""; A1, 1)))
and in cell D1: = LARGE (B1 & "" & C1)
With this you separate the street and house number, delete the spaces and put both back together in capital letters.

greeting
Christian

Contributions from the Excel examples on the subject of "Beep with a difference"