Finding multiple coded values in a string, then returning a lookup value for each found code

Question

I have 600 item codes, and each one is coded with attributes in the overall item code. For example, we might have the plain item 600, then 600BK (Black body) and 600BKR(Black Body, Red colored detail) and 600BKR-YEL (Black body, red colored detail, Yellow base).

So I've got the list of item codes:

600
600BK
600BKR
600BKR-YEL

and then on a separate worksheet, an lookup list of each code and what it stands for:

BK    Black Body
R     Red Detail
YEL   Yellow Base 

I'd like to write a function that would find every code in the item title that is on the lookup sheet, and then return all of the corresponding values for each found code, preferably in one cell.

600
600BK         Black Body
600BKR        Black Body Red Detail
600BKR-YEL    Black Body Red Detail Yellow Base

Is this possible?

Answer 1

I would do something like the following. Assuming that your list of item codes is in column A of Sheet1 and, say, rows 2-20, and your lookup list is in columns A and B of Sheet2, rows 2-10:

  =IFERROR(VLOOKUP(MID(A2,4,2),Sheet2!$A$2:$B$4,2,0),"")&" "
  &IFERROR(VLOOKUP(MID(A2,6,1),Sheet2!$A$2:$B$4,2,0),"")&" "
  &IFERROR(VLOOKUP(MID(A2,8,3),Sheet2!$A$2:$B$4,2,0),"")

in cell B2 and then copied down for all the rows in Sheet1.

While it does put the lookups in a single formula, this approach would be very cumbersome if you have many codes. I would recommend that you insert helper rows in Sheet1 for each code position (e.g. mid(A2,4,2), and then concatenate them into a single string.

Answer 2

The short answer is "Yes", one way how is rather longer.

Since there are many possibilities not excluded in your question (such as plain items coded longer than three numbers or alphanumeric, more than three codes per plain item, codes varying in significance depending upon plain item etc) I’d suggest safest to start with parsing the codes. This should avoid complications with the likes of GR being either Grey Body with Red Detail (often a compatible colour scheme!) or Green Body.

Assuming these have been parsed (in three columns B:D starting in Row2) thereafter it is easy, with your lookup table (the one on the separate sheet) say named codes:

=VLOOKUP(B2,codes,2,0)&VLOOKUP(C2,codes,2,0)&VLOOKUP(D2,codes,2,0)

in your item codes sheet. To get spaces between the values I have assumed all the entries in the right hand column of your code array end in a space (easy enough to arrange such as with the likes of =A2&" " copied down, etc).

So the difficult part may be to parse the codes out of the item codes, for which I’d suggest adding columns as below (more if there are more than three codes):

with formulae as below:

ColumnB is to define where to start looking for codes (in case plain codes are other than three numeric). ColumnsC:D are for where to start searching for the next code/the length of the next code. I would agree not elegant, but relatively versatile. Make sure the parsing is correct on the left before working towards the right.

After successful parsing I suggest Copy/Paste Special/Values (to get rid of the formulae) then replace blank with a full stop (to avoid the lookup formula errorring out without making it more complex)*. Also, assuming throughout that your list of item codes is in ColumnA, delete ColumnsB:F before applying the lookup formula as above (or adjust the references to suit) -and add more lookups if necessary.

*Oversight: Make sure a full stop is added in a cell in each column of the lookup table.

Add columns for c5, c6 and c7 to allow for (a) maximum length 7 with (b) 'worst case' scenario (ie all single character).

Answer 3

Here is a version that uses the search function (non-case-sensitive version of find). The set-up is as follows.

In sheet 1, the codes to be looked up begin in column A of Sheet 1. The final result will be in column B. Columns C, D ,etc .have the unique codes arranged horizontally in row 1, i.e., "BK" in C1, "R" in D2, etc. The practical way to achieve this is to simply copy the code list in the lookup table and Paste Special Transpose them horizontally in cells C1, D1, etc.

Then first in cell B2 enter the following formula:

  =IF(NOT(ISERROR(SEARCH(C$1,$A2))),VLOOKUP(C$1,Sheet2!$A$2:$B$4,2,0),"")

Copy this formula across the rows from column C to how many code columns you Ceated in row 2.

Finally, in cell C2, concatenate all the results for row 2, i.e., the formula

 =D2&" "&E2&" "&F2

etc., for all the columns with codes in row 1. This step is tedious, but can be shorted with the following VBA function, which allows all the cells in a range to be concatenated:

  Function Concat(useThis As Range, Optional delim As String) As String
     ' this function will concatenate a range of cells and return the result as a single string
     ' useful when you have a large range of cells that you need to concatenate
     ' source: http://chandoo.org/wp/2008/05/28/how-to-add-a-range-of-cells-in-excel-concat/

  Dim retVal As String, dlm As String, cell As Range
  retVal = ""
  If delim = Null Then
     dlm = ""
  Else
     dlm = delim
  End If
  For Each cell In useThis
      If CStr(cell.Value) <> "" And CStr(cell.Value) <> " " Then
          retVal = retVal & CStr(cell.Value) & dlm
      End If
  Next
  If dlm <> "" Then
     retVal = Left(retVal, Len(retVal) - Len(dlm))
  End If
  Concat = retVal
  End Function

You would insert and copy this function into a module in Developer VBA. Usage is simple--concat(C1:D1," "), for example.

Note that this approach works for all 2-character codes, and all 1-character codes if they are not in the 2+ character codes, that is, if there are no code pairs such as "R" and "BR".