Unique in Range

[How_Do_I]

Hello, how would I count the unique in a range like this and extract them in to a vertical list please...?

Excel Workbook
	B	C	D	E
2	Plum
3	Plum	Watermelon	Apple	Peach
4	Melon	Kiwifruit		Blackberry
5	Raspberry	Blueberry	Cranberry	Banana
Sheet1

 

[How_Do_I]

Found out how to count the unique...

G2=SUMPRODUCT((B2:E5<>"")/COUNTIF(B2:E5,B2:E5&""))

Just need how to extract the unique now...

 

[jasonb75]

This came up as the result of using the search box at the top of the page

https://www.mrexcel.com/forum/excel...umbers-multiple-columns-new-table-column.html

 

[lrobbo314]

VBA or Power Query both give the following results.

Book1
	F	G
1	Fruit	Count
2	Plum	2
3	Watermelon	1
4	Apple	1
5	Peach	1
6	Melon	1
7	Kiwifruit	1
8	Blackberry	1
9	Raspberry	1
10	Blueberry	1
11	Cranberry	1
12	Banana	1
Sheet2

VBA:

Sub UNIQUECOUNTS()
Dim SD As Object:       Set SD = CreateObject("Scripting.Dictionary")
Dim AR() As Variant:    AR = Range("B2:E" & Range("B" & Rows.Count).End(xlUp).Row).Value
Dim R As Range:         Set R = Range("G2")


For i = 1 To UBound(AR)
    For j = 1 To UBound(AR, 2)
        If AR(i, j) <> vbNullString Then
            If Not SD.exists(AR(i, j)) Then
                SD.Add AR(i, j), 1
            Else
                SD(AR(i, j)) = SD(AR(i, j)) + 1
            End If
        End If
    Next j
Next i


R.Resize(SD.Count, 1) = Application.Transpose(SD.keys)
R.Resize(SD.Count, 1).Offset(, 1) = Application.Transpose(SD.items)
End Sub

Power Query:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    List = Table.AddColumn(Source, "Custom", each Record.FieldValues(_)),
    Remove = Table.RemoveColumns(List,{"Column1", "Column2", "Column3", "Column4"}),
    Expanded = Table.ExpandListColumn(Remove, "Custom"),
    NoNull = Table.SelectRows(Expanded, each [Custom] <> null and [Custom] <> ""),
    Group = Table.Group(NoNull, {"Custom"}, {{"Count", each Table.RowCount(_), type number}}),
    Rename = Table.RenameColumns(Group,{{"Custom", "Fruit"}})
in
    Rename

 

[Peter_SSs]

Just need how to extract the unique now...

For a more concise formula approach than the link in post 2, this formula copied down as far as you might ever need.
It does use the volatile function INDIRECT but unless your list could be at least tens of thousands of unique items, that shouldn't cause a performance problem.

This also then gives a nice easy formula for the unique count.

Danhendo888 2.xlsm
	B	C	D	E	F	G	H	I
1							Unique List	Count Unique
2	Plum						Plum	11
3	Plum	Watermelon	Apple	Peach			Watermelon
4	Melon	Kiwifruit		Blackberry			Apple
5	Raspberry	Blueberry	Cranberry	Banana			Peach
6							Melon
7							Kiwifruit
8							Blackberry
9							Raspberry
10							Blueberry
11							Cranberry
12							Banana
13
Sheet2

Cell Formulas
Range	Formula
I2	I2	=COUNTIF(H2:H20,"?*")
H2:H13	H2	=IFERROR(INDIRECT(TEXT(AGGREGATE(15,6,(ROW(B$2:E$5)*10^6+COLUMN(B$2:E$5))/((B$2:E$5<>"")*(ISNA(MATCH(B$2:E$5,H$1:H1,0)))),1),"R000000C000000"),0),"")

 

[jasonb75]

Something that I just tried in another thread inspired this idea.

Using the same layout as Peter in the previous reply, try this array formula in H2, confirmed with Ctrl Shift Enter

=TRIM(LEFT(TEXTJOIN(REPT(" ",255),1,IF(ISERROR(MATCH($B$2:$E$5,H$1:H1,0)),$B$2:$E$5&"","")),255))

 

[Peter_SSs]

Something that I just tried in another thread inspired this idea.

Using the same layout as Peter in the previous reply, try this array formula in H2, confirmed with Ctrl Shift Enter

=TRIM(LEFT(TEXTJOIN(REPT(" ",255),1,IF(ISERROR(MATCH($B$2:$E$5,H$1:H1,0)),$B$2:$E$5&"","")),255))

Some comments
1. If the data is like the sample then 255 is much bigger than needed for the number of spaces. Only really needs to be as big as the longest expected text in the list. This is relevant as TEXTJOIN has a character limit of 32,767.
With data like the sample, that limit could be exceeded with a range of, say, 4 columns x 35 rows. Reducing the 255 to, say, 50 would allow a considerably larger range before exceeding the character limit.

2. Probably unlikely, but if the target range (B2:E5 here) contained any error value like #VALUE ! or #DIV/0! then the suggested formula will not return any values. Swapping ISERROR for ISNA in the suggested formula would avoid that for all errors except #N/A! errors

3. If the OP's signature is up-to-date, a TEXTJOIN solution will not be viable.

 

[How_Do_I]

Thanks everyone... yes Peter_SSs I'm using XL 2010 so TEXTJOIN isn't available...

 

[Peter_SSs]

Thanks everyone...

You're welcome.

 

[gianvi]

Hello to all,
i have to work in same situation as the file in #5.
i translated the formula to join my italian language and also replaced , with ; but still not working.
i miss something?
i tried both way with normal formula and the ctrl/shift/enter way.
office professional 2016

test2.xlsx
	B	C	D	E	F	G	H	I	J	K	L
1											list
2	s01	s01	s01	s01	s01	s01	s01				0
3						s01					0
4		s02	s02	s02				s04			0
5		s02	s02	s02				s04			0
6		s02	s02	s03				s04			0
7		s02	s02	s02				s04			0
8		s02	s02	s02				s04			0
9											0
10											0
11				s04							0
12											0
13											0
14						s03					0
15						s03					0
16						s04					0
17						s05					0
18						s05					0
my range

Cell Formulas
Range	Formula
L2:L18	L2	=IFERROR(INDIRECT(TEXT(AGGREGATE(15,6,(ROWS(B$2:I$18)*10^6+COLUMNS(B$2:I$18))/((B$2:I$18<>"")*(ISNA(MATCH(B$2:I$18,L$1:L1,0)))),1),"R000000C000000"),0),"")