Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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! |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Consolidating Data from Multiple lines | Excel Programming | |||
Consolidating data from multiple spreadsheets... | Excel Discussion (Misc queries) | |||
Consolidating data from multiple sheets. | Excel Discussion (Misc queries) | |||
need help in consolidating multiple data | Excel Discussion (Misc queries) | |||
Need advice : consolidating data from multiple CSV files in Excel - External data handling | Excel Programming |