Extracting data from a worksheet and sorting into another

[chrisinlakes]

Hi everyone,

I'm about to start a fair sized task where I have to use a spreadsheet to upload inventory data to a new POS system.
The data to be entered in being provided by our suppliers in spreadsheets of their making so there is no consistency in the formats of how they provide the data.
I will need to extract things such as product descriptions, product codes, prices and barcodes and put them into the upload template in the correct cells.

Is there an easy way to do this other than manually changing columns and pre sorting them to the upload template format and copying over?

I am still waiting on copies of the upload template and some source data before I can play around so am just trying to establish what is possible at the moment..

Thanks in advance...

Chris

 

[gplhl]

Send your suppliers your template? I had to do this with a new MRP system, the alternative is that you have to manually map each one of their spreadsheets into a master sheet and then ensure data format consistency is in line with your new system, i.e. text length, number formats, date formats, part numbers etc. How many suppliers are you dealing with?

 

[chrisinlakes]

Hi, thanks for replying.
I don't have exact numbers yet, but think it's around 15 to 20.
Your suggestion however doesn't answer my question.
If it was an option to get someone else to do the sorting, I wouldn't have asked.

 

[dreid1011]

You could potentially do it with some VBA, but there would still be a measure of manual labor to get columns or column headers as least into a workable order before running code on it. My thought is at the very least, you could normalize column headers across all sheets for the desired import data, and then search each sheet for those headers and go row by row importing and formatting the data as you need it.

 

[gplhl]

Hi, thanks for replying.
I don't have exact numbers yet, but think it's around 15 to 20.
Your suggestion however doesn't answer my question.
If it was an option to get someone else to do the sorting, I wouldn't have asked.

Thats not too many suppliers, I would manually map each one, you can do it by working out which column matches your master sheet, i.e. there column price column is 8, yours is 6. Temporarily for import, put above your name row a row for the mapping numbers, i.e. 8 above your price column as a reference to their column, use vlookup to bring the data in, copy and paste values, rinse and repeat.