Filtered 2D array

[vampsthevampyre]

After yesterdays success of running totals in 2D arrays (Thanks Fluff), I have now hit another problem filtering the results.

I have generated a 2D array similar to the one shown below (this is a cut down version) cells O5-X14 and I'm trying to generate the two outputs AA5-AA14 and AC5-AE14. I wish to be able to use the number of filtered numbers in following equations i.e for results AC9,AD9 and AE9 there would be three different follow up equations up to a possible 10 in this example (max number of columns) so any solutions on how to also reference the filtered results would also be appreciated.

Regards

Ian

Book1
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM
2
3																										Output		Filtered
4		Column1	Col1	Col2	Col3	Col4	Col5	Col6	Col7	Col8	Col9	Col10		Dynamic Array												No.s		1	2	3	4	5	6	7	8	9	10
5		Row1						1						0	0	0	0	0	1	0	0	0	0			1		1
6		Row2			2									0	0	2	0	0	0	0	0	0	0			1		2
7		Row3					6		9					0	0	0	0	6	0	9	0	0	0			2		6	9
8		Row4			12									0	0	12	0	0	0	0	0	0	0			1		12
9		Row5	1	3		4								1	3	0	4	0	0	0	0	0	0			3		1	3	4
10		Row6						7						0	0	0	0	0	7	0	0	0	0			1		7
11		Row7				2				3				0	0	0	2	0	0	0	3	0	0			2		2	3
12		Row8												0	0	0	0	0	0	0	0	0	0			0
13		Row9		1			5			7				0	1	0	0	5	0	0	7	0	0			3		1	5	7
14		Row10				8			9					0	0	0	8	0	0	9	0	0	0			2		8	9
15
Sheet6

Cell Formulas
Range	Formula
O5:X14	O5	=Table13[[Col1]:[Col10]]
Dynamic array formulas.

 

[saboh12617]

Hello,

In AA4 (count of outputs>0) you can enter :

=BYROW(O4#,LAMBDA(r,SUMPRODUCT(--(r>0))))

And in AC4 :

=LET(
matIni;O4#;
matRes,DROP(REDUCE(0,SEQUENCE(ROWS(matIni)),LAMBDA(acc,i,VSTACK(acc,
TOROW(FILTER(CHOOSEROWS(matIni,i),CHOOSEROWS(matIni,i)>0,""))))),1),
IFERROR(matRes,""))

However, in the "filtered results", you can not output a matrice of different row lengths, they have to be all the same so Excel fills the empty cells with #NA (which i hid with iferror, to match your example).

If you do want to have a row exactly the length of the quantity of values, you need to extend a different formula (a filter) manually.

 

[HighAndWilder]

After yesterdays success of running totals in 2D arrays (Thanks Fluff), I have now hit another problem filtering the results.

I have generated a 2D array similar to the one shown below (this is a cut down version) cells O5-X14 and I'm trying to generate the two outputs AA5-AA14 and AC5-AE14. I wish to be able to use the number of filtered numbers in following equations i.e for results AC9,AD9 and AE9 there would be three different follow up equations up to a possible 10 in this example (max number of columns) so any solutions on how to also reference the filtered results would also be appreciated.

Regards

Ian

Book1
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM
2
3																										Output		Filtered
4		Column1	Col1	Col2	Col3	Col4	Col5	Col6	Col7	Col8	Col9	Col10		Dynamic Array												No.s		1	2	3	4	5	6	7	8	9	10
5		Row1						1						0	0	0	0	0	1	0	0	0	0			1		1
6		Row2			2									0	0	2	0	0	0	0	0	0	0			1		2
7		Row3					6		9					0	0	0	0	6	0	9	0	0	0			2		6	9
8		Row4			12									0	0	12	0	0	0	0	0	0	0			1		12
9		Row5	1	3		4								1	3	0	4	0	0	0	0	0	0			3		1	3	4
10		Row6						7						0	0	0	0	0	7	0	0	0	0			1		7
11		Row7				2				3				0	0	0	2	0	0	0	3	0	0			2		2	3
12		Row8												0	0	0	0	0	0	0	0	0	0			0
13		Row9		1			5			7				0	1	0	0	5	0	0	7	0	0			3		1	5	7
14		Row10				8			9					0	0	0	8	0	0	9	0	0	0			2		8	9
15
Sheet6

Cell Formulas
Range	Formula
O5:X14	O5	=Table13[[Col1]:[Col10]]
Dynamic array formulas.

Does this work for you?

Dates and Times.xlsm
	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM
1
2														Output
3														No,s		Filtered
4																1	2	3	4	5	6	7	8	9	10
5		0	0	0	0	0	1	0	0	0	0			1		1
6		0	0	2	0	0	0	0	0	0	0			1		2
7		0	0	0	0	6	0	9	0	0	0			2		6	9
8		0	0	12	0	0	0	0	0	0	0			1		12
9		1	3	0	4	0	0	0	0	0	0			3		1	3	4
10		0	0	0	0	0	7	0	0	0	0			1		7
11		0	0	0	2	0	0	0	3	0	0			2		2	3
12		0	0	0	0	0	0	0	0	0	0			0
13		0	1	0	0	5	0	0	7	0	0			3		1	5	7
14		0	0	0	8	0	0	9	0	0	0			2		8	9
15
Sheet8

Cell Formulas
Range	Formula
AA5:AA14	AA5	=IF(SUM(O5:X5)>0,COUNTIF(O5:X5,">0"),0)
AC5:AC6,AC8,AC10,AC12,AC14:AD14,AC13:AE13,AC11:AD11,AC9:AE9,AC7:AD7	AC5	=IF(AA5>0,TEXTSPLIT(TEXTJOIN(",",TRUE,SUBSTITUTE(O5:X5,0,"")),","),"")

 

[kvsrinivasamurthy]

Try. In AB5

=LET(a,C5:L14,MAKEARRAY(ROWS(a),COLUMNS(a),LAMBDA(r,c,IFERROR(INDEX(a,r,SMALL(IF(INDEX(a,r,)>0,COLUMN(INDEX(a,r,))-COLUMN(B4),""),c)),""))))

 

[saboh12617]

Hello @HighAndWilder, i find it very clever to use the text functions as you did (way smaller formula than mine), however i think you should return the result with a VALUE(), to convert back the strings to usable numbers for future the future equations mentioned by the OP.

 

[HighAndWilder]

Hello @HighAndWilder, i find it very clever to use the text functions as you did (way smaller formula than mine), however i think you should return the result with a VALUE(), to convert back the strings to usable numbers for future the future equations mentioned by the OP.

Good idea. I've now used a custom format of 0;-0;;@ so that the zero's don't show in the results as in the original post.

I use what I know at the time and then reverse engineer the suggestions of others to learn more. There are always many ways to skin a cat but I would never do that.

Dates and Times.xlsm
	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM
1
2														Output
3														No,s		Filtered
4																1	2	3	4	5	6	7	8	9	10
5		0	0	0	0	0	1	0	0	0	0			1		1
6		0	0	2	0	0	0	0	0	0	0			1		2
7		0	0	0	0	6	0	9	0	0	0			2		6	9
8		0	0	12	0	0	0	0	0	0	0			1		12
9		1	3	0	4	0	0	0	0	0	0			3		1	3	4
10		0	0	0	0	0	7	0	0	0	0			1		7
11		0	0	0	2	0	0	0	3	0	0			2		2	3
12		0	0	0	0	0	0	0	0	0	0			0
13		0	1	0	0	5	0	0	7	0	0			3		1	5	7
14		0	0	0	8	0	0	9	0	0	0			2		8	9
15
16
Sheet8

Cell Formulas
Range	Formula
AA5:AA14	AA5	=IF(SUM(O5:X5)>0,COUNTIF(O5:X5,">0"),0)
AC5:AC6,AC8,AC10,AC12,AC14:AD14,AC13:AE13,AC11:AD11,AC9:AE9,AC7:AD7	AC5	=IF(AA5>0,VALUE(TEXTSPLIT(TEXTJOIN(",",TRUE,SUBSTITUTE(O5:X5,0,"")),",")),0)

 

[djclements]

Another possibility is to sort the data by row number using some array reshaping functions:

=LET(
    a, DROP(Table13,,1),
    b, a<>"",
    r, SEQUENCE(ROWS(a)),
    WRAPROWS(SORTBY(TOCOL(IF(b,a,"")),TOCOL(IF({1},r,a)),1,TOCOL(IF(b,r)),1),COLUMNS(a))
)

You could also omit the empty columns, if desired:

=LET(
    a, DROP(Table13,,1),
    b, a<>"",
    r, SEQUENCE(ROWS(a)),
    TAKE(WRAPROWS(SORTBY(TOCOL(IF(b,a,"")),TOCOL(IF({1},r,a)),1,TOCOL(IF(b,r)),1),COLUMNS(a)),,MAX(BYROW(a,COUNT)))
)

And if you wanted to reference the dynamic spill range (O5#) instead of Table13, the following modifications should work:

=LET(
    a, O5#,
    b, a<>0,
    r, SEQUENCE(ROWS(a)),
    TAKE(WRAPROWS(SORTBY(TOCOL(IF(b,a,"")),TOCOL(IF({1},r,a)),1,TOCOL(IF(b,r)),1),COLUMNS(a)),,MAX(BYROW(a,LAMBDA(v,COUNTIF(v,"<>0")))))
)

 

[djclements]

REVISED: The SORTBY portion of each of the three formulas shown in my previous reply (post #7) can be simplified with a single by_array argument:

• SORTBY(TOCOL(IF(b,a,"")),TOCOL(IF(b,r,r+0.1)))

For example, the first formula would be:

=LET(
    a, DROP(Table13,,1),
    b, a<>"",
    r, SEQUENCE(ROWS(a)),
    WRAPROWS(SORTBY(TOCOL(IF(b,a,"")),TOCOL(IF(b,r,r+0.1))),COLUMNS(a))
)

 

[Cubist]

You can simply use FILTER.

Book2
	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL
2	Output
3	No,s		Filtered
4			1	2	3	4	5	6	7	8	9	10
5	1		1
6	1		2
7	2		6	9
8	1		12
9	3		1	3	4
10	1		7
11	2		2	3
12	0
13	3		1	5	7
14	2		8	9
Sheet1

Cell Formulas
Range	Formula
AA5:AA14	AA5	=COUNTIFS(O5:X5,">0")
AC5:AC6,AC8,AC10,AC12,AC14:AD14,AC13:AE13,AC11:AD11,AC9:AE9,AC7:AD7	AC5	=FILTER(O5:X5,O5:X5>0,"")