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

 

[spacely]

Rick, your code works. I have 9 columns. I didn't change the data between attempts.

Dim Rng As Range, V As Variant, Data As Variant
Data = Range("C3:K4")
With CreateObject("Scripting.Dictionary")
For Each V In Data
If Len(V) Then .Item(V) = 1
Next
Range("J15").Resize(.Count) = Application.Transpose(.Keys)
End With

 

[Rick Rothstein]

Data = Range("C3:K4")

The reason igold's code did not work for you when you made the above change is that was not the only change you needed to make for his code (my code works slightly differently, so it wasn't a problem for you there). In igold's code is this line of code...

For i = 1 to 4

The 4 is the number of columns... when you changed the number of columns in the range being processed, you needed to account for it in the above line of code as well. If you had changed that line to this...

For i = 1 to 9

along with the range change, then his code would have worked for you as well.

 

[igold]

Errant post.

 

[Rick Rothstein]

@Rick,

You took care of the blanks with this line...

If Len(V) Then .Item(V) = 1

Is that correct...

Yes... if there length was 0 (blank, no text), that is the same as being False and if the length was greater than 0, VB considers that True (to VB, True is actually determined by it being Not False, or with numbers, Not 0).

 

[spacely]

Ah. Hard-coded. Good solutions, and thank you

 

[igold]

Yes... if there length was 0 (blank, no text), that is the same as being False and if the length was greater than 0, VB considers that True (to VB, True is actually determined by it being Not False, or with numbers, Not 0).

@Rick,

Yes I realized that quickly after I posted the question so I deleted it. However what you have said here is what I had originally done in the post in which you and Mark are discussing .HasFormula. I used this logic on .Formula in my code and Mark had changed it to .HasFormula.

 

[Rick Rothstein]

@Rick,

However what you have said here is what I had originally done in the post in which you and Mark are discussing .HasFormula.

You really should have posted your comment in the other thread where I think your comment would have been more relevant.


O.T.

I used this logic on .Formula in my code and Mark had changed it to .HasFormula.

You cannot use that shortcut with the Formula property because it contains a text String value only, not a Boolean. If you wanted to test if the cell has a formula or not using the Formula property, you would have to test its length.

 

[spacely]

I can mark this thread solved if you tell me how...

 

[Rick Rothstein]

I can mark this thread solved if you tell me how...

This forum does not have a way to do that... most threads are assumed closed when the original poster posts a final "thank you" message which indicates the problem was solved.

 

[spacely]

OK. Must be the Matlab forum I'm thinking of.

Thank you