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

Mading

New Member
Joined
Feb 10, 2016
Messages
7
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:


Column AColumn B
ASD, SEMH1,2
SEMH, MLD2,1
SLCN,SEMH,ASD2,1,3

<tbody>
</tbody>


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:


Column AColumn BColumn C
ASD, SEMH1,2ASD
SEMH, MLD2,1MLD
SLCN,SEMH,ASD2,1,3SEMH

<tbody>
</tbody>


Is such a thing even possible?

Thanks in advance

Excel 2016
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
Question
The serial numbers (column B) always have just one digit, i.e., 1 to 9?

M.
 
Upvote 0
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
ABC
1ASD, SEMH1,2
2SEMH, MLD2,1
3SLCN,SEMH,ASD2,1,3
4
Sheet1


And, after the macro in the active worksheet:


Excel 2007
ABC
1ASD, SEMH1,2ASD
2SEMH, MLD2,1MLD
3SLCN,SEMH,ASD2,1,3SEMH
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.

Code:
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.
 
Upvote 0
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
ABCDE
1Item of Interest
2ASD, SEMH1,2ASD1
3SEMH, MLD2,1MLD
4SLCN,SEMH,ASD2,1,3SEMH
Extract Item
 
Upvote 0
Or maybe this UDF (User Defined Function)

Code:
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)


A
B
C
D
1
Needs​
Ranks​
Result​
Search​
2
ASD,SEMH​
1, 2​
ASD​
1​
3
SEMH, MLD​
2,1​
MLD​
4
SLCN,SEMH,ASD​
2, 1, 3​
SEMH​

<tbody>
</tbody>


Hope this helps

M.
 
Last edited:
Upvote 0
Or possibly this variation of Marcelo's UDF.

Rich (BB code):
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
ABCD
1Item of Interest
2ASD, SEMH1,2SEMH2
3SEMH, MLD2,1SEMH
4SLCN,SEMH,ASD2,1,3SLCN
Extract Item (2)
 
Upvote 0
Or..just for fun :)
Another solution using formula - works only if the ranks have just one digit 1 to 9


A
B
C
D
1
Needs​
Ranks​
Column C​
Search​
2
ASD,SEMH​
1, 2​
ASD​
1​
3
SEMH, MLD​
2,1​
MLD​
4
SLCN,SEMH,ASD​
2, 1, 3​
SEMH​

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

M.
 
Upvote 0
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
ABCD
1Item of Interest
2ASD, SEMH1, 2ASD1
3SEMH, MLD2,1MLD
4SLCN,SEMH,ASD2, 1, 3SEMH
Extract Item (3)
 
Last edited:
Upvote 0
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)

ABCD
1 Item of Interest
2ASD, SEMH1, 2ASD1
3SEMH, MLD2,1MLD
4SLCN,SEMH,ASD2, 1, 3SEMH

<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>
</tbody>

Spreadsheet Formulas
CellFormula
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
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,210
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top