Price List Comparator

[RegionX]

Hi I'm new here and to VBA in general. I'm hoping to not start from scratch and thought someone may have worked on something similar to what I need.

I get price lists from various suppliers as a spreadsheet. All the products are listed as a size consisting of 3 numbers. The lists are all in completely different layouts but I need to extract the size and price information and create a table to compare the prices of each supplier. We have over 50 price lists and get more weekly so I need to automate this to some extent.

My thinking is to have a series of prompts that determine where the size column is and then where the price column is. From there it should be straight forward.

Has anyone got a better idea or have a similar project I can look at?

[SamT]

I would create a "Collator" sheet: starting in 1A, and in each third column across the sheet, place the List Sheet or Supplier name. Under each such Name, list the existing column #, next to that, list the desired column #.

This particular layout allows the use of Set/Let Collation = Rows(1).Find(SheetName).CurrentRegion where Collation is a Range or an Array.

Looking at the two column number lists in the Collation, merely Move or Copy the Existing Column to the Desired Column, either in the existing List sheet or to a new Master List Sheet.

1)This allows a very short VBA loop to handle all Lists in very short order (1 to 2 seconds for 50 Lists)
2)A minor functionary can create the Collator sheet
3)The Supplier/Sheet Name is available for every Columns Set
4)An If Not Collation is Nothing lets it handle Supplier Lists that aren't on the Collator sheet. The same statement can return an error msg to the User when a new uncollated Supplier List is used.
5)At a later date more columns can be Collated

[Logit]

Post samples of the data that you receive so they can be examined and better understood.
Without the actual data you are receiving, any answer is probably just a guess.

When you post the data ... make certain it is the actual spreadsheet and not an image.