Labels from Order Form

[jessicab148]

For my local NFP club we have got an order form that I made in Excel. We have got 89 Orders for superhero capes and im trying to make Lables to attach to the peoples orders once we have packed them. What i am trying to do is make possibly a Hlookup for each box so that it will automatically 'Fill in" the ordered Capes. Is this possible?
Capes SS.png

[AliGW]

It would help if you could upload the workbook.

[Mr_HiB]

Hi Jessicab148,

Because of the way your data is set up we won't be able to use any formulas in Excel to give you a summary of the labels you're looking for.

You'll have to do some massaging of the data to get you the labels. I know it's 89 customers.... but essentially my proposal is to copy the costume names on a secondary sheet (for point of reference let's assume you paste it in row 1), then repeat these steps for each customer:
(below my explanation there are pics for reference)

1. Copy their order quantities and line them up with the labels on the 2nd sheet; paste the qty in the row directly below the labels (in this example in row 2)
2. Copy the two rows you now have on the 2nd sheet and in an empty cell off to the side do a transpose paste (under home-on paste (click the down arrow key to get the many options of paste and pick transpose)
3. The list should now have the costumes and quantities going vertically, with the costumes in the left column and quantities in the right.
4. Sort (choose custom sort) the list by quantity largest to smallest (to get costumes with a qty to show up first).

This would be the labels you'll need for the first customer that you can print.

Repeat steps 1-4 for each customer pasting the quantities for each customer over the previous customer's quantity (paste in row 2).

The reason for this is because of the way your data is set up.
If you had the data stored in a table with headers such as: Customer Name, Costume Name, Quantity, Price. With a customer name repeating for each costume he/she orders then the data would be more manageable and we can use it for a pivot table even.

I understand that this will be monotonous and time consuming (but should not take more than 1.5 to 3 hrs); but the only other way to get around this issue is to write a macro to automate the steps above. But writing a macro can take a couple to a few hours to make sure that you have everything right. You would have to write or find code that can start you off and feel free to post in the forum if you have questions.

If you don't know anyone to help there are people who can provide you the service and you can also make a request on this website's commercial services forum (I think, not familiar with that myself).

Sometimes the work gets complicated because of the way the data is setup/structured.

But if you chose to do it for the 89 customers you currently have I've given some pics as reference for the steps I just talked about:

STARTING POINT
2015-10-10 Superheros 1.jpg


STEP 1 - data on 2nd sheet for example
2015-10-10 Superheros 2.jpg


STEP 2 (and results of step 3)
2015-10-10 Superheros 3.jpg

STEP 4
2015-10-10 Superheros 4.jpg