Consolidating multiple data
 

Hi,

I hope I'm posting this in the correct forum.

I've got Sales(and a few more data points) for ~10,000 customers over 3 years and I'm looking to consolidate the same so I could probably VLOOKUP the sales(and other) data year-wise.

The issue I'm facing is that the names of the customers are inconsistent among years. The common reasons for inconsistency a

1) Extra spaces at the end of a company's names [Eliminated this problem with the "TRIM" function]

2) A company's name maybe "ABC, INC" in one year and "ABC INC." or "ABC INC" or "ABC/INC" in the other years.

I've tried a whole bunch of stuff to eliminate this problem. Few of them being delimit, trying to use the "LEFT" function but didn't pull accurately, tried VLOOKUP with the closest match(1 instead of 0 at the end) but no luck.

If anyone has any sort of idea on how to go about this, please let me know.

An idea which I came up with but not sure if it'll work or how to really implement this is pull all but the last part of a customer name.

e.g., If XYZ ASSOCIATES, INC. was the name of the company, I'm looking to pull just XYZ Associates. Please be weary that the name may be "ABC DEF ASSOCATES INC." and need not always be just 3 parts. Also I won't be able to replace the ", INC." as it isn't consistent across customer names.

Hope you're getting a fair idea of what I'm trying to get at.

Any suggestion are most welcome!

Thanks!

Cleaning up the data may be your only hope. Sort by company name and you will get them next to each other then copy and paste the right one thru the spreadsheet.

You could also make another column next to the company name and use the =SUBSTITUTE or =REPLACE to remove the punctuation/spaces that might be different.
=SUBSTITUTE (SUBSTITUTE(SUBSTITUTE (a2," ",""),"/",""),".","") will get out the spaces, / slashes, and periods

I also find INDEX(array2,MATCH(value,array1,0)) better than VLookup.