Returning Unique Values in Two Dimensional Range

[smiley3141]

I have a worksheet that currently has over 25,000 rows (it should double in the next year or two) and 70 columns. I need to compare the results in 60 of the columns and find the unique answers. Using conditional formatting to hilight the unique values is not sufficient for my needs. I actually need to produce a list of unique values. Given the number of rows and columns involved, I do not want to copy the data from the 60 columns into a single row.

Is there some way to search a two dimensional range and return all the unique values?

Thank you for any help you can offer me.

 

[Matty]

Welcome to the Board...

Could you provide a small example of your data and illustrate what you want any solution to do?

Matty

 

[MickG]

Hi, Try this:-
This code ouputs the Uniques to Sheet (2) column "A".
For 25K rows x 70 columns Takes about 9 seconds.

[COLOR="Navy"]Sub[/COLOR] MG14Aug12
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Dn [COLOR="Navy"]As[/COLOR] Variant, t
[COLOR="Navy"]Dim[/COLOR] Ray [COLOR="Navy"]As[/COLOR] Variant
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
[COLOR="Navy"]Set[/COLOR] Rng = Rng.Resize(, 70)
Ray = Rng
t = Timer
[COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
    .CompareMode = vbTextCompare
        [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Ray
            [COLOR="Navy"]If[/COLOR] Not .Exists(Dn) [COLOR="Navy"]Then[/COLOR] .Add Dn, ""
        [COLOR="Navy"]Next[/COLOR]
Sheets("Sheet2").Range("A1").Resize(.Count) = Application.Transpose(.Keys)
[COLOR="Navy"]End[/COLOR] With
MsgBox Timer - t
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[Mike Szczesny]

Hi Smiley,

If I understand you correctly, you want to find out the unique values accross a range of columns for a given row. Do you need the total amount of unique numbers for each row (accross those columns)? Or would you just need a column of unique values going down the spreadsheet ? Are we talking numbers or text? Please advise.
Mike Szczesny

 

[smiley3141]

Sorry for the delay. Thanks for the help. It works.