Filter value applying to numbers separated by comma

[Niall19]

Hi all,

Apologies for the simple question regarding filter values. I would like, if trying to isolate batch 1, for example, for the results to include the entry which has both "1,9" as this entry appears in both batch 1 & 9. Currently, this is viewed as a separate category so would not appear under batch 1 filtering. This would streamline our process.

Help would be greatly appreciated,

Niall

 

[JamesCanale]

Assuming you don't want to find 11 or 51 - I have something here with a helper column. I can't figure out how to do it without the helper column. But I suspect someone can figure that if you need it.

MrExcelPlayground21.xlsx
	A	B
1	Thing	Solution
2	1	TRUE
3	3	FALSE
4	5,1	TRUE
5	1,9	TRUE
6	11	FALSE
7	11,12	FALSE
8	1,12	TRUE
9	3	FALSE
10	4,5	FALSE
Sheet1

Cell Formulas
Range	Formula
B2:B10	B2	=OR(VALUE(TEXTSPLIT(A2,","))=1)

 

[Niall19]

Assuming you don't want to find 11 or 51 - I have something here with a helper column. I can't figure out how to do it without the helper column. But I suspect someone can figure that if you need it.

MrExcelPlayground21.xlsx
	A	B
1	Thing	Solution
2	1	TRUE
3	3	FALSE
4	5,1	TRUE
5	1,9	TRUE
6	11	FALSE
7	11,12	FALSE
8	1,12	TRUE
9	3	FALSE
10	4,5	FALSE
Sheet1

Cell Formulas
Range	Formula
B2:B10	B2	=OR(VALUE(TEXTSPLIT(A2,","))=1)

Hi,

Thank you for the suggestion. This spreadsheet already has a sea of columns so my instinct is to avoid any extra columns being added if at all possible.

Is a formula possible, for example, if an entry is "1, 9", to include all entries with the value "1" somewhere in the cell, even if with a comma and another value, to include in the filter results for just "1". Word salad but hope that makes sense.

My excel knowledge is very poor so at least if I know this is not possible without an extra column, it's something.

Niall

 

[JamesCanale]

I don't know of a way to do that if you want to exclude say "11" from being counted as a "1". If the data set is only 1 to 9 and you don't worry about a false positive on 11, then something like this will work:

MrExcelPlayground21.xlsx
	A
1	Thing
2	1
3	3
4	5,1
5	1,9
6	11
7	11,12
8	1,12
9	3
10	4,5
11
12
13
14	1
15	5,1
16	1,9
17	11
18	11,12
19	1,12
Sheet1

Cell Formulas
Range	Formula
A14:A19	A14	=FILTER(A2:A10,NOT(ISERROR(SEARCH(1,A2:A10))))
Dynamic array formulas.

 

[JamesCanale]

This will exclude things like 11 and 512 from finding 1's. Assuming there are no spaces around the commas.

MrExcelPlayground21.xlsx
	A	B
2	1	1
3	3	5,1
4	5,1	1,9
5	1,9	1,12
6	11
7	11,12
8	1,12
9	3
10	4,5
Sheet1

Cell Formulas
Range	Formula
B2:B5	B2	=FILTER(A2:A10,ISNUMBER(SEARCH(","&1&",",","&A2:A10&",")))
Dynamic array formulas.