Parsing a list of numbers

[stefanaalten]

Hi,

I'm struggling to come up with a way of producing the output I'm after. My data is as shown below:

ID	List	Item 1	Item 2	Item 3
A	1,2	TRUE​	TRUE​	FALSE​
B	2​	FALSE​	TRUE​	FALSE​
C		FALSE​	FALSE​	FALSE​
D	1,2,3	TRUE​	TRUE​	TRUE​

For each entry in my list (column A, ID's A-D) I have a list of numbers (column B). I would like to populate the columns Item 1, etc. with true/false values based on whether the item occurs in the list. It should be simple parsing function shouldn't it, but I'm not really sure how to go about it - can anyone please help? My actual list has 50 items, i.e. columns for Item 1 - Item 50. The lists (in column B) will not consist of more than 5 items, and I can ensure they are in ascending numeric order if that helps the formula.
I'd be really grateful for a bit of help! At the moment I'm populating the values in the Item columns manually, which is error-prone!
Kind regards,
Stefan

 

[stefanaalten]

AlanY, many thanks for your solution, but it's a little impractical to enter the full titles in the list - in some cases I would end up with very long descriptions (some of the pad names list multiple instruments), so looking for a solution that allows the list to simply consist of the numbers (comma-separated or somehow delimited).

 

[stefanaalten]

Perfect - thanks FormR!

 

[AlanY]

AlanY, many thanks for your solution, but it's a little impractical to enter the full titles in the list - in some cases I would end up with very long descriptions (some of the pad names list multiple instruments), so looking for a solution that allows the list to simply consist of the numbers (comma-separated or somehow delimited).

glad that you got it sorted

 

[stefanaalten]

My scenario has changed and now the solution (FormR's post) no longer works. I've tried to work it out and my brain is fried ? and would be very grateful if anyone (with a better brain than mine!) could help?

The range shown below is AH4:CA10 in my spreadsheet (top-left blank cell is AH4). The top row (i.e. row 4 in my spreadsheet) has numbers formatted with leading zeroes. The "Rehearsal Pads" column has the lists entered as text ('000, '011,012, '013, etc.) The lists in this column will consist of max. 5 items (but usually will just be 1-3 items).

What formula could produce the TRUE/FALSE values shown (in cells AI6:CA10)?

000

010

011

012

013

020

021

022

023

etc.

093

100

Rehearsal Pads

Pad 000 Score

Pad 010 Piccolo

Pad 011 Flute 1

Pad 012 Flute 1

Pad 013 Flute 2

Pad 020 Oboe 1

Pad 021 Oboe 1

Pad 022 Oboe 2

Pad 023 Bassoon

etc.

Pad 093 Timpani

Pad 100 Various Others

000

TRUE​

FALSE​

FALSE​

FALSE​

FALSE​

FALSE​

FALSE​

FALSE​

FALSE​

etc.

FALSE​

FALSE​

011,012

FALSE​

FALSE​

TRUE​

TRUE​

FALSE​

FALSE​

FALSE​

FALSE​

FALSE​

etc.

FALSE​

FALSE​

013

FALSE​

FALSE​

FALSE​

FALSE​

TRUE​

FALSE​

FALSE​

FALSE​

FALSE​

etc.

FALSE​

FALSE​

020,021

FALSE​

FALSE​

FALSE​

FALSE​

FALSE​

TRUE​

TRUE​

FALSE​

FALSE​

etc.

FALSE​

FALSE​

022

FALSE​

FALSE​

FALSE​

FALSE​

FALSE​

FALSE​

FALSE​

TRUE​

FALSE​

etc.

FALSE​

FALSE​

023

FALSE​

FALSE​

FALSE​

FALSE​

FALSE​

FALSE​

FALSE​

FALSE​

TRUE​

etc.

FALSE​

FALSE​

 

[FormR]

Hi, you can try like this.

EDIT: XL2BB has converted the text in column AH and row 4 to numbers, they are / need to be, as you have them in post 14.

Book1
	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT
4		0	10	11	12	13	20	21	22	23		93	100
5	Rehearsal Pads	Pad 000 Score	Pad 010 Piccolo	Pad 011 Flute 1	Pad 012 Flute 1	Pad 013 Flute 2	Pad 020 Oboe 1	Pad 021 Oboe 1	Pad 022 Oboe 2	Pad 023 Bassoon	etc.	Pad 093 Timpani	Pad 100 Various Others
6	0	True	False	False	False	False	False	False	False	False		False	False
7	11012	False	False	True	True	False	False	False	False	False		False	False
8	13	False	False	False	False	True	False	False	False	False		False	False
9	20021	False	False	False	False	False	True	True	False	False		False	False
10	22	False	False	False	False	False	False	False	True	False		False	False
11	23	False	False	False	False	False	False	False	False	True		False	False
Sheet1

Cell Formulas
Range	Formula
AI6:AQ11, AS6:AT11	AI6	=ISNUMBER(FIND(","&AI$4&",",","&$AH6&","))

 

[stefanaalten]

Fantastic - many thanks! ??? (replacing the spurious zeroes in column AH with commas - assume that was just a typo)