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

 

[steve the fish]

This produces same answers as your example.

=ISNUMBER(FIND(MID(C$1,FIND(" ",C$1)+1,99)&",",$B2&","))

 

[stefanaalten]

Thanks for that. I need to modify the formula slightly to work with my real data, but struggling with that ?.
Firstly, there will be 50 "item" columns. Secondly, the headings will actually be "Pad 1 Score", "Pad 2 Piccolo", "Pad 3 Flute 1", "Pad 4 Flute 2", ... "Pad 37 Baritone/Euphonium", ... "Pad 50 Various Others", so how should I modify the formula to allow for that? As it stands the formula works with up to 9 columns and does not permit the suffix text.
Sorry to be such a numpty. I've tried to work it out for myself.

 

[gtwo]

First, if you can change "TRUE" for like "YES", and "FALSE" for like "NO", it will save you a lot of headache, because the words TRUE and FALSE in excel are not very well handled by it, so avoid those.

So, let's say you change that. Now you can add two rows in your header, before your data.
Row one, is just to number each PAD(1 , 2, 3, 4 and so on)
Row two, you put the description just as "Score", "Piccolo", "Flute", "Flute" and so on, just like you mentioned.
Row three, you concatenate the information with a simple formula, then copy this formula to the other columns.

=CONCATENATE("Pad ";C1;" ";C2)

(just as shown in the image below).

Now for the columns B, that will put everything together, the formula is:

=CONCATENATE(
IF(C4="YES";$C$1&",";"");
IF(D4="YES";$D$1&",";"");
IF(E4="YES";$E$1&",";"");
IF(F4="YES";$F$1&",";"");
IF(G4="YES";$G$1&",";"");
IF(H4="YES";$H$1&",";"");
IF(I4="YES";$I$1&",";""))

Put this formula in cell B4

As you can see, is not beautiful, but it works, and to add new columns, just one of the rows in the formula above, and change the columns letter.

I hope you get it .

 

[stefanaalten]

Thanks gtwo but that's not what I'm trying to accomplish. I'm trying to do the reverse: using the lists in column B I want to populate the columns C, D, E with true/false (or yes/no - it doesn't really matter) based on whether the corresponding number appears in the list.
In the meantime, I have found an article, How to split text string in Excel by comma, space, character or mask, that provides some help but it doesn't address my specific scenario as I will only have values in a few of the columns (the article gives help on how to split a string where all columns have a value).

 

[AlanY]

something like this?

Book1
	A	B	C	D	E
1		Pad 1 Score	Pad 2 Piccolo	Pad 3 Flute 1	Pad 3 Flute 2
2
3	Pad 1 Score, Pad 2 Piccolo, Pad 3 Flute 1,	True	True	True	False
4	Pad 3 Flute 1,	False	False	True	False
5	Pad 2 Piccolo, Pad 3 Flute 1,	False	True	True	False
Sheet4

Cell Formulas
Range	Formula
B3:E5	B3	=IFERROR(IF(FIND(B$1,$A3),TRUE),FALSE)

 

[Tom Baron]

As long as your table wont change format and your items will run in numerical order from 1 to x left to right then you can cheat a little with:
=NOT(ISERROR(SEARCH(COLUMN(A1),$B9)))

Drop this into column 3 (where item 1 should be)

This works as column(a1) returns 1 and when you drag the formula right it'll return column b (2) column c (3) etc, unlike above and all the other formulas, it doesn't attempt to grab the number from the header and instead relies on where the column is in the worksheet. Iserror just returns true or false depending on whether the number can be found in the list and NOT reverses a True to False (as if the search function returns an error then iserror will be true and you want it false)

Cheers,

Tom

 

[Tom Baron]

 

[stefanaalten]

Tom, I like your solution but it doesn't seem to work correctly - e.g. if I enter "10" in column B, then it results in TRUE for Item 10 but also for Item 1, and ditto if I enter "12" in column B then I get TRUE for Item 12 but also for Item 1 and Item 2 - ???

 

[FormR]

Hi, here is another option along similar lines of post#8

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1	ID	List	Item 1	Item 2	Item 3	Item 4	Item 5	Item 6	Item 7	Item 8	Item 9	Item 10
2	A	1,2	True	True	False	False	False	False	False	False	False	False
3	B	2	False	False	False	False	False	False	False	False	False	False
4	C		False	False	False	False	False	False	False	False	False	False
5	D	1,2,3	True	True	True	False	False	False	False	False	False	False
6	E	10	False	False	False	False	False	False	False	False	False	True
7	F	10,1	True	False	False	False	False	False	False	False	False	True
Sheet2

Cell Formulas
Range	Formula
C2:L7	C2	=ISNUMBER(FIND(","&COLUMNS($C2:C2)&",",","&$B2&","))