Unique in Range

How_Do_I

Well-known Member
Joined
Oct 23, 2009
Messages
1,831
Office Version
  1. 2010
Platform
  1. Windows
Hello, how would I count the unique in a range like this and extract them in to a vertical list please...?

Excel Workbook
BCDE
2Plum
3PlumWatermelonApplePeach
4MelonKiwifruitBlackberry
5RaspberryBlueberryCranberryBanana
Sheet1
 
Last edited by a moderator:

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
Found out how to count the unique...

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

Just need how to extract the unique now...
 
Last edited by a moderator:
Upvote 0
VBA or Power Query both give the following results.


Book1
FG
1FruitCount
2Plum2
3Watermelon1
4Apple1
5Peach1
6Melon1
7Kiwifruit1
8Blackberry1
9Raspberry1
10Blueberry1
11Cranberry1
12Banana1
Sheet2


VBA:
Code:
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:
Code:
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
 
Upvote 0
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
BCDEFGHI
1Unique ListCount Unique
2PlumPlum11
3PlumWatermelonApplePeachWatermelon
4MelonKiwifruitBlackberryApple
5RaspberryBlueberryCranberryBananaPeach
6Melon
7Kiwifruit
8Blackberry
9Raspberry
10Blueberry
11Cranberry
12Banana
13 
Sheet2
Cell Formulas
RangeFormula
I2I2=COUNTIF(H2:H20,"?*")
H2:H13H2=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),"")
 
Last edited:
Upvote 0
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))
 
Upvote 0
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. :)
 
Upvote 0
Thanks everyone... yes Peter_SSs I'm using XL 2010 so TEXTJOIN isn't available...
 
Upvote 0
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
BCDEFGHIJKL
1list
2s01s01s01s01s01s01s010
3s010
4s02s02s02s040
5s02s02s02s040
6s02s02s03s040
7s02s02s02s040
8s02s02s02s040
90
100
11s040
120
130
14s030
15s030
16s040
17s050
18s050
my range
Cell Formulas
RangeFormula
L2:L18L2=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),"")
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,947
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top