Unique values for an array of n rows and m columns

[XcelLearner]

Hi,

I have a table of n rows and m columns. For simplicity, I use a table of 5 rows and 2 columns from B1:C5. I try to find unique values, filtering for blank value (as in C3). I look for a formula that extracts values from both columns and rows - I googled for the usage of UNIQUE function but it seems that solutions are based on either rows or columns, not both. I would also like to count the times of appearance for each unique value.

The expected results would be in B9:B16 for unique values, and C9:C16 for counts.

Can anyone please help with the formula? I am using Office 365. Thanks a lot.

 

[Eric W]

Greetings
this works for Office 2019
What about this, Feedback Please
I have this unique but sorted from Lowest to highest

I think your formula is quite ingenious. I've seen some very clever uses of the FILTERXML function, and this is one. The problem with this is maintainability. It's quite complicated, and few people could modify it or even change the range in it easily. pgc01's formula in post 14 is shorter and simpler for Excel 2019 (although it doesn't sort), and the LET formula in post 15 is also shorter and simpler for versions with the new functions.

If we all agree, I will choose the solution submitted by Peter, revised from Eric's as the best solution. It is like a team work.

This was definitely a team effort! The new functions are still relatively new, and people are experimenting with them in different circumstances. I think we can agree that if you want a SPILL formula, the one in post 15 is the best. But we don't all need to agree! Ultimately, this was your question, and you should pick the solution that works best for you. I see Sandy has now added a PQ option.

Thanks for an interesting question that sparked a lively discussion.

 

[Peter_SSs]

But we don't all need to agree!

I agree.

 

[Dossfm0q]

alternative option, but I need Help of len of result now ok with 1 to 9 if len 10 to be 2 100 to be 3
(highlighted with Red Color)

1. =NUMBERVALUE(MID(TEXTJOIN("",FALSE,COUNTIF($B$2:$C$6,"<"&TRANSPOSE($B$2:$C$6))),ROW(INDIRECT("1:"&(COUNTA($B$2:$C$6)+COUNTBLANK($B$2:$C$6)))),1))
2. +NUMBERVALUE(MID(TEXTJOIN("",FALSE,TRANSPOSE(--($B$2:$C$6<>""))),ROW(INDIRECT("1:"&(COUNTA($B$2:$C$6)+COUNTBLANK($B$2:$C$6)))),1))

Unique values for an array of n rows and m columns.xlsx
	A	B	C
1	DATE	ITEMS
2	9/22/2020	VGI	CTR
3	9/24/2020	EVF	C32
4	9/25/2020	DGC
5	9/29/2020	VTP	BMI
6	9/30/2020	VGI	DIG
7
8
9
10		ITEMS	Qnt
11		BMI	1
12		C32	1
13		CTR	1
14		DGC	1
15		DIG	1
16		EVF	1
17		VGI	2
18		VTP	1
Sheet2

Cell Formulas
Range	Formula
B11:B18	B11	=IFERROR(INDEX($B$2:$C$6,MOD(MATCH(1,(NUMBERVALUE(MID(TEXTJOIN("",FALSE,COUNTIF($B$2:$C$6,"<"&TRANSPOSE($B$2:$C$6))),ROW(INDIRECT("1:"&(COUNTA($B$2:$C$6)+COUNTBLANK($B$2:$C$6)))),1))+NUMBERVALUE(MID(TEXTJOIN("",FALSE,TRANSPOSE(--($B$2:$C$6<>""))),ROW(INDIRECT("1:"&(COUNTA($B$2:$C$6)+COUNTBLANK($B$2:$C$6)))),1)))-SUM(COUNTIF($B$2:$C$6,B$10:B10)),0)-1,ROWS($B$2:$C$6))+1,CEILING(MATCH(1,(NUMBERVALUE(MID(TEXTJOIN("",FALSE,COUNTIF($B$2:$C$6,"<"&TRANSPOSE($B$2:$C$6))),ROW(INDIRECT("1:"&(COUNTA($B$2:$C$6)+COUNTBLANK($B$2:$C$6)))),1))+NUMBERVALUE(MID(TEXTJOIN("",FALSE,TRANSPOSE(--($B$2:$C$6<>""))),ROW(INDIRECT("1:"&(COUNTA($B$2:$C$6)+COUNTBLANK($B$2:$C$6)))),1)))-SUM(COUNTIF($B$2:$C$6,B$10:B10)),0),ROWS($B$2:$C$6))/ROWS($B$2:$C$6)),"")
C11:C18	C11	=IF(B11<>"",COUNTIF($B$2:$C$6,B11),"")
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Peter_SSs]

alternative option,

The question was for a table of m rows and n columns. I have used your latest formula and inserted another column into the original range and the results are shown below so it seems something is not working.

20 11 03.xlsm
	A	B	C	D
1	DATE	ITEMS
2	9/22/2020	VGI	ABC	CTR
3	9/24/2020	EVF		C32
4	9/25/2020	DGC	GGG
5	9/29/2020	VTP		BMI
6	9/30/2020	VGI		DIG
7
8
9
10		ITEMS	Qnt
11		VGI	2
12
13		EVF	1
14
15
16
17
18
Test1

Cell Formulas
Range	Formula
B11:B18	B11	=IFERROR(INDEX($B$2:$D$6,MOD(MATCH(1,(NUMBERVALUE(MID(TEXTJOIN("",FALSE,COUNTIF($B$2:$D$6,"<"&TRANSPOSE($B$2:$D$6))),ROW(INDIRECT("1:"&(COUNTA($B$2:$D$6)+COUNTBLANK($B$2:$D$6)))),1))+NUMBERVALUE(MID(TEXTJOIN("",FALSE,TRANSPOSE(--($B$2:$D$6<>""))),ROW(INDIRECT("1:"&(COUNTA($B$2:$D$6)+COUNTBLANK($B$2:$D$6)))),1)))-SUM(COUNTIF($B$2:$D$6,B$10:B10)),0)-1,ROWS($B$2:$D$6))+1,CEILING(MATCH(1,(NUMBERVALUE(MID(TEXTJOIN("",FALSE,COUNTIF($B$2:$D$6,"<"&TRANSPOSE($B$2:$D$6))),ROW(INDIRECT("1:"&(COUNTA($B$2:$D$6)+COUNTBLANK($B$2:$D$6)))),1))+NUMBERVALUE(MID(TEXTJOIN("",FALSE,TRANSPOSE(--($B$2:$D$6<>""))),ROW(INDIRECT("1:"&(COUNTA($B$2:$D$6)+COUNTBLANK($B$2:$D$6)))),1)))-SUM(COUNTIF($B$2:$D$6,B$10:B10)),0),ROWS($B$2:$D$6))/ROWS($B$2:$D$6)),"")
C11:C18	C11	=IF(B11<>"",COUNTIF($B$2:$D$6,B11),"")

 

[Dossfm0q]

المصنف1.xlsx
	A	B	C	D	E
1	DATE	ITEMS
2	9/22/2020	VGI	CTR	hh
3		KKK	LHL	kk
4		GGG	nnnn	HOL
5	9/24/2020	EVF	C32	RDR
6	9/25/2020	DGC	VGI	FMD
7	9/29/2020	VTP	BMI	SSS
8	9/30/2020	hhh	AAA	AAA
9
10
11
12		ITEMS	Qnt
13		AAA	2
14		BMI	1
15		C32	1
16		CTR	1
17		DGC	1
18		EVF	1
19		FMD	1
20		GGG	1
21		hh	1
22		hhh	1
23		HOL	1
24		kk	1
25		KKK	1
26		LHL	1
27		nnnn	1
28		RDR	1
29		SSS	1
30		VGI	2
31		VTP	1
32
33
Sheet1

Cell Formulas
Range	Formula
C13:C31	C13	=IF(B13<>"",COUNTIF($B$2:$D$8,B13),"")
B13:B32	B13	=IFERROR(INDEX($B$2:$D$8,MOD(MATCH(1,(NUMBERVALUE(TRIM(MID(SUBSTITUTE(TEXTJOIN(" ",FALSE,COUNTIF($B$2:$D$8,"<"&TRANSPOSE($B$2:$D$8)))," ",REPT(" ",99)),(ROW(OFFSET($A$1,,,LEN(TEXTJOIN(" ",FALSE,COUNTIF($B$2:$D$8,"<"&TRANSPOSE($B$2:$D$8))))-LEN(SUBSTITUTE(TEXTJOIN(" ",FALSE,COUNTIF($B$2:$D$8,"<"&TRANSPOSE($B$2:$D$8)))," ",""))+1))-1)*99+((ROW(OFFSET($A$1,,,LEN(TEXTJOIN(" ",FALSE,COUNTIF($B$2:$D$8,"<"&TRANSPOSE($B$2:$D$8))))-LEN(SUBSTITUTE(TEXTJOIN(" ",FALSE,COUNTIF($B$2:$D$8,"<"&TRANSPOSE($B$2:$D$8)))," ",""))+1)))=1),99)))+NUMBERVALUE(TRIM(MID(SUBSTITUTE(TEXTJOIN(" ",FALSE,TRANSPOSE(--($B$2:$D$8<>"")))," ",REPT(" ",99)),(ROW(OFFSET($A$1,,,LEN(TEXTJOIN(" ",FALSE,TRANSPOSE(--($B$2:$D$8<>""))))-LEN(SUBSTITUTE(TEXTJOIN(" ",FALSE,TRANSPOSE(--($B$2:$D$8<>"")))," ",""))+1))-1)*99+((ROW(OFFSET($A$1,,,LEN(TEXTJOIN(" ",FALSE,TRANSPOSE(--($B$2:$D$8<>""))))-LEN(SUBSTITUTE(TEXTJOIN(" ",FALSE,TRANSPOSE(--($B$2:$D$8<>"")))," ",""))+1)))=1),99))))-SUM(COUNTIF($B$2:$D$8,B$12:B12)),0)-1,ROWS($B$2:$D$8))+1,CEILING(MATCH(1,(NUMBERVALUE(TRIM(MID(SUBSTITUTE(TEXTJOIN(" ",FALSE,COUNTIF($B$2:$D$8,"<"&TRANSPOSE($B$2:$D$8)))," ",REPT(" ",99)),(ROW(OFFSET($A$1,,,LEN(TEXTJOIN(" ",FALSE,COUNTIF($B$2:$D$8,"<"&TRANSPOSE($B$2:$D$8))))-LEN(SUBSTITUTE(TEXTJOIN(" ",FALSE,COUNTIF($B$2:$D$8,"<"&TRANSPOSE($B$2:$D$8)))," ",""))+1))-1)*99+((ROW(OFFSET($A$1,,,LEN(TEXTJOIN(" ",FALSE,COUNTIF($B$2:$D$8,"<"&TRANSPOSE($B$2:$D$8))))-LEN(SUBSTITUTE(TEXTJOIN(" ",FALSE,COUNTIF($B$2:$D$8,"<"&TRANSPOSE($B$2:$D$8)))," ",""))+1)))=1),99)))+NUMBERVALUE(TRIM(MID(SUBSTITUTE(TEXTJOIN(" ",FALSE,TRANSPOSE(--($B$2:$D$8<>"")))," ",REPT(" ",99)),(ROW(OFFSET($A$1,,,LEN(TEXTJOIN(" ",FALSE,TRANSPOSE(--($B$2:$D$8<>""))))-LEN(SUBSTITUTE(TEXTJOIN(" ",FALSE,TRANSPOSE(--($B$2:$D$8<>"")))," ",""))+1))-1)*99+((ROW(OFFSET($A$1,,,LEN(TEXTJOIN(" ",FALSE,TRANSPOSE(--($B$2:$D$8<>""))))-LEN(SUBSTITUTE(TEXTJOIN(" ",FALSE,TRANSPOSE(--($B$2:$D$8<>"")))," ",""))+1)))=1),99))))-SUM(COUNTIF($B$2:$D$8,B$12:B12)),0),ROWS($B$2:$D$8))/ROWS($B$2:$D$8)),"")
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Peter_SSs]

Re post #25
I'm afraid that the column B formula is incomprehensible to me but to test it I decided to add a few columns and rows and some extra values. This is the result (with formulas from some other posts to compare)

20 11 03.xlsm
	A	B	C	D	E	F	G	H
1	DATE	ITEMS
2	9/22/2020	VGI				CTR		hh
3		KKK				LHL		kk
4
5				qqq
6				qqq
7		GGG		qqq		nnnn		HOL
8	9/24/2020	EVF		qqq	Y3445	C32		RDR
9			zz	qqq
10			zz	qqq
11			zz
12	9/25/2020	DGC	zz			VGI		FMD
13			zz
14	9/29/2020	VTP				BMI		SSS
15	9/30/2020	hhh				AAA		AAA
16
17
18
19	Post #:	25	14	15
20		AAA	VGI	VGI
21		BMI	CTR	KKK
22		C32	hh	GGG
23		CTR	KKK	EVF
24		DGC	LHL	DGC
25		EVF	kk	VTP
26			qqq	hhh
27			GGG	zz
28			nnnn	qqq
29			HOL	Y3445
30			EVF	CTR
31			Y3445	LHL
32			C32	nnnn
33			RDR	C32
34			zz	BMI
35			DGC	AAA
36			FMD	hh
37			VTP	kk
38			BMI	HOL
39			SSS	RDR
40			hhh	FMD
41			AAA	SSS
42
Test2

Cell Formulas
Range	Formula
D20:D41	D20	=LET(a,B2:H15,r,ROWS(a),seq,SEQUENCE(r*COLUMNS(a),,0),arr,INDEX(a,MOD(seq,r)+1,seq/r+1),UNIQUE(FILTER(arr,arr<>0)))
B20:B39	B20	=IFERROR(INDEX($B$2:$H$15,MOD(MATCH(1,(NUMBERVALUE(TRIM(MID(SUBSTITUTE(TEXTJOIN(" ",FALSE,COUNTIF($B$2:$H$15,"<"&TRANSPOSE($B$2:$H$15)))," ",REPT(" ",99)),(ROW(OFFSET($A$1,,,LEN(TEXTJOIN(" ",FALSE,COUNTIF($B$2:$H$15,"<"&TRANSPOSE($B$2:$H$15))))-LEN(SUBSTITUTE(TEXTJOIN(" ",FALSE,COUNTIF($B$2:$H$15,"<"&TRANSPOSE($B$2:$H$15)))," ",""))+1))-1)*99+((ROW(OFFSET($A$1,,,LEN(TEXTJOIN(" ",FALSE,COUNTIF($B$2:$H$15,"<"&TRANSPOSE($B$2:$H$15))))-LEN(SUBSTITUTE(TEXTJOIN(" ",FALSE,COUNTIF($B$2:$H$15,"<"&TRANSPOSE($B$2:$H$15)))," ",""))+1)))=1),99)))+NUMBERVALUE(TRIM(MID(SUBSTITUTE(TEXTJOIN(" ",FALSE,TRANSPOSE(--($B$2:$H$15<>"")))," ",REPT(" ",99)),(ROW(OFFSET($A$1,,,LEN(TEXTJOIN(" ",FALSE,TRANSPOSE(--($B$2:$H$15<>""))))-LEN(SUBSTITUTE(TEXTJOIN(" ",FALSE,TRANSPOSE(--($B$2:$H$15<>"")))," ",""))+1))-1)*99+((ROW(OFFSET($A$1,,,LEN(TEXTJOIN(" ",FALSE,TRANSPOSE(--($B$2:$H$15<>""))))-LEN(SUBSTITUTE(TEXTJOIN(" ",FALSE,TRANSPOSE(--($B$2:$H$15<>"")))," ",""))+1)))=1),99))))-SUM(COUNTIF($B$2:$H$15,B$19:B19)),0)-1,ROWS($B$2:$H$15))+1,CEILING(MATCH(1,(NUMBERVALUE(TRIM(MID(SUBSTITUTE(TEXTJOIN(" ",FALSE,COUNTIF($B$2:$H$15,"<"&TRANSPOSE($B$2:$H$15)))," ",REPT(" ",99)),(ROW(OFFSET($A$1,,,LEN(TEXTJOIN(" ",FALSE,COUNTIF($B$2:$H$15,"<"&TRANSPOSE($B$2:$H$15))))-LEN(SUBSTITUTE(TEXTJOIN(" ",FALSE,COUNTIF($B$2:$H$15,"<"&TRANSPOSE($B$2:$H$15)))," ",""))+1))-1)*99+((ROW(OFFSET($A$1,,,LEN(TEXTJOIN(" ",FALSE,COUNTIF($B$2:$H$15,"<"&TRANSPOSE($B$2:$H$15))))-LEN(SUBSTITUTE(TEXTJOIN(" ",FALSE,COUNTIF($B$2:$H$15,"<"&TRANSPOSE($B$2:$H$15)))," ",""))+1)))=1),99)))+NUMBERVALUE(TRIM(MID(SUBSTITUTE(TEXTJOIN(" ",FALSE,TRANSPOSE(--($B$2:$H$15<>"")))," ",REPT(" ",99)),(ROW(OFFSET($A$1,,,LEN(TEXTJOIN(" ",FALSE,TRANSPOSE(--($B$2:$H$15<>""))))-LEN(SUBSTITUTE(TEXTJOIN(" ",FALSE,TRANSPOSE(--($B$2:$H$15<>"")))," ",""))+1))-1)*99+((ROW(OFFSET($A$1,,,LEN(TEXTJOIN(" ",FALSE,TRANSPOSE(--($B$2:$H$15<>""))))-LEN(SUBSTITUTE(TEXTJOIN(" ",FALSE,TRANSPOSE(--($B$2:$H$15<>"")))," ",""))+1)))=1),99))))-SUM(COUNTIF($B$2:$H$15,B$19:B19)),0),ROWS($B$2:$H$15))/ROWS($B$2:$H$15)),"")
C20:C42	C20	=IFERROR(INDIRECT(TEXT(MIN(IF($B$2:$H$15<>"",IF(ISERROR(MATCH($B$2:$H$15,$C$19:C19,0)),100000*ROW($B$2:$H$15)+COLUMN($B$2:$H$15)))),"\R0\C00000"),FALSE),"")
Dynamic array formulas.

 

[Peter_SSs]

Further, if I change cell E8, the results are even more unexpected.

20 11 03.xlsm
	A	B	C	D	E	F	G	H
1	DATE	ITEMS
2	9/22/2020	VGI				CTR		hh
3		KKK				LHL		kk
4
5				qqq
6				qqq
7		GGG		qqq		nnnn		HOL
8	9/24/2020	EVF		qqq	3445	C32		RDR
9			zz	qqq
10			zz	qqq
11			zz
12	9/25/2020	DGC	zz			VGI		FMD
13			zz
14	9/29/2020	VTP				BMI		SSS
15	9/30/2020	hhh				AAA		AAA
16
17
18
19	Post #:	25	14	15
20		3445	VGI	VGI
21			CTR	KKK
22			hh	GGG
23			KKK	EVF
24			LHL	DGC
25			kk	VTP
26			qqq	hhh
27			GGG	zz
28			nnnn	qqq
29			HOL	3445
30			EVF	CTR
31			3445	LHL
32			C32	nnnn
33			RDR	C32
34			zz	BMI
35			DGC	AAA
36			FMD	hh
37			VTP	kk
38			BMI	HOL
39			SSS	RDR
40			hhh	FMD
41			AAA	SSS
42
Test2

Cell Formulas
Range	Formula
D20:D41	D20	=LET(a,B2:H15,r,ROWS(a),seq,SEQUENCE(r*COLUMNS(a),,0),arr,INDEX(a,MOD(seq,r)+1,seq/r+1),UNIQUE(FILTER(arr,arr<>0)))
B20:B39	B20	=IFERROR(INDEX($B$2:$H$15,MOD(MATCH(1,(NUMBERVALUE(TRIM(MID(SUBSTITUTE(TEXTJOIN(" ",FALSE,COUNTIF($B$2:$H$15,"<"&TRANSPOSE($B$2:$H$15)))," ",REPT(" ",99)),(ROW(OFFSET($A$1,,,LEN(TEXTJOIN(" ",FALSE,COUNTIF($B$2:$H$15,"<"&TRANSPOSE($B$2:$H$15))))-LEN(SUBSTITUTE(TEXTJOIN(" ",FALSE,COUNTIF($B$2:$H$15,"<"&TRANSPOSE($B$2:$H$15)))," ",""))+1))-1)*99+((ROW(OFFSET($A$1,,,LEN(TEXTJOIN(" ",FALSE,COUNTIF($B$2:$H$15,"<"&TRANSPOSE($B$2:$H$15))))-LEN(SUBSTITUTE(TEXTJOIN(" ",FALSE,COUNTIF($B$2:$H$15,"<"&TRANSPOSE($B$2:$H$15)))," ",""))+1)))=1),99)))+NUMBERVALUE(TRIM(MID(SUBSTITUTE(TEXTJOIN(" ",FALSE,TRANSPOSE(--($B$2:$H$15<>"")))," ",REPT(" ",99)),(ROW(OFFSET($A$1,,,LEN(TEXTJOIN(" ",FALSE,TRANSPOSE(--($B$2:$H$15<>""))))-LEN(SUBSTITUTE(TEXTJOIN(" ",FALSE,TRANSPOSE(--($B$2:$H$15<>"")))," ",""))+1))-1)*99+((ROW(OFFSET($A$1,,,LEN(TEXTJOIN(" ",FALSE,TRANSPOSE(--($B$2:$H$15<>""))))-LEN(SUBSTITUTE(TEXTJOIN(" ",FALSE,TRANSPOSE(--($B$2:$H$15<>"")))," ",""))+1)))=1),99))))-SUM(COUNTIF($B$2:$H$15,B$19:B19)),0)-1,ROWS($B$2:$H$15))+1,CEILING(MATCH(1,(NUMBERVALUE(TRIM(MID(SUBSTITUTE(TEXTJOIN(" ",FALSE,COUNTIF($B$2:$H$15,"<"&TRANSPOSE($B$2:$H$15)))," ",REPT(" ",99)),(ROW(OFFSET($A$1,,,LEN(TEXTJOIN(" ",FALSE,COUNTIF($B$2:$H$15,"<"&TRANSPOSE($B$2:$H$15))))-LEN(SUBSTITUTE(TEXTJOIN(" ",FALSE,COUNTIF($B$2:$H$15,"<"&TRANSPOSE($B$2:$H$15)))," ",""))+1))-1)*99+((ROW(OFFSET($A$1,,,LEN(TEXTJOIN(" ",FALSE,COUNTIF($B$2:$H$15,"<"&TRANSPOSE($B$2:$H$15))))-LEN(SUBSTITUTE(TEXTJOIN(" ",FALSE,COUNTIF($B$2:$H$15,"<"&TRANSPOSE($B$2:$H$15)))," ",""))+1)))=1),99)))+NUMBERVALUE(TRIM(MID(SUBSTITUTE(TEXTJOIN(" ",FALSE,TRANSPOSE(--($B$2:$H$15<>"")))," ",REPT(" ",99)),(ROW(OFFSET($A$1,,,LEN(TEXTJOIN(" ",FALSE,TRANSPOSE(--($B$2:$H$15<>""))))-LEN(SUBSTITUTE(TEXTJOIN(" ",FALSE,TRANSPOSE(--($B$2:$H$15<>"")))," ",""))+1))-1)*99+((ROW(OFFSET($A$1,,,LEN(TEXTJOIN(" ",FALSE,TRANSPOSE(--($B$2:$H$15<>""))))-LEN(SUBSTITUTE(TEXTJOIN(" ",FALSE,TRANSPOSE(--($B$2:$H$15<>"")))," ",""))+1)))=1),99))))-SUM(COUNTIF($B$2:$H$15,B$19:B19)),0),ROWS($B$2:$H$15))/ROWS($B$2:$H$15)),"")
C20:C42	C20	=IFERROR(INDIRECT(TEXT(MIN(IF($B$2:$H$15<>"",IF(ISERROR(MATCH($B$2:$H$15,$C$19:C19,0)),100000*ROW($B$2:$H$15)+COLUMN($B$2:$H$15)))),"\R0\C00000"),FALSE),"")
Dynamic array formulas.