Convert a two column list (A,B) into unique rows based on value of A

[cmaslowsky]

I am looking for the most efficient way to take a two column table of data and convert it into a multi column table of only unique rows based on the value of the first column (see simple example below). Can be a VBA script or formulas. I am working with data that starts at around 10,000 rows of two columns, which will result in a list of about 1000 rows ranging from 1 associated result to 100 results, so I need to make sure it can process relatively efficiently.

Thanks!

START WITH:				RESULT:
NUMBER	COLOR			NUMBER	COLOR	COLOR	COLOR	COLOR...
1	blue			1	blue	red
1	red			2	blue	green
2	blue			3	red	green	orange
2	green			4	red	yellow
3	red			5....
3	green
3	orange
4	red
4	yellow
5....

<tbody>
</tbody>

 

[MickG]

Try this:-
Data in columns "A & B" Results start "D1".

[COLOR="Navy"]Sub[/COLOR] MG10May08
[COLOR="Navy"]Dim[/COLOR] Ray [COLOR="Navy"]As[/COLOR] Variant, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Ac [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Temp [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String,[/COLOR] Col [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
Ray = Range(Range("A1"), Range("B" & Rows.Count).End(xlUp))
ReDim nray(1 To UBound(Ray, 1), 1 To UBound(Ray, 1))
nray(1, 1) = "Number"
nray(1, 2) = "Color"
c = 1
[COLOR="Navy"]For[/COLOR] n = 2 To UBound(Ray, 1)
    [COLOR="Navy"]If[/COLOR] Not Ray(n, 1) = Temp [COLOR="Navy"]Then[/COLOR]
        Ac = 2: c = c + 1
        nray(c, 1) = Ray(n, 1)
        nray(c, 2) = Ray(n, 2)
    [COLOR="Navy"]Else[/COLOR]
        Ac = Ac + 1
         Col = IIf(Ac > Col, Ac, Col)
        nray(1, Ac) = "Color"
        nray(c, Ac) = Ray(n, 2)
    [COLOR="Navy"]End[/COLOR] If
Temp = Ray(n, 1)
[COLOR="Navy"]Next[/COLOR] n

Range("D1").Resize(c, Col).Value = nray

Regards Mick

 

[cmaslowsky]

Perfect! Thanks Mick!

Try this:-
Data in columns "A & B" Results start "D1".

[COLOR=Navy]Sub[/COLOR] MG10May08
[COLOR=Navy]Dim[/COLOR] Ray [COLOR=Navy]As[/COLOR] Variant, n [COLOR=Navy]As[/COLOR] [COLOR=Navy]Long,[/COLOR] c [COLOR=Navy]As[/COLOR] [COLOR=Navy]Long,[/COLOR] Ac [COLOR=Navy]As[/COLOR] [COLOR=Navy]Long,[/COLOR] Temp [COLOR=Navy]As[/COLOR] [COLOR=Navy]String,[/COLOR] Col [COLOR=Navy]As[/COLOR] [COLOR=Navy]Long[/COLOR]
Ray = Range(Range("A1"), Range("B" & Rows.Count).End(xlUp))
ReDim nray(1 To UBound(Ray, 1), 1 To UBound(Ray, 1))
nray(1, 1) = "Number"
nray(1, 2) = "Color"
c = 1
[COLOR=Navy]For[/COLOR] n = 2 To UBound(Ray, 1)
    [COLOR=Navy]If[/COLOR] Not Ray(n, 1) = Temp [COLOR=Navy]Then[/COLOR]
        Ac = 2: c = c + 1
        nray(c, 1) = Ray(n, 1)
        nray(c, 2) = Ray(n, 2)
    [COLOR=Navy]Else[/COLOR]
        Ac = Ac + 1
         Col = IIf(Ac > Col, Ac, Col)
        nray(1, Ac) = "Color"
        nray(c, Ac) = Ray(n, 2)
    [COLOR=Navy]End[/COLOR] If
Temp = Ray(n, 1)
[COLOR=Navy]Next[/COLOR] n

Range("D1").Resize(c, Col).Value = nray

Regards Mick

 

[MickG]

You're welcome

 

[cmaslowsky]

Hi Mick-
It's now resulting in an error that reads "method resize of object range failed"
Any thoughts?
Thanks,
Craig

You're welcome

 

[sandy666]

with PowerQuery

NUMBER	COLOR		NUMBER	Color.1	Color.2	Color.3
1​	blue		1​	blue	red
1​	red		2​	blue	green
2​	blue		3​	red	green	orange
2​	green		4​	red	yellow
3​	red
3​	green
3​	orange
4​	red
4​	yellow

[SIZE=1]// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Group = Table.Group(Source, {"NUMBER"}, {{"Count", each _, type table}}),
    List = Table.AddColumn(Group, "Color", each Table.Column([Count],"COLOR")),
    Extract = Table.TransformColumns(List, {"Color", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    SplitCount = Table.AddColumn(Extract, "Split Count", each List.Count(Text.Split([Color],","))),
    MaxCount = List.Max(SplitCount[Split Count]),
    Split = Table.SplitColumn(Extract, "Color", Splitter.SplitTextByDelimiter(","), MaxCount)
in
    Split[/SIZE]

 

[MickG]

You could try explicitly naming the sheet as per code below:-
NB:Change "Sheet1" In second line to your data sheet name

If that does not work, you could post an example of data that failed.

[COLOR="Navy"]Sub[/COLOR] MG11May29
[COLOR="Navy"]Dim[/COLOR] Ray [COLOR="Navy"]As[/COLOR] Variant, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Ac [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Temp [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String,[/COLOR] Col [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]With[/COLOR] Sheets("Sheet1")
Ray = .Range("A1", .Range("B" & Rows.Count).End(xlUp))

ReDim nray(1 To UBound(Ray, 1), 1 To UBound(Ray, 1))
nray(1, 1) = "Number"
nray(1, 2) = "Color"
c = 1
[COLOR="Navy"]For[/COLOR] n = 2 To UBound(Ray, 1)
    [COLOR="Navy"]If[/COLOR] Not Ray(n, 1) = Temp [COLOR="Navy"]Then[/COLOR]
        Ac = 2: c = c + 1
        nray(c, 1) = Ray(n, 1)
        nray(c, 2) = Ray(n, 2)
    [COLOR="Navy"]Else[/COLOR]
        Ac = Ac + 1
         Col = IIf(Ac > Col, Ac, Col)
        nray(1, Ac) = "Color"
        nray(c, Ac) = Ray(n, 2)
    [COLOR="Navy"]End[/COLOR] If
Temp = Ray(n, 1)
[COLOR="Navy"]Next[/COLOR] n

.Range("D1").Resize(c, Col).Value = nray
[COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick