formatting multiple vendor price lists

[jkastning]

We currently have a few hundred vendors we order from for a retail business. We receive price lists in excel format, but they all differ in how they are formatted, i.e. one may have a column header of "UPC" and the next may say "UPC ID" and they may be in different columns. We currently format a handfull of these price lists so they have the same headers so we can easily compare costs, margins, etc, but we don't have the resources to do many more than this. What I am looking for help on is how to easily format multiple price lists with the same headers for different vendors. If anyone has done this or has ideas I would appreciate the help!

 

[mikerickson]

What do the price lists have in common?
For example, are the "UPC ID" and "UPC" in the same column for every vendor?

How can a poor dumb computer tell which column is which?

 

[jkastning]

Thats the problem, they may be in column A from one vendor and column N from the next. Some vendors keep it simple with only 10 or so columns and we have others that have up to 40. Management would like us to report this data from all vendors in the same format utilizing every possible column from each vendor. Today we "narrowed" this down to about 400 different column headings from the vendors.

 

[mikerickson]

As an overall scheme, I'd consider making column header charts

Vendors Ours
UPC UPC
UPC ID UPC
etc.

Include all the columns that you want to keep.

Then when you receive a vendor sheet, a macro could replace their headers with yours. And then re-arange their sheet so the columns that still have bad headers are together.
That should get most of it done, reducing the amount of hand work necessary.

A problem like this is going to involve hand work (i.e. human thinking). Eventually, you might be able to get all the vendor headings matched with yours. (Just in time for their upper management to decide to change their forms. )

I don't think that a macro to do everything is likely, but it could cut down the hand work.

 

[Rick Rothstein]

I'm not sure you have given us enough information for us to completely understand your problem. For example, are you trying to reformat (a copy) of each vendors list where their columns are ordered in some standard way? Or are you trying to located a specific subset of columns that each vendor has in common? How do you, as the human, identify the proper column? What is there about the names for these columns that tell you which one is which? Or did you have to "learn" how each vendor named their columns so you would recognize them when you saw them again. Remember, the computer is not smarter than you, so need to tell it (via code) what you know and how you know it in order to get it to do what you want it to.

 

[jkastning]

Yes, we are trying to reformat price lists from vendors so the columns are ordered in the same way. For example, we receive a price list from vendor A that has the UPC in column A, the cost in column B, the unit size in column C and so on. On our template for all vendors we will need to transfer this info into different columns. The columns are identifiable since they have common headings such as description, upc, etc but they may have a heading that is slightly different but contains the same info. For example instead of "description" the heading may be "item description" or "product desc". The main goal we are trying to accomplish is to cut down on the manual work of opening each workbook, finding the apporpriate column, then copying and pasting into our template. Most of our vendors update their price lists each month and since we have a few hundred active vendors its difficult for us to do very many of these.

 

[AndreiM]

Have you found a solution to your problem?