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.

 

[DRSteele]

Function UNIQUE won't work for a block of values, just a row or a column. The solution is to stack all the column sections into one column and then use UNIQUE. One way is complex formulas. The best way to do it is PowerQuery.

Watch this ExcelIsFun video and see if it helps.

 

[Eric W]

I have Excel 365, but my company has been agonizingly slow in getting it updated to include the new dynamic array functions. So I wrote some UDFs to emulate the functionality. They are of course not exact, so whatever I try may not work on the actual functions. But given that try these 2 formulas:

B9:

=SORT(UNIQUE(INDEX(B1:C5,MOD(SEQUENCE(10,,0),5)+1,INT(SEQUENCE(10,,0)/5)+1)))

C9:

=COUNTIF(B1:C5,B9#)

 

[DRSteele]

Good show, Eric! I tinkered with the spilling formulas and came up with this. The blanks are irritating, but I kept experimenting and just used FILTER to rid the array of them. These columns and rows have to be contiguous for this type of approach to work.

MrExcel posts18.xlsx
	B	C
2	itemA	itemB
3	a	e
4	b	f
5	c	f
6	d	g
7		h
8	e
9	a	i
10
11
12	unique list, sorted	count of items
13	a	2
14	b	1
15	c	1
16	d	1
17	e	2
18	f	2
19	g	1
20	h	1
21	i	1
22
Sheet52

Cell Formulas
Range	Formula
B13:B21	B13	=FILTER(SORT(UNIQUE(INDEX(B3:C9,MOD(SEQUENCE(ROWS(B3:C9)*COLUMNS(B3:C9),,0),ROWS(B3:C9))+1,INT(SEQUENCE(ROWS(B3:C9)*COLUMNS(B3:C9),,0)/ROWS(B3:C9))+1))),SORT(UNIQUE(INDEX(B3:C9,MOD(SEQUENCE(ROWS(B3:C9)*COLUMNS(B3:C9),,0),ROWS(B3:C9))+1,INT(SEQUENCE(ROWS(B3:C9)*COLUMNS(B3:C9),,0)/ROWS(B3:C9))+1)))<>0)
C13:C21	C13	=COUNTIF($B$3:$C$9,B13#)
Dynamic array formulas.

 

[Peter_SSs]

A few other 'spill' options ..
- G2 if the range is not too big. The issue with the size of the range is that the REPT(" ",100) may have to be increased and in any case if the range is fairly larger the character limit for TEXTJOIN (32,767) may get exceeded & the formula will fail
- For larger ranges, or in any case, if you also have the new LET function try the I2 formula. This could still exceed the TEXTJOIN limit but should handle a much larger range before failing.
- If you do not have the LET function, J2 contains the same formula but without the shortenings enabled by LET

In all cases the spilling COUNTIF formula already suggested is still applicable.

I note also that the OP's results are sorted but there was no mention of that requirement so I have not sorted. Any of my formulas could be wrapped in a SORT function if required though.

20 10 29.xlsm
	B	C	D	E	F	G	H	I	J
1						List	Count	List	List
2	Plum					Plum	2	Plum	Plum
3	Plum	Watermelon	Apple	Melon		Watermelon	1	Watermelon	Watermelon
4	Melon	Kiwifruit		Blackberry		Apple	2	Apple	Apple
5	Raspberry	Melon	Apple	Melon		Melon	4	Melon	Melon
6						Kiwifruit	1	Kiwifruit	Kiwifruit
7						Blackberry	1	Blackberry	Blackberry
8						Raspberry	1	Raspberry	Raspberry
9
Unique Count 1

Cell Formulas
Range	Formula
G2:G8	G2	=UNIQUE(TRIM(MID(TEXTJOIN(REPT(" ",100),1,IF(B2:E5="","",B2:E5)),SEQUENCE(COUNTA(B2:E5),,1,100),100)))
H2:H8	H2	=COUNTIF(B2:E5,G2#)
I2:I8	I2	=LET(Cnt,COUNTA(B2:E5),TxtJ,"|"&TEXTJOIN("|",1,IF(B2:E5="","",B2:E5))&"|",Seq,SEQUENCE(Cnt),Subs,SUBSTITUTE(SUBSTITUTE(TxtJ,"|","#",Seq),"|","%",Seq),UNIQUE(REPLACE(LEFT(TxtJ,FIND("%",Subs)-1),1,FIND("#",Subs),"")))
J2:J8	J2	=UNIQUE(REPLACE(LEFT("|"&TEXTJOIN("|",1,IF(B2:E5="","",B2:E5))&"|",FIND("%",SUBSTITUTE(SUBSTITUTE("|"&TEXTJOIN("|",1,IF(B2:E5="","",B2:E5))&"|","|","#",SEQUENCE(COUNTA(B2:E5))),"|","%",SEQUENCE(COUNTA(B2:E5))))-1),1,FIND("#",SUBSTITUTE(SUBSTITUTE("|"&TEXTJOIN("|",1,IF(B2:E5="","",B2:E5))&"|","|","#",SEQUENCE(COUNTA(B2:E5))),"|","%",SEQUENCE(COUNTA(B2:E5)))),""))
Dynamic array formulas.

 

[Eric W]

Don, thanks for working out the details! I'd hoped that SORT might sort the empty cells to the end, but I guess not. The FILTER works, although it essentially doubles the size of the formula. Maybe using LET could help. I think this could work on some non-contiguous ranges. For example, if the columns are A1:A5 C1:C5, and E1:E5, you'd give INDEX the range A1:E5, and multiply the results of the INT portion by 2 (more or less). Still awkward, and there still would be limitations, but possible.

Peter, clever use of TEXTJOIN! A neat way to compress a 2-d text array to 1-d. The OP's original list is not quite sorted (EVF is out of place), but I think that was the intent.

 

[XcelLearner]

I have Excel 365, but my company has been agonizingly slow in getting it updated to include the new dynamic array functions. So I wrote some UDFs to emulate the functionality. They are of course not exact, so whatever I try may not work on the actual functions. But given that try these 2 formulas:

B9:

=SORT(UNIQUE(INDEX(B1:C5,MOD(SEQUENCE(10,,0),5)+1,INT(SEQUENCE(10,,0)/5)+1)))

C9:

=COUNTIF(B1:C5,B9#)

Thanks a lot, Eric. Your formulas work, except that they don't filter the blank cells. Appreciate.
By the way, I am testing your formulas, and see that the first formula works with small range. As I have a larger data set, I revise the "10" to "1000" and it seems to work well.

 

[Eric W]

Thanks a lot, Eric. Your formulas work, except that they don't filter the blank cells. Appreciate.
By the way, I am testing your formulas, and see that the first formula works with small range. As I have a larger data set, I revise the "10" to "1000" and it seems to work well.

The 10 represents 2 columns times 5 rows = 10. The 5 in the formula is the number of rows. So adjust it to your range accordingly. If you look at DRSteele's post (#4), he adjusted my original formula to figure out the number of rows and columns based on the range you use. So you don't need to figure out the row/column values, just put the range in all 14 places in the formula. He also added the FILTER function to remove blank cells.

Glad we could help!

 

[XcelLearner]

Thank you very much for your helps, Eric, Don, and Peter.

I tested three formulas (actually four, as Peter generously offered several options), and I've found that all approaches give consistent results. (Must be so, but it is still amazing for me, haha.) While Eric's and Don's solutions are a bit comprehensible for me, Peter's are things that I've seen first time. I like Peter's, because they look shorter (especially the I2 formula), though I don't understand how they work.
Thanks a lot for your time. Have a great day.

I might tweak the problem a bit, just for curiosity, but I need to work on those given formulas first.

 

[Peter_SSs]

You're welcome. You ended up with quite a lot of choice.
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.

20 10 29.xlsm
	B	C	D	E	F	G
1						List
2	Plum					Plum
3	Plum	Watermelon	Apple	Melon		Watermelon
4	Melon	Kiwifruit		Blackberry		Apple
5	Raspberry	Melon	Apple	Melon		Melon
6						Kiwifruit
7						Blackberry
8						Raspberry
9
Unique Count 1

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))))
Dynamic array formulas.