Converting multiple spread sheets in to 1 sheet

**RichardWard** · 07-24-2013, 09:58 AM

Hi,

I have been asked if we can convert our pick sheets into one sheet for upload into Sage. Attached is a sample of the master template for Sage...the actual has about 4500 lines of data. I have also attached a couple of sample pick sheets which are created for the customers. We may have 30 of these pick sheets at one time (usually containing much more data) that need to be converted into the Sage format. I currently have to open up a pick sheet, then search for the product in the master sheet and update the quantity for each style / colour / size, then do the same for the next pick sheet and so on... The only area on the master sheet that needs to be filled in is the quantity

Can anyone help me with a quicker solution...please let me know if you need any further info?

Thanks

Richard

**DavidJAllen** · 07-29-2013, 06:34 AM

Richard,

I can't quite understand what you are trying to do here. Can you run through an example with a specific product so I can see what you are trying to do? When I look at the product codes on the pick sheet they do not directly relate to a code on the master sheet. Are you wanting the 30 pick sheets to be combined into the All Codes Master format?

If I can understand I can help.

Regards

David

**RichardWard** · 08-01-2013, 06:10 AM

Hi David,

Thanks for the reply...

The codes do not directly match...I thought that may be a problem...the codes on the master sheet are specific for Sage...but can not be used on the pick sheets. Would I need to add another column on the master sheet that does match the pick sheet, which can then be removed for uploading into Sage?

Yes I would like to combine the 30 pick sheets into a master sheet format. If you look at the master sheet...this contains the information from the 2 pick sheets that I have manually entered. The only column that requires updating on the master sheet is the Quantity column.

Thanks again for the reply...let me know if you need any further information.

Richard

**DavidJAllen** · 08-02-2013, 05:03 AM

Richard,

We should be able to derive the master code. It seems to me it is as follows:

Code first (e.g. LUCHZ)
first to numbers in the description field
colour code
size

In the attached you will see I have an excel formula to derive the master code, please check if this is correct.

If so I should not be difficult to write a macro to open 30 files and build the master code sheet.

Regards

David

**RichardWard** · 08-07-2013, 01:22 PM

Hi David,

Sorry for the delay in coming back to you...I have been away for a few days.

You are correct that it is the code first, but this can consist of 3 - 7 letters

The numbers in the description field can consist of 1 - 4 numbers

There are a lot of colours on the full master sheet

Will this be an issue?

Regards

Richard

**DavidJAllen** · 08-19-2013, 04:58 PM

Richard,

Different length codes are not an issue. See attached 3 files, do the following

1) download to a temporary directory
2) open All Codes Master, note quantities are zero
3) open ColourCodesTblWB, note lookup table
4) open Pick Sheet Sample 1, run the macro. This transfers all the quantities to the all codes master

The algorithm is as follows:

1) loop through the pick sheet quantities in range E14:J27
2) if a quantity is > 0 derive the product code
3) loop through product codes in All Codes Master workbook, when it matches write the quantity.

Code is below.

Please Login or Register  to view this content.

The only other thing that needs doing is to add an extra loop to run through all your pick sheets. Let me know what you think and lets enhance from there.

Regards

David