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]

The code stops short. There is indeed one blank cell, but it doesn't finish the column. The data is below, where Main Shaft Extreme is in the 1st cell of the 1st row, and all the rest are in the 2nd row, with a blank cell separating them, indicated with "blankcell".

Main Shaft Extreme
Main Gearbox (gears/bearings) Fatigue blankcell Gearbox Foundation Fatigue blankcell Main Shaft Fatigue Generator Mounts Fatigue blankcell Generator Flex Couplings & HSS Fatigue

 

[Rick Rothstein]

Here is how I would write the macro (output to Column F)...

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

 

[igold]

@Rick,

Actually, you did write the code. I tried to make the adjustment for the additional values in the range. When I was testing, I noticed that the Ubound of the array was correct yet it was not outputting all the values to the column. I was short one value, which is why I added 1 to Ubound(arr).

Could you explain to me why this is happening.

Regards,

igold

 

[igold]

The code stops short. There is indeed one blank cell, but it doesn't finish the column. The data is below, where Main Shaft Extreme is in the 1st cell of the 1st row, and all the rest are in the 2nd row, with a blank cell separating them, indicated with "blankcell".

Main Shaft Extreme
Main Gearbox (gears/bearings) Fatigue blankcell Gearbox Foundation Fatigue blankcell Main Shaft Fatigue Generator Mounts Fatigue blankcell Generator Flex Couplings & HSS Fatigue

How many columns is that? It is hard to tell from your description. It looks like more than 4 columns, it looks like 7 columns...

igold

 

[Rick Rothstein]

@Rick,

Actually, you did write the code. I tried to make the adjustment for the additional values in the range. When I was testing, I noticed that the Ubound of the array was correct yet it was not outputting all the values to the column. I was short one value, which is why I added 1 to Ubound(arr).

Could you explain to me why this is happening.

The problem (it is not actually a "problem" as such) happens when you assigned .keys to arr and then worked only from the arr array. The Keys property of a dictionary is a zero-based array so UBound for it is one less than the actual number of elements in the array, hence, you had to add one to account for that zero-eth element. I avoided that problem by not assigning it to an array but, rather, working directly with the dictionary object itself... I transposed the dictionary's Keys property directly and used the dictionary's Count property to find out how many keys there were.

 

[igold]

Makes sense, thanks. So the code as written should work.

If the OP is having issues, I suspect that the number of columns in his array is more than the four he indicated in the original post.

As always, thanks for your time.

igold

 

[Rick Rothstein]

Makes sense, thanks. So the code as written should work.

If the OP is having issues, I suspect that the number of columns in his array is more than the four he indicated in the original post.

We can only code for what the posters tell us which is why I always try to remember to tell them (when appropriate) not to simplify their questions for us as that will only get them great answers to a question they don't have and for which they really don't care.

 

[spacely]

Yes, 7 columns, but I changed the range in your code to engulf it. Should it not work?

 

[Rick Rothstein]

Yes, 7 columns, but I changed the range in your code to engulf it. Should it not work?

It should... why don't you show us the code you now have so that we can see if any other issues may have been raised.

 

[igold]

Let's see the changes you made.

I just made change to account for 7 columns and the code worked.