Creating a lookup grid from delimited text

SteveBC

New Member
Joined
Mar 8, 2013
Messages
21
We receive updates from our supplier in the format below showing products we have ordered by stock number and the options we have chosen against each product in a // delimited format. So the data looks something like this except there are around 50 available options and typically around a dozen are attached to each product.
Stock NoOptions
1234Option A // Option C // Option F // Option H
1235Option B // Option C // Option G // Option H
1236Option C // Option D // Option E // Option K

<tbody>
</tbody>
<strike></strike>
I'd like to create a grid per below showing at a glance which options have been selected for each S/No by looking at the above data. Any suggestions appreciated please.

<tbody>
</tbody>
123412351236
OptionA 1
OptionB 1
OptionC 1 1 1
OptionD 1
OptionE 1
OptionF 1
OptionG 1
OptionH 1 1
OptionI
OptionJ
OptionK 1

<tbody>
</tbody>
<strike></strike>
<strike></strike>

<tbody>
</tbody>
 
Last edited:

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
How about

Excel 2013/2016
ABCDEFGH
1Stock NoOptions123412351236
21234Option A // Option C // Option F // Option HOption A1  
31235Option B // Option C // Option G // Option HOption B1
41236Option C // Option D // Option E // Option KOption C111
5Option D1
6Option E1
7Option F1
8Option G1
9Option H11
10Option I
11Option J
12Option K1
Today
Cell Formulas
RangeFormula
F2=IF(ISNUMBER(FIND($E2,INDEX($B$2:$B$4,MATCH(F$1,$A$2:$A$4,0)))),1,"")
G2=IF(ISNUMBER(FIND($E2,INDEX($B$2:$B$4,MATCH(G$1,$A$2:$A$4,0)))),1,"")
H2=IF(ISNUMBER(FIND($E2,INDEX($B$2:$B$4,MATCH(H$1,$A$2:$A$4,0)))),1,"")
 
Upvote 0
Glad to help & thanks for the feedback
 
Upvote 0

Forum statistics

Threads
1,213,567
Messages
6,114,342
Members
448,570
Latest member
rik81h

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