Extracting Unique Values from Rows

[fireboltpk]

Hi,

I have a very large data set, 10,000 rows and 150 columns.

I need to extract and display unique values from the rows. My data is arranged as follows.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Boston[/TD]
[TD]3[/TD]
[TD][/TD]
[TD]5[/TD]
[TD][/TD]
[TD]3[/TD]
[TD]7[/TD]
[TD][/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]NYC[/TD]
[TD]1[/TD]
[TD][/TD]
[TD]2[/TD]
[TD][/TD]
[TD]2[/TD]
[TD][/TD]
[TD]4[/TD]
[TD]8[/TD]
[/TR]
</tbody>[/TABLE]


Desired Result Display:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Boston[/TD]
[TD]3[/TD]
[TD]5[/TD]
[TD]7[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]NYC[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]4[/TD]
[TD]8[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


There over over 100 unique values in the rows I have.

What is the best way to go about it?

Thanks!

 

[MickG]

Try this for results on sheet2. starting "A1".

[COLOR="Navy"]Sub[/COLOR] MG29May13
[COLOR="Navy"]Dim[/COLOR] Ray [COLOR="Navy"]As[/COLOR] Variant, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] col [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] oMax [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Dic [COLOR="Navy"]As[/COLOR] Object, Ac [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
 Ray = Range("A1").CurrentRegion
  ReDim nray(1 To UBound(Ray, 1), 1 To UBound(Ray, 2))
    [COLOR="Navy"]For[/COLOR] n = 2 To UBound(Ray)
         c = c + 1
         nray(c, 1) = Ray(n, 2): col = 1
       [COLOR="Navy"]Set[/COLOR] Dic = CreateObject("Scripting.Dictionary")
            [COLOR="Navy"]For[/COLOR] Ac = 3 To UBound(Ray, 2)
               [COLOR="Navy"]If[/COLOR] Not IsEmpty(Ray(n, Ac)) [COLOR="Navy"]Then[/COLOR]
                  [COLOR="Navy"]If[/COLOR] Not Dic.exists(Ray(n, Ac)) [COLOR="Navy"]Then[/COLOR]
                     col = col + 1
                     oMax = Application.Max(oMax, col)
                     nray(c, col) = Ray(n, Ac)
                     Dic.Add Ray(n, Ac), Nothing
                  [COLOR="Navy"]End[/COLOR] If
                [COLOR="Navy"]End[/COLOR] If
            [COLOR="Navy"]Next[/COLOR] Ac
   [COLOR="Navy"]Next[/COLOR] n
Sheets("Sheet2").Range("A1").Resize(UBound(Ray, 1), oMax).Value = nray
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick