Get unique values from a 2d range?

[spacely]

Hi all,

I'd like to extract a list of unique values from a 2D range. So, text values from A1 to D10, some repeat. And in another column vector are listed just the uniques. I browsed here, but they cover uniques from just one column, not a range:

3 Ways to extract unique values from a range in Excel - Listen Data

Somehow I think one could tweak that?

Thanks!

David

 

[mikerickson]

By "uniques" do you mean
a) those elements that appear only once.
b) all the elements with duplicates removed.

i.e. if your data set is A, B, A, C do you want the result B, C or A, B, C

Also, is VBA acceptable?

 

[igold]

Hi David,

If you wanted to use VBA, you could try this method. This code will look at the Range("A1:D10), remove the duplicates and write the unique values to Column P starting at Cell P1.

Sub Unique()


Dim arr
Dim x As Long, i As Long


arr = Range("A1:D10")
    With CreateObject("Scripting.Dictionary")
    For x = LBound(arr) To UBound(arr)
        If Not IsMissing(arr(x, 1)) Then .Item(arr(x, 1)) = 1
            For i = 1 To 4
                If Not IsMissing(arr(x, i)) Then .Item(arr(x, i)) = 1
            Next
    Next
    arr = .Keys
    End With
    Range("P1").Resize(UBound(arr) + 1) = Application.Transpose(arr)
End Sub

I hope this helps.

igold

 

[mikerickson]

If you want those elements that appear only once, you can do this with Names

Define some names

Name: dataRange RefersTo: =Sheet1!$A$1:$D$10

Name: dataLocation RefersTo: =ROW(dataRange)+COLUMN(dataRange)/1000
Name: uniqueLocation RefersTo: =IF(COUNTIF(dataRange,dataRange)=1,dataLocation)
Name: wholeData RefersTo: =(Sheet1!$A:$A):dataRange

Then in F1 you can put the formula
=SMALL(uniqueLocation,ROWS($1:1))
and in G1, put
=INDEX(wholeData,INT(F1),1000*MOD(F1,1))

And then drag them down.

Or, avoid the use of a helper column, put putting
=INDEX(wholeData, INT(SMALL(uniqueLocation,ROWS($1:1))), 1000*MOD(SMALL(uniqueLocation,ROWS($1:1)),1))
in a cell and dragging that down

 

[T. Valko]

One way...

Data Range

	A​	B​	C​	D​	E​	F​
1​
2​	Text1​	Text1​	Text2​	Text3​		Text3​
3​	Text3​	Text3​	Text4​	Text4​		Text2​
4​	Text4​	Text5​	Text5​	Text5​		Text1​
5​	Text6​	Text7​	Text7​	Text8​		Text4​
6​	Text8​	Text8​	Text8​	Text8​		Text5​
7​						Text8​
8​						Text7​
9​						Text6​
10​

This array formula** entered in F2:

=IFERROR(LOOKUP("zzzzz",INDEX(IF(COUNTIF(F$1:F1,A$2:D$6),0,A$2:D$6),MIN(IF(COUNTIF(F$1:F1,A$2:D$6),"",ROW(A$2:D$6)-ROW(A$2)+1)),0)),"")

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Copy down until you get blanks.

Assumes no empty cells within the data range.

 

[spacely]

So, if my data set is A, B, A, C, it should return A, B, and C. Although order doesn't matter. But there will be blanks. I guess I'll look at the VBA example to see if it will work...

Thank you...

 

[seba64]

Hi David,

If you wanted to use VBA, you could try this method. This code will look at the Range("A1:D10), remove the duplicates and write the unique values to Column P starting at Cell P1.

Sub Unique()


Dim arr
Dim x As Long, i As Long


arr = Range("A1:D10")
    With CreateObject("Scripting.Dictionary")
    For x = LBound(arr) To UBound(arr)
        If Not IsMissing(arr(x, 1)) Then .Item(arr(x, 1)) = 1
            For i = 1 To 4
                If Not IsMissing(arr(x, i)) Then .Item(arr(x, i)) = 1
            Next
    Next
    arr = .Keys
    End With
    Range("P1").Resize(UBound(arr) + 1) = Application.Transpose(arr)
End Sub

I hope this helps.

igold

application.transpose? That exist? Ever i had to create a function transpose

 

[spacely]

igold, that VBA messes up if there are blank cells. Would a small change fix it?

 

[igold]

Can you be more explicit rather than "messes up". In my tests, if there was a blank cell or more than one blank cell in the range, the code returned a single blank cell to the column.

Code could be added to operate on the column to remove the blank cell if it is unwanted.

 

[igold]

You could add this line as the very last line of code:

Range("P1:P" & UBound(arr)).SpecialCells(xlCellTypeBlanks).Delete

I hope this helps.

igold