converting table to list

I am wanting to manage my inventory and need to convert spread sheets with tables specifying style, size color to lists with skus specifying style, size color. please see attached spread sheet with one sheet with table and second sheet with list.
thanks
malte

Use a combination of LEFT, RIGHT, MID and LEN functions to separate the code in column A to 3 separate columns. Then you can create a pivot table from your data to create your summary table.

Se attachment. Let me know if you need more explanation.

thank you. this process is clearly more than I understand at the moment but it appears that my request is doable. Any more explanation would be much appreciated.
Thanks
Malte

The first thing you have to do is split your product code into 3 separate parts: style, size and color. In your initial data sheet all these codes are put together as one code and to be able to sort these individualy you need them i 3 separate fields.

First lets look at the formula in cell C2: =LEFT(A2,2) . This gives your the first 2 letters of the code in A2. I noticed that this is always your Style code.

In D2 you have the colour code. This is always the last 2 caracters. Almost same formula: =RIGHT(A2,2)

The size code is a bit more tricky as this is sometimes one letter and sometimes two letters. The fact i use to determine the length is the fact that this is the only code that vary in length. So if the whole code is 5 letters this means the size code is 1 letter, and if the whole code is 6 letters the size code is 2 letters.
Formula in E2: =MID(A2,3,-4+LEN(A2))
LEN(A2) gives you the number om caracters in the code. So -4+LEN(A2) gives the result 1 or 2 depending on the number of caracters in the size code.
MID(A2,3,1) Gives you 1 letter from A2 starting with letter number 3.
MID(A2,3,2) Gives you 2 letters from A2 starting with letter number 3.

Let me know if you understand so far, before we go any further.

Attached is my complete project for your review

I see you have problem with the style codes beeing more than 2 caracters.
That makes things a bit more complicated. But it is still possible to do.
My attachment shows you how to do this. There are some restrictions:

• Color code must always be 2 characters
• Size code must be 1 or 2 characters, and the only codes with 2 characters is : XS, ML and XL
• Style code can be any number of characters, but last character can not be X og M.

I think these restrictions should be fine with the codes in your attachment.

I have also shown you the conversion the other way which are very easy.

That is brilliant, thanks.
what I now need to do is get my head around the pivot table part of this equation, it's new to me. When I play with the initial table you set up I can make changes to the row item quantities and refresh the table. How about making changes to a table and having that reflect in the rows?
thanks
Malte

Pivot tables are perfect for your needs. They are very flexible but have some limitations

You have to organise your source data in a list that can be treated as a database by Excel. This is simply a list like the one in my first example file. Headers in the first row and data in the following rows.

To create a pivot table just click in one of the cells in your list and let the Whizard do the job. You find it in the Data Menu. First time you do this just click thru everything until you get the table.

The table is at first empty, but have 4 areas where you can put your fields. You get a list of all your data fields (headers from your list) Just drag and drop these to the area in the table where you want them. You can later move them to other areas in the table and watch your layout change.

For your project i would recomend the following headers for your list:
Product, Style, Color, Size, Stock. You could also add Price etc.
The product field was not in my first example, but after seeing your latest attachment I would recomend this and put all your products in the same list. You can then easily create one pivot table for each product from the same list.

Back to the drag and drop job to set up your Pivot: Put Product in the Page area. Put Style and Color in the Row area. Put Size in the Colum area. And finally put Stock in the Data area. This should give you a table much like the ones in your attachment.

You might have to do some formatting. Just try some drag and drop for the fields to other areas of the table and watch it change layout. An important place to start with your formatting is to click in a cell containing data form the field you like to format and find "Field settings" from the "Pivot table" menu. There you can turn on and off subtotals, and make the data sum instead of count.

Many people have tried Pivot table and given up for never to return. This is mostly because the table didn't look like they wanted it to, they found subtotal rows or colums that made the table look weird, the data was counted instead summed og summed instead of counted etc. etc. It is all about formatting it the right way. I tried pivot table many years ago, and didn't catch the big fuss about this excellent tool. I never returned, until one day almost 10 years later i had a list with 30000 rows of data. I was wondering where to start. I then read somewhere a quick introduction to Pivot table, and tought lets try. I can only say one thing: Where have you been all my life ? It's been right where i worked every day, hiding behind a menu item with a strange name....

Just play around, and ask if you have any questions.

One important thing:
You can not change any data in the Pivot table and make this change be reflected in your database list. This is a one way transfer of data only. All changes must be made in your database list. For your purpose I can se why you ask about this, but in many other cases each cell in a pivot table is actually a sum of many, many rows of data.

You will probably find some links to more info if you search this forum.

Good luck !

I think I got it on the pivot tables.
What about getting the data converted from a table to a list, which is my primary need.

This is quite a complex lookup function. but it is possible to do. You can see the function in the N column in the attached sheet. The attachment shows you an example used on your T-shirt sheet.

You need to make the list of the unique lookuplabels that the funtion uses for lookup (column K, L and M). I did this with some copy, paste, and autofill. Maybe you already have this list ? Your style colum has merged cells and we needed the same list without merged cells. Column I shows you one way to do this. This is copied to column K with Paste Spesial -> Values, to convert it to constant names instead of formulas.

Hope this makes sence to you, and that you are able to understand the formula so you can convert it to your other sheets.

Tools -> Formula auditing -> Evaluate formula is a great tool to see what is actualy going on in a fomula.

Is there a way to keep the fields being generated from showing #VALUE if the evaluated field is empty? see example
thanks

You can replace the formula in B4:

=LEFT(A4,LEN(A4)-2)

with this formula:

=IF(A4="","",(LEFT(A4,LEN(A4)-2)))