Select a value from a comma separated list based on a serial number

[Mading]

Hi

I have a list of student needs (comma separated) in one column, with the rank of those needs (also comma separated) in another column, but the problem is, the rank is not always in a logical numerical order:


[TABLE="class: grid, width: 300"]
<tbody>[TR]
[TD]Column A[/TD]
[TD]Column B[/TD]
[/TR]
[TR]
[TD]ASD, SEMH[/TD]
[TD]1,2[/TD]
[/TR]
[TR]
[TD]SEMH, MLD[/TD]
[TD]2,1[/TD]
[/TR]
[TR]
[TD]SLCN,SEMH,ASD[/TD]
[TD]2,1,3[/TD]
[/TR]
</tbody>[/TABLE]


So I need a formula which will allow me to look down column B and search for the serial number I specify (in this case "1") then look down column A to find the "need" that corresponds to the position of the serial number "1" in column B, so that, in this example, column C would look like this:


[TABLE="class: grid, width: 300"]
<tbody>[TR]
[TD]Column A[/TD]
[TD]Column B[/TD]
[TD]Column C[/TD]
[/TR]
[TR]
[TD]ASD, SEMH[/TD]
[TD]1,2[/TD]
[TD]ASD[/TD]
[/TR]
[TR]
[TD]SEMH, MLD[/TD]
[TD]2,1[/TD]
[TD]MLD[/TD]
[/TR]
[TR]
[TD]SLCN,SEMH,ASD[/TD]
[TD]2,1,3[/TD]
[TD]SEMH[/TD]
[/TR]
</tbody>[/TABLE]


Is such a thing even possible?

Thanks in advance

Excel 2016

 

[Marcelo Branco]

Question
The serial numbers (column B) always have just one digit, i.e., 1 to 9?

M.

 

[hiker95]

Mading,

Here is a macro solution for you to consider based on the raw data structure you have displayed.

Sample raw data in the active worksheet:

Excel 2007
	A	B	C
1	ASD, SEMH	1,2
2	SEMH, MLD	2,1
3	SLCN,SEMH,ASD	2,1,3
4
Sheet1

And, after the macro in the active worksheet:

Excel 2007
	A	B	C
1	ASD, SEMH	1,2	ASD
2	SEMH, MLD	2,1	MLD
3	SLCN,SEMH,ASD	2,1,3	SEMH
4
Sheet1

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code
2. Open your NEW workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Sub FindSerialNumberText()
' hiker95, 05/13/2016, ME941020
Dim r As Range, sa, sb, n As Long
Application.ScreenUpdating = False
With ActiveSheet
  .Columns(3).ClearContents
  For Each r In .Range("B1", .Range("B" & Rows.Count).End(xlUp))
    sb = Split(r, ",")
    n = sb(0)
    If InStr(r.Offset(, -1), ", ") Then
      sa = Split(r.Offset(, -1), ", ")
      r.Offset(, 1) = sa(n - 1)
    Else
      sa = Split(r.Offset(, -1), ",")
      r.Offset(, 1) = sa(n - 1)
    End If
  Next r
  .Columns(3).AutoFit
End With
Application.ScreenUpdating = True
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm, and, answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

Then run the FindSerialNumberText macro.

 

[Peter_SSs]

See if this does what you want.
If so, the formula could be simplified a little if your data was more consistent: Your first 2 sample rows have a comma and a space between the items in column A, but the third row only has a comma.

Formula in C2 copied down.

Excel Workbook
	A	B	C	D	E
1					Item of Interest
2	ASD, SEMH	1,2	ASD		1
3	SEMH, MLD	2,1	MLD
4	SLCN,SEMH,ASD	2,1,3	SEMH
Extract Item

 

[Marcelo Branco]

Or maybe this UDF (User Defined Function)

Function FindNeed(strNeeds As String, strRank As String, lRank As Long)
    FindNeed = Application.Index(Split(Replace(strNeeds, " ", ""), ","), _
        Application.Match(CStr(lRank), Split(Replace(strRank, " ", ""), ","), 0))
End Function

Usage
Put the serial number of interest in D2

Formula in C2 copied down
=FindNeed(A2,B2,$D$2)


[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

A​

[/TD]
[TD="bgcolor: #DCE6F1"]

B​

[/TD]
[TD="bgcolor: #DCE6F1"]

C​

[/TD]
[TD="bgcolor: #DCE6F1"]

D​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1​

[/TD]
[TD]

Needs​

[/TD]
[TD]

Ranks​

[/TD]
[TD]

Result​

[/TD]
[TD]

Search​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD]

ASD,SEMH​

[/TD]
[TD]

1, 2​

[/TD]
[TD]

ASD​

[/TD]
[TD]

1​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

3​

[/TD]
[TD]

SEMH, MLD​

[/TD]
[TD]

2,1​

[/TD]
[TD]

MLD​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

4​

[/TD]
[TD]

SLCN,SEMH,ASD​

[/TD]
[TD]

2, 1, 3​

[/TD]
[TD]

SEMH​

[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Hope this helps

M.

 

[Peter_SSs]

Or possibly this variation of Marcelo's UDF.

Function GetNeed(strNeeds As String, strRank As String, lRank As Long)
  GetNeed = Trim(Split(strNeeds, ",")(Application.Match(CStr(lRank), Split(strRank, ","), 0) - 1))
End Function

Excel Workbook
	A	B	C	D
1				Item of Interest
2	ASD, SEMH	1,2	SEMH	2
3	SEMH, MLD	2,1	SEMH
4	SLCN,SEMH,ASD	2,1,3	SLCN
Extract Item (2)

 

[Marcelo Branco]

Or..just for fun
Another solution using formula - works only if the ranks have just one digit 1 to 9


[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]

A​

[/td][td="bgcolor: #DCE6F1"]

B​

[/td][td="bgcolor: #DCE6F1"]

C​

[/td][td="bgcolor: #DCE6F1"]

D​

[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]

1​

[/td][td]

Needs​

[/td][td]

Ranks​

[/td][td]

Column C​

[/td][td]

Search​

[/td][/tr]


[tr][td="bgcolor: #DCE6F1"]

2​

[/td][td]

ASD,SEMH​

[/td][td]

1, 2​

[/td][td]

ASD​

[/td][td]

1​

[/td][/tr]


[tr][td="bgcolor: #DCE6F1"]

3​

[/td][td]

SEMH, MLD​

[/td][td]

2,1​

[/td][td]

MLD​

[/td][td][/td][/tr]


[tr][td="bgcolor: #DCE6F1"]

4​

[/td][td]

SLCN,SEMH,ASD​

[/td][td]

2, 1, 3​

[/td][td]

SEMH​

[/td][td][/td][/tr]
[/table]

Formula in C2 copied down
=IFERROR(TRIM(MID(SUBSTITUTE(","&A2,",",REPT(" ",500)),(INT(SEARCH($D$2,SUBSTITUTE(B2," ",""))/2)+1)*500,500)),"")

M.

 

[Peter_SSs]

Or..just for fun

OK


Another solution using formula - works only if the ranks have just one digit 1 to 9 (& given the possible introduction of spaces in column B per your latest sample) I think that can be trimmed down some more.

Excel Workbook
	A	B	C	D
1				Item of Interest
2	ASD, SEMH	1, 2	ASD	1
3	SEMH, MLD	2,1	MLD
4	SLCN,SEMH,ASD	2, 1, 3	SEMH
Extract Item (3)

 

[Mading]

OK


Another solution using formula - works only if the ranks have just one digit 1 to 9 (& given the possible introduction of spaces in column B per your latest sample) I think that can be trimmed down some more.


Extract Item (3)

	A	B	C	D
ASD, SEMH	ASD
SEMH, MLD	MLD
SLCN,SEMH,ASD	SEMH

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:143px;"><col style="width:59px;"><col style="width:75px;"><col style="width:118px;"></colgroup><tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]

[TD="align: center"]Item of Interest[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]

[TD="align: right"]1, 2[/TD]

[TD="align: center"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]

[TD="align: right"]2,1[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]

[TD="align: right"]2, 1, 3[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula C2 =TRIM(MID(SUBSTITUTE(A2&",",",",REPT(" ",100)),50*(FIND(D$2,SUBSTITUTE(B2&D$2," ",""))+1)-99,100)) <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

Sorry for the late reply and thank you to everyone for your suggestions. Pete, I went with your formula in the end, as it was the most straight-forward to implement - and I'm a UDF-phobe! It worked like a charm, thanks very much

Martin

 

[hiker95]

Sorry for the late reply and thank you to everyone for your suggestions.

Mading,

You are very welcome. Glad we could help.

And, come back anytime.