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]

So just to confuse you some more, here is yet another one that is a bit shorter again (provide you have LET) and does not have the potential problem with TEXTJOIN that my other formulas had.

Best version yet! I especially like how you can use AGGREGATE to add additional conditions to exclude values if desired.

Here's another shorter version using LET:

=LET(r,ROWS(B2:E5),seq,SEQUENCE(r*COLUMNS(B2:E5),,0),arr,INDEX(B2:E5,MOD(seq,r)+1,seq/r+1),UNIQUE(FILTER(arr,arr<>0)))

Regrettably, I still don't have the ability to test it.

 

[Fluff]

That works for me

+Fluff v2.xlsm
	A	B	C	D	E	F	G	H
1							Peter	Eric
2		Plum					Plum	Plum
3		Plum	Watermelon	Apple	Melon		Watermelon	Melon
4		Melon	Kiwifruit		Blackberry		Apple	Raspberry
5		Raspberry	Melon	Apple	Melon		Melon	Watermelon
6							Kiwifruit	Kiwifruit
7							Blackberry	Apple
8							Raspberry	Blackberry
9
Sheet2

Cell Formulas
Range	Formula
G2:G8	G2	=LET(Agg,AGGREGATE(15,6,((ROW(B2:E5)-ROW(B2)+1)*10^6+COLUMN(B2:E5)-COLUMN(B2)+1)/(B2:E5<>""),SEQUENCE(COUNTA(B2:E5))),UNIQUE(INDEX(B2:E5,Agg/10^6,RIGHT(Agg,6))))
H2:H8	H2	=LET(r,ROWS(B2:E5),seq,SEQUENCE(r*COLUMNS(B2:E5),,0),arr,INDEX(B2:E5,MOD(seq,r)+1,seq/r+1),UNIQUE(FILTER(arr,arr<>0)))
Dynamic array formulas.

 

[DRSteele]

Eric and Peter, that is terrific stuff.

 

[pgc01]

Hi

Also a solution if you can't (won't) use the new functions.
I used Peter's layout and values. With F1 empty or with a value not in the target range, in F2:

=IFERROR(INDIRECT(TEXT(MIN(IF($A$2:$D$5<>"",IF(ISERROR(MATCH($A$2:$D$5,$F$1:F1,0)),100000*ROW($A$2:$D$5)+COLUMN($A$2:$D$5)))),"\R0\C00000"),FALSE),"")

This is an array formula, you have to confirm it with CTRL-SHIFT-ENTER
Copy down

 

[Peter_SSs]

This is an array formula, you have to confirm it with CTRL-SHIFT-ENTER

To clarify, in the case of the OP, who apparently has the new array functions, C+S+E confirmation is not required
Great idea for the problem though!

I also like Eric's latest 'LET' formula.

But since we have been continually shortening the formulas, all the previous LET formulas can be shortened a tiny bit further with one more assignment pair. Here is Eric's with that addition

=LET(a,B2:E5,r,ROWS(a),seq,SEQUENCE(r*COLUMNS(a),,0),arr,INDEX(a,MOD(seq,r)+1,seq/r+1),UNIQUE(FILTER(arr,arr<>0)))

? .. and if we wanted to be pedantic, not including reducing all the variable names to single characters, one more character could be saved by changing the value of seq from
SEQUENCE(r*COLUMNS(a),,0)
to
SEQUENCE(r*COLUMNS(a))-1
.. though I believe that does make the calculation a little less efficient so not worth it anyway.

 

[Eric W]

I like Peter's addition, not just for the shortness, but because it is easier to maintain. In this version, the range only has to be entered 1 time instead of 3. Less chance for someone to miss one.

I thought of a way to shorten it by 2 characters (without changing variable names):

=LET(a,B2:E5,r,ROWS(a),seq,SEQUENCE(r*COLUMNS(a),,r),arr,INDEX(a,MOD(seq,r)+1,seq/r),UNIQUE(FILTER(arr,arr<>0)))

and this actually might make it a tad more efficient, but I still prefer the previous version. I like the symmetry of the +1, +1 and I think it makes the formula easier to understand. Which raises another point. This is a fairly complicated formula, but with proper use of LET, I think it's pretty easy to see what it's doing. I think that's really more valuable than the reduction in size.

 

[Dossfm0q]

Greetings
this works for Office 2019
What about this, Feedback Please
I have this unique but sorted from Lowest to highest
Press CTRL+SHIFT+ENTER to enter array formulas.

MREXCEL.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
19
Unique values for an array of n

Cell Formulas
Range	Formula
B11:B19	B11	=IFERROR(INDEX($B$2:$C$6,MOD(MATCH(1,(FILTERXML("<t><s>"&SUBSTITUTE(TEXTJOIN("/",FALSE,SUBSTITUTE(COUNTIF($B$2:$C$6,"<"&TRANSPOSE($B$2:$C$6)),";",",")),"/","</s><s>")&"</s></t>","//s[count(node())>0]")+(--FILTERXML("<t><s>"&SUBSTITUTE(TEXTJOIN("/",FALSE,SUBSTITUTE(TRANSPOSE($B$2:$C$6<>""),";",",")),"/","</s><s>")&"</s></t>","//s[count(node())>0]")))-SUM(COUNTIF($B$2:$C$6,B$10:B10)),0)-1,ROWS($B$2:$C$6))+1,CEILING(MATCH(1,(FILTERXML("<t><s>"&SUBSTITUTE(TEXTJOIN("/",FALSE,SUBSTITUTE(COUNTIF($B$2:$C$6,"<"&TRANSPOSE($B$2:$C$6)),";",",")),"/","</s><s>")&"</s></t>","//s[count(node())>0]")+(--FILTERXML("<t><s>"&SUBSTITUTE(TEXTJOIN("/",FALSE,SUBSTITUTE(TRANSPOSE($B$2:$C$6<>""),";",",")),"/","</s><s>")&"</s></t>","//s[count(node())>0]")))-SUM(COUNTIF($B$2:$C$6,B$10:B10)),0),ROWS($B$2:$C$6))/ROWS($B$2:$C$6)),"")
C11:C19	C11	=IF(B11<>"",COUNTIF($B$2:$C$6,B11),"")
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Peter_SSs]

... with proper use of LET, I think it's pretty easy to see what it's doing. I think that's really more valuable than the reduction in size.

I agree with both points.

 

[XcelLearner]

Thank you very much to all of you for a very lively discussion. I benefit a lot, though I don't understand much of the formulae. For me, many formulae are ones I first saw, and I've tried all of them.
For Dossfm0q's solution, I couldn't make it to work, even when I was mindful for a CSE. I CSE'ed and dragged down but it didn't come out with with same output with other formulae.

To clarify, in the case of the OP, who apparently has the new array functions, C+S+E confirmation is not required
Great idea for the problem though!

I also like Eric's latest 'LET' formula.

But since we have been continually shortening the formulas, all the previous LET formulas can be shortened a tiny bit further with one more assignment pair. Here is Eric's with that addition

=LET(a,B2:E5,r,ROWS(a),seq,SEQUENCE(r*COLUMNS(a),,0),arr,INDEX(a,MOD(seq,r)+1,seq/r+1),UNIQUE(FILTER(arr,arr<>0)))

? .. and if we wanted to be pedantic, not including reducing all the variable names to single characters, one more character could be saved by changing the value of seq from
SEQUENCE(r*COLUMNS(a),,0)
to
SEQUENCE(r*COLUMNS(a))-1
.. though I believe that does make the calculation a little less efficient so not worth it anyway.

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.

 

[sandy666]

another approach

Date	Value1	Value2		Value	Count
9/22/2020	VGI	CTR		BMI	1
9/24/2020	EVF	C32		C32	1
9/25/2020	DGC			CTR	1
9/29/2020	VTP	BMI		DGC	1
9/30/2020	VGI	DIG		DIG	1
				EVF	1
				VGI	2
				VTP	1

let
    Source = Excel.CurrentWorkbook(){[Name="Table4"]}[Content],
    RC = Table.RemoveColumns(Source,{"Date"}),
    Unpivot = Table.UnpivotOtherColumns(RC, {}, "Attribute", "Value"),
    TSC = Table.SelectColumns(Unpivot,{"Value"}),
    Group = Table.Group(TSC, {"Value"}, {{"Count", each Table.RowCount(_), type number}}),
    Sort = Table.Sort(Group,{{"Value", Order.Ascending}})
in
    Sort