How to assign a value w/inconsistent data

See if this "AutoFilter" works for you..if not let me know, we might can do some fancier things if need be.
See attached.
Select the drop down arrow for your sizes and select a size. It will list all of the products with that particular size. I took the liberty of re-arranging the data to make the AutoFilter work(it doesn't like merged cells or spaces in the data).
To see the whole list, go under Data/Filter/ShowAll

You can sort by any of the dropdown arrows or combination of arrows

Hi hermanexcel,

I'm wondering if you are selling products, and have a Medium size Widget 3654, how you would look it up? The only sizes for this Widget is S-XL. Would listing every possible size for every product make your problem easier? The reason I ask is that these Sizes are not numeric or alphabetic and therefore very hard to lookup from Excel. Excel doesn't know if ML is between S and XL or if XL is between M and DXL.

If you had an expansion of all possible sized it might make your lookup table a little larger (maybe 5 times bigger) but would allow exact lookup information. Then you could change the master file and do easy price changes and lookups for sales.

The problem is, we don't know how each manufacturer has their sizes labeled.

Adding all sizes would be pretty much by hand, as you said there isn't a standard between manufacturers. If I was doing it myself, I'd get good with copy and paste keys. BUT - when you have a complete list, looking up single sizes going out the door might be more accurate. That being said, if you don't care about size then you may not need to list all sizes in separate rows.

Marvin,
Can we use a LOOKUP function based on the criteria he has on Sheet2 in Cols "D:E"? to return all matches for a given size?

Hi John,

There are certainly different ways to solve this puzzle. Here is how I imagine the problem. I'm selling a widget Number 10210 and as I put it in the shipping box I read it is a size M. I then need to find out how much to charge. I look and see there are two prices. 23.16 or 25.54 ? I look at the size chart and it shows S-XL and 2XL-3XL. Neither of these match to a M.

To do an exact lookup I need an M in my database. If the last parameter on the Lookup function (Range_Lookup) is TRUE then we could do a relative lookup. That is, it would run through the range until it found the first value larger (number or alpha) and drop back to the one just before that. These size numbers and letters aren't alphabetical so that won't work.

I think what is needed is a new sorting criteria for the VLookup function to tell when the size letter is larger if the TRUE parameter is used.

This comes back to the question "Is M in the S-XL group or the 2XL-3XL group?".

My suggestion was to make a bigger table that had all sizes listed so an exact match VLookup could be performed.

All the above is complete imaginary on how the user may need to use Excel. If there was some method for leaving out the Size and/or Color criteria to do price lookups they could use that.

I'm ready to be wrong on this answer, but believe with different standards on color and size this problem avoids a good formula to give back correct prices.

Originally Posted by MarvinP

To do an exact lookup I need an M in my database. If the last parameter on the Lookup function (Range_Lookup) is TRUE then we could do a relative lookup. That is, it would run through the range until it found the first value larger (number or alpha) and drop back to the one just before that. These size numbers and letters aren't alphabetical so that won't work.

This comes back to the question "Is M in the S-XL group or the 2XL-3XL group?".

My suggestion was to make a bigger table that had all sizes listed so an exact match VLookup could be performed.

He has done some of this work on Sheet2 regarding the sizes in Cols "A:B" and Since "M" falls into the category "1" ...I think we can use that as the Table to LOOKUP on Sheet1, but I'm concerned about the merged cells and gaps in the data which might give funky returns..ie some rows will not have a description because the description is only listed once in the first row of that item's data.

Hi John
I did see the table on sheet 2. Did you see on sheet 1 the prices based on colors too? What color is PFD anyhow? And, I'm not sure I'd know a color Neutral from White or Heather.

This reminds me of the Auto Shop parts problem. Each manufacturer has thier own standard in marking their part. There is no standard (maybe it has changed since I taught Auto Shop?).

Keep working on it and see if the user can group or limit prices based on product number, color and size. Maybe all they need is an AutoFilter to show their options and they choose from there?

Hi,

I'm looking for the Questions in your above posts to try to answer them.
1. I believe you need to fill in the blanks cells in Columns A and B (at a minimum) or your original data.
2. After doing 1 try AutoFilters to see if it helps.
3. If 2 doesn't help answer the question then start adding rows to specify each individual size and color.

hope that helps.