Working on Specfic Text in a cell and compute with a lookuptable and provide result

Hi Friends,

Good Day!. I am very glad to see this website.

I am a MBA Student. I am doing my project in Inventory Management. In My Excel Sheet I have to segregate the items. Let me Explain.

Item Description Holds Item Number, Item size Variant 1 and Variant 2. Based on item No VAriant1 & Variant 2 l Above i Have to assign a value.

FYI: Item no is unique and all others are different.

Please help me out. Thanks in Advance.

Encl: Practice.xlsx

Regards,
Prasad

First, we do not do your homework for you. If you have made progress and have a specific question about an Excel feature that is giving you trouble, we are happy to help. But please do not post your assignment and ask us to complete it for you. That is what you are supposed to be learning in your class.

We can help give you some guidance, but I don't understand what you want your result to look like. Where do the numbers come from in the Unit column? Do you have to consider Quantity in any way? The items in rows 10-13 have no "item number" in their codes. What do you want to do with those?

I concur with the above. Based on what I am seeing I make several recommendations.

First of all you have Excel 2013. This means you have Excel Tables. You don't need Excel Tables to solve this issue, but they do make using Excel a lot easier. In many cases you have to "guess" how much data you have and then write your formulas to match exactly that many rows. If the amount of data changes, then you have to rewrite the formulas. Excel tables know how big they are. Also Excel tables do things like copy down formulas automatically, and you can refer to them by Column Heading such as Table_Data[Item Description] instead of 'Sheet Name'!$A2:$A13. Here is some information to get you started on tables. http://www.utteraccess.com/wiki/Tables_in_Excel - I suggest that when you have time, you study it. Tables will come in very handy later on.

As for this particular problem you are going to have to separate out parts between the dashes. Two formulas that will come in handy are FIND() and IFERROR(). Read up on them. Once you figure out these formulas, then you can put them in "helper" columns and apply a pivot table to the result.

P.S. https://exceljet.net/formula/count-s...ters-in-a-cell might also come in handy.

You should not need lookup tables.

Make several helper columns and copy the formulas down in each.

Helper column 1: Use the link in my second post to determine how many dashes there are in the string. Use and if statement to determine if you are dealing with a short string (3 dashes) or a long string (4 dashes).

Helper columns 2-5: Use the find command to determine the positions of the 2nd, 3rd and 4th dashes. Note that for short strings, the position of the 4th dash does not exist so it will be an error so use IFERROR.

Helper column 6-8: use an if statement against helper column 1 to determine what part of the string is Var1, what part is Var2, etc.

Apply a pivot table to the whole thing.

Or you can try something entirely different.

Insert 4 columns between Column A and Column B. Apply text-to-columns to column A. this will give you 4 or 5 columns of data. You can count up the number of columns that have data (COUNTA) to determine if you are dealing with a long string or a short string. Use an if statement to determine which columns are Var1, Var2, etc.

Put header titles over each column of data. Apply a pivot table.

That's about as explicit as I can get without actually doing the problem for you.