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

C

cmaslowsky

New Member
Joined
May 10, 2019
Messages
3
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!

[TABLE="width: 500, align: left"]
<tbody>[TR]
[TD]START WITH:[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]RESULT:[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]NUMBER[/TD]
[TD]COLOR[/TD]
[TD][/TD]
[TD][/TD]
[TD]NUMBER[/TD]
[TD]COLOR[/TD]
[TD]COLOR[/TD]
[TD]COLOR[/TD]
[TD]COLOR...[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]blue[/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[TD]blue[/TD]
[TD]red[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]red[/TD]
[TD][/TD]
[TD][/TD]
[TD]2[/TD]
[TD]blue[/TD]
[TD]green[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]blue[/TD]
[TD][/TD]
[TD][/TD]
[TD]3[/TD]
[TD]red[/TD]
[TD]green[/TD]
[TD]orange[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]green[/TD]
[TD][/TD]
[TD][/TD]
[TD]4[/TD]
[TD]red[/TD]
[TD]yellow[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]red[/TD]
[TD][/TD]
[TD][/TD]
[TD]5....[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]green[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]orange[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]red[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]yellow[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5....[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
Sort by date Sort by votes
Try this:-
Data in columns "A & B" Results start "D1".
Code: 
[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
 
Upvote 0
Perfect! Thanks Mick!

MickG said:
Try this:-
Data in columns "A & B" Results start "D1".
Code: 
[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
Click to expand...
 
Upvote 0
with PowerQuery

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]NUMBER[/td][td=bgcolor:#5B9BD5]COLOR[/td][td][/td][td=bgcolor:#70AD47]NUMBER[/td][td=bgcolor:#70AD47]Color.1[/td][td=bgcolor:#70AD47]Color.2[/td][td=bgcolor:#70AD47]Color.3[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
1​
[/td][td=bgcolor:#DDEBF7]blue[/td][td][/td][td=bgcolor:#E2EFDA]
1​
[/td][td=bgcolor:#E2EFDA]blue[/td][td=bgcolor:#E2EFDA]red[/td][td=bgcolor:#E2EFDA][/td][/tr]

[tr=bgcolor:#FFFFFF][td]
1​
[/td][td]red[/td][td][/td][td]
2​
[/td][td]blue[/td][td]green[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
2​
[/td][td=bgcolor:#DDEBF7]blue[/td][td][/td][td=bgcolor:#E2EFDA]
3​
[/td][td=bgcolor:#E2EFDA]red[/td][td=bgcolor:#E2EFDA]green[/td][td=bgcolor:#E2EFDA]orange[/td][/tr]

[tr=bgcolor:#FFFFFF][td]
2​
[/td][td]green[/td][td][/td][td]
4​
[/td][td]red[/td][td]yellow[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
3​
[/td][td=bgcolor:#DDEBF7]red[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]
3​
[/td][td]green[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
3​
[/td][td=bgcolor:#DDEBF7]orange[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]
4​
[/td][td]red[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
4​
[/td][td=bgcolor:#DDEBF7]yellow[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

Code: 
[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]
 
Upvote 0
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.
Code: 
[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
 
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Solved
Need code that converts colors from the RGB code to the Natural Color System (NCS)
Replies
6
Views
231
PeteWright
PeteWright
G
  • Solved
Individual 3 color scale for each row
Replies
8
Views
445
etaf
etaf
Rixn
  • Question Question
Conditional formatting cell color based on a number range.
Replies
4
Views
876
awoohaw
awoohaw
M
  • Question Question
Compare cell colour to other cell colour and trigger action automatically
Replies
7
Views
431
Maxime_Excel
M
N
  • Question Question
Find Latest comment for ID using vlookup/MAX?
Replies
1
Views
330
KOKOSEK
KOKOSEK

Forum statistics

Threads
1,223,974
Messages
6,175,739
Members
452,667
Latest member
vanessavalentino83

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top