Creating a lookup grid from delimited text

S

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.
[TABLE="width: 479"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 455"]
<tbody>[TR]
[TD]Stock No[/TD]
[TD]Options[/TD]
[/TR]
[TR]
[TD]1234[/TD]
[TD]Option A // Option C // Option F // Option H[/TD]
[/TR]
[TR]
[TD]1235[/TD]
[TD]Option B // Option C // Option G // Option H[/TD]
[/TR]
[TR]
[TD]1236[/TD]
[TD]Option C // Option D // Option E // Option K[/TD]
[/TR]
</tbody>[/TABLE]
<strike></strike>[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]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.[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 272"]
<tbody>[TR]
[TD="width: 68, bgcolor: transparent"][TABLE="width: 272"]
<tbody>[TR]
[TD="width: 68, bgcolor: transparent"][/TD]
[TD="width: 68, bgcolor: transparent"]1234[/TD]
[TD="width: 68, bgcolor: transparent"]1235[/TD]
[TD="width: 68, bgcolor: transparent"]1236[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]OptionA[/TD]
[TD="bgcolor: transparent"] 1[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]OptionB[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"] 1[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]OptionC[/TD]
[TD="bgcolor: transparent"] 1[/TD]
[TD="bgcolor: transparent"] 1 [/TD]
[TD="bgcolor: transparent"] 1[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]OptionD[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"] 1[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]OptionE[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"] 1[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]OptionF[/TD]
[TD="bgcolor: transparent"] 1[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]OptionG[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"] 1[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]OptionH[/TD]
[TD="bgcolor: transparent"] 1[/TD]
[TD="bgcolor: transparent"] 1[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]OptionI[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]OptionJ[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]OptionK[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"] 1 [/TD]
[/TR]
</tbody>[/TABLE]
<strike></strike>[/TD]
[TD="width: 68, bgcolor: transparent"]<strike></strike>[/TD]
[TD="width: 68, bgcolor: transparent"][/TD]
[TD="width: 68, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]
 
Last edited:

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
Sort by date Sort by votes
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
You must log in or register to reply here.

Similar threads

T
  • Question Question
Macro to turn a grid of data into a list of data
Replies
4
Views
676
hiker95
H
L
  • Question Question
Listing top 20 values with multiple data sets
Replies
1
Views
653
liveinhope
L
T
  • Question Question
Macro to copy from one sheet to another with options on which column to copy and which column to paste to and more
Replies
3
Views
517
AFPathfinder
A
M
  • Question Question
Create Workbooks from a data list using filters to select names
Replies
8
Views
505
Joe4
Joe4
M
  • Question Question
Find A Value Copy to Cell and replace Text
Replies
8
Views
717
Mpotten
M

Forum statistics

Threads
1,224,823
Messages
6,181,170
Members
453,021
Latest member
Justyna P

Share this page

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