-2

Good afternoon StackOverflow.

I've been asked by my boss to make his life easier with this particular spreadsheet.

What I do: Stock Control

What the spreadsheet needs to do:

The spreadsheet has a master list of base stock codes, next to these codes is the amount of time it takes to make an item. So A2 might have an item, A3:A9 holds the time in minutes.

Another list will be pulled off, of what has been made, this list needs to compare to the Master, and automatically populate the times across the relevant columns.

A big issue... This cannot be helped.... The way the stock codes are put together is the biggest issue. A typical stock code is:

Item + Wood Colour + Fabric Colour

Example: CHE+PO+200300 - Would be a Chelford Chair + Pippy Oak + Ambla Green.

However, there are stock codes such as CHEL, CHELL, which have completely different manufacture times.

The lists can get pretty large as well.

I can also provide a sample master list, and a sample stock code list... Can anyone help?

Myth
  • 1
  • 1
  • 1
    You're question is unclear what you actually want. It also appears you want someone to build the spreadsheet for you. – bowlturner Jun 23 '15 at 16:57
  • No, I have the spreadsheet. Like I said, I can provide examples. The spreadsheet needs to compare a list against a list, which, for the most part, will not match 100%. I thought about using Left to truncate the comparable stock codes to before the + that joins them, but then I noticed some of the master stock codes have the + character in them. – Myth Jun 23 '15 at 17:01

1 Answers1

0

As I understand the question, you want, when you enter the code the time will populate from Master Sheet, in this case you can use the Vlookup

MABBAS
  • 3
  • 3
  • I've thought of Vlookup, but that would mean changing the layout of the data, I've also tried index and match, but then I have the issues of the stock codes, and I'm not entirely sure how to construct a formula that would fill the 8 or so cells. – Myth Jun 23 '15 at 17:06
  • will you post some sample data from both sheet – MABBAS Jun 23 '15 at 17:12
  • Here is a link to what I have. It's stored on my OneDrive. The first sheet is the codes that need comparing, second sheet is master, sheet 3&4 can pretty much be ignored, they're where I was messing with the Left formula (http://1drv.ms/1BKc9iI) – Myth Jun 23 '15 at 17:22
  • where do you want formula? – MABBAS Jun 23 '15 at 17:40
  • Anywhere as long as it works to be honest. Although a new sheet would probably be best... Why, do you have something that may work? – Myth Jun 23 '15 at 17:44
  • =VLOOKUP("*"&A2&"*",Sheet1!A:B,2,FALSE), formula check the A2 if the same text contain in array will bring you result. – MABBAS Jun 23 '15 at 17:45
  • Sorry for deleting comment, it was misleading. I've moved about the formula a lot, and it's not doing anything close to what I need. It will only return a full stock code. I need it to return a result from the master table, if the stock code from the master table matches a portion of the stock code in the first sheet. – Myth Jun 23 '15 at 18:58