Data Consolidation Question

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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 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.
 
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
 
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
 
Back
Top