How to adjust a 'defined-name' array based on whether there are comma-separated numbers in cell(s) of the array?

[Rnkhch]

Hello,

I have defined names corresponding to certain ranges like the one below where Info!$E$1 specifies the bottom of the array (through a SUMPRODUCT formula that detects the last row of the column that has data):

AINFOcJ=INDIRECT("Info!$J$3:$J$"&Info!$E$1)

Up until now, this column J included only numbers 1 or 2 which are selectable from dropdown menus in each cell of column J. However, I want to start adding "1,2" as another option in these dropdown menus, so now the menus will show three options: "1", "2", and "1,2".

Th problem now is that "1,2" is not a number, so it will completely mess up all calculations that utilize the AINFOcJ array. So now I need a way of adjusting AINFOcJ definition only if commas are found anywhere in the range of J3:Jn such that all "1,2" instances are dissected into 1 and 2 and shown right after each other at the correct order within the rest of the array. So basically this will result in an expanded array which I have shown in the attached XL2BB. the top arrays are original ranges and bottom arrays are the result of the defined names. The left arrays are the normal scenario where there are no instances of "1,2". The right array has two instances of "1,2" which I have shown dissected at the bottom.

So, how do I need to modify the definition of my array to take care of this situation?

Book2
	A	B	C	D	E
1
2		1		1
3		2		2
4		2		2
5		1		1
6		2		1,2
7		2		2
8		2		2
9		1		1,2
10		1		1
11		2		2
12
13
14		1		1
15		2		2
16		2		2
17		1		1
18		2		1
19		2		2
20		2		2
21		1		2
22		1		1
23		2		2
24				1
25				2
26
Sheet1

Thanks for any input!

 

[StephenCrump]

What about: =FILTERXML("<x><y>"&SUBSTITUTE(TEXTJOIN(",",,Info!$J$3:INDEX(Info!$J:$J,Info!$E$1)),",","</y><y>")&"</y></x>","//y")

And if you have the new TEXTSPLIT function (I'm still waiting) you could use that instead of the FILTERXML construction.

 

[Rnkhch]

OMG, it works so well!!! Thank you so much!

Today I got solutions for two complex questions, so feeling super happy

I'm also still waiting for those 14 or so new functions, but no idea when they're coming.

 

[StephenCrump]

Today I got solutions for two complex questions, so feeling super happy

Excellent. I'm glad you're getting your money's worth from the Forum.

 

[Rnkhch]

Thanks! Also I realized I'm gonna have to apply this filtering concept to a more complex situation, i.e. the columns flanking J, so that when J contains any "1,2", they would need to expand accordingly by duplicating every cell which is across from a "1,2"-containing cell in J But let me first think about it a little more, and if I get stuck (which I'll probably do), I'll post here with some more details and an XL2BB.

Cheers