Need to overhaul picklist; break out sizes into rows, correct columns sharing meaning

Hey guys,

I'm trying to re-organize my inventory report spreadsheet.

It has two issues.

1. The Inventory is listed one style per row, and each column represents a size, and the Quanity on hand of that size for that Item.

I would like it to instead have a new row for each size clearly stating the size next to it, so there is not need to look up and trace down to find the size you're looking at.

2. The columns share a location in the report, but they mean different sizes depending on Gender. All Female sizes are one up from Men's So A Mans item in the first row is an X-Small, but if it's a Womans item in the first row, it's a Small.
This leads to lots of confusion, particularly on large lists where the items are towards the middle of the page.

I'm hoping someone could give me a recommendation on the best strategy to take for this? I'm new to VB, i'm doing this for another department to help them do their job, so I don't have a budget or I would just hire someone so I'm trying to make this happen for them? I appreciate any help I can get.

So, I've got this:

ORD__Item______XSM(smf)_SMM(mdf)_MDM(lgf)____LGM(xlf)____XLM(2xlf)__GEN  <--- this column is the indicator what each columns size is MALE(female)
101__redwidget_____0_______1__________0__________0___________0______MA
102__Pinkwidget____0_______0__________2__________0___________0______FE
102__whiteWidget___1_______0__________0__________1___________0______FE
103__redwidget_____0_______1__________1__________0___________0______MA

and I'm trying to get this

ORD__Item_________Size____________Qty
101__redwidget____Mens Small_______1
102__Pinkwidget___Womens Large_____2
102__whiteWidget__Womens Small_____1
102__whiteWidget__Womens Large_____1
103__redwidget____Mens Small_______1
103__redwidget____Mens Medium______1

Re: Need to overhaul picklist; break out sizes into rows, correct columns sharing mea

Quote from StephenR;770320

Welcome to Ozgrid.

I'm not sure what you're asking. You're more likely to get a fitting solution if you post a workbook with some sample data and an indication of your desired results.

Hi Stephen, thanks for the welcome. I apologize if the charts I posted of my existing and desired spreadsheet layouts were not clear enough..... is there a way to upload an spreadsheet without code? that might be easier?

Essentially, I have an inventory list of products.

Currently the list is structured so that there is one "Product group" per row, and all sizes and inventory quantity of each size are indicated by which column the items are in and the number in the cel where the item row and size meet.

I would like instead for each size of each product to exist on their own row with the quantity next to it.

To complicate things, the columns mean different sizes for women's or men's. They are off by one, so a Men's Medium is a Women's Large.

I'm hope someone could guide me on how to turn my current spreadsheet at the top of my original post into the preferred version with more rows and one correct size and quantity per row that does not have to be figured out by the "person" looking at the print out.

I'm guessing I can do like an array, but I don't know how to do that and change the sizing, or if I should do it first or later. or even if that's the right path?