 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / February 2005Data Consolidation Question
I have SEVERAL vendors, by name, that are listed different ways - Example: ABC Co may be ABC Co or ABC Company or abc co or ABC Co., Inc. I want to consolidate "like" names within the column on a workbook and also consolodate with "like" names from another workbook to make one vendor list (and also consolidate all the rest of the columns in doing so) - (the spend to date) column Can I do this It's difficult to help you with this without knowing more about what you have. Excel works in a logical manner. By this I mean that you have to provide Excel with some logic to allow it to conclude that this and that are the same. For instance, are any two or more companies with the same first word, regardless of case, always the same company? Or can you say that "co", regardless of case, is always the same as "company"? Or the presence of "Inc.", regardless of case, can be ignored. Or it can't be ignored? Think about what you have and see if you can come up with some logic. Pretend that you are explaining how to do this to some new employee that just walked in off the street and who has no idea of what you are about to say. You can also take this in steps. For instance, first group all the entries with ABC as the first 3 characters. Or soandso as the first word. HTH Otto >I have SEVERAL vendors, by name, that are listed different ways - Example: > ABC Co may be ABC Co or ABC Company or abc co or ABC Co., Inc. [quoted text clipped - 8 lines] > > Can I do this I think what I want to do is say something like - If the first 10 characters are the same, disregarding the case, then consolidate the data. > It's difficult to help you with this without knowing more about what you > have. Excel works in a logical manner. By this I mean that you have to [quoted text clipped - 22 lines] > > > > Can I do this One way to this: Copy all the data from one workbook and paste it at the bottom of the other data in the other workbook. You now have all the data on one sheet in one workbook. Now sort all the data on the column that has the vendor names. All the vendors that have the same first 10 characters will be together. Is this what you want? HTH Otto >I think what I want to do is say something like - If the first 10 >characters [quoted text clipped - 32 lines] >> > >> > Can I do this If I were doing this, I'd probably use a Pivot table to find all of the names that are used and then standardize from there. I'd then define a lookup table from the pivot chart that identifies what you want to use for all of them and use the VLOOKUP function to standardize the data. > I have SEVERAL vendors, by name, that are listed different ways - Example: > ABC Co may be ABC Co or ABC Company or abc co or ABC Co., Inc. [quoted text clipped - 5 lines] > > Can I do this What's a VLOOKUP? What I think I want to do is say something like; if the first 10 characters are the same, then combine the data & disregard case > If I were doing this, I'd probably use a Pivot table to find all of the > names that are used and then standardize from there. I'd then define a [quoted text clipped - 13 lines] > > > > Can I do this |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.