Remove duplicates in multiple columns and return the names in one column horizontally

brandnewbie

New Member
Joined
Jul 21, 2012
Messages
6
Hi,

I don't have the Excel skills or knowledge to even attempt this so it will be easier to show the before and after of what I'm trying to achieve. I desperately need to change this source data:

User NameEntity CodeBusiness AreaLevel 1Level 2Level 3Level 4
Howard Ratner98.1.3456Web HostingY
Nick Kemp98.1.3456Web HostingY
Nick Kemp98.1.3456Web HostingY
Cheryl Williams98.1.3456Web HostingY
Howard Ratner97.1.3456Web HostingY
To be defined97.1.3456Web HostingY
Roger Hirst97.1.3456Web HostingY
Nick Kemp97.1.3456Web HostingY
Gerald Richards26.3.1117HRyyyy

<tbody>
</tbody>


...into this if possible:

Entity CodeBusiness AreaLevel 1Level 2Level 3Level 4
98.1.3456Web HostingNick KempNick KempHoward RatnerCheryl Williams
97.1.3456Web HostingRoger HirstTo be definedHoward RatnerNick Kemp
26.3.1117HRGerald RichardsGerald RichardsGerald RichardsGerald Richards

<tbody>
</tbody>

Any help would be GREATLY appreciated. Thank you!!!!
 

Some videos you may like

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

MickG

MrExcel MVP
Joined
Jan 9, 2008
Messages
14,841
Try this:-
Results sheet(2)
Code:
[COLOR="Navy"]Sub[/COLOR] MG21Jul07
[COLOR="Navy"]Dim[/COLOR] Rng         [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Dn          [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] n           [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Col         [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
[COLOR="Navy"]Dim[/COLOR] c            [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("B1"), Range("B" & Rows.Count).End(xlUp))
    ReDim ray(1 To Rng.Count, 1 To 6)
        [COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
            .CompareMode = vbTextCompare
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    [COLOR="Navy"]If[/COLOR] Not .Exists(Dn.Value) [COLOR="Navy"]Then[/COLOR]
        n = n + 1
        .Add Dn.Value, n
            [COLOR="Navy"]For[/COLOR] Col = 1 To 6
                ray(n, Col) = IIf(UCase(Dn(, Col)) = "Y", Dn.Offset(, -1), Dn(, Col))
            [COLOR="Navy"]Next[/COLOR] Col
    [COLOR="Navy"]Else[/COLOR]
        [COLOR="Navy"]For[/COLOR] Col = 3 To 6
            [COLOR="Navy"]If[/COLOR] UCase(Dn(, Col)) = "Y" [COLOR="Navy"]Then[/COLOR] ray(.Item(Dn.Value), Col) = Dn.Offset(, -1)
         [COLOR="Navy"]Next[/COLOR] Col
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR]
c = .Count
[COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]With[/COLOR] Sheets("sheet2")
    .Range("A1").Resize(c, 6) = ray
    .Columns.AutoFit
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Regards Mick
 

brandnewbie

New Member
Joined
Jul 21, 2012
Messages
6
Wow! I had no idea it would be that complex. Thank you so much for the speedy response but I fear I will never be able to understand or modify that! Is there anyway of doing this without using code, even if it's multiple crude steps?
 

brandnewbie

New Member
Joined
Jul 21, 2012
Messages
6
Hi Mick,

Thanks again for your solution. Looks like I'm stuck with using code to resolve this.

In running the code you've kindly provided, I've picked up on some other issues with the data set which I hope you might be able to help with. Basically, the Entity Code is not a unique identifier for Business Area and may have a one to many relationship. I have pasted a revised version of the table in the hope that a slight amendment to the code can accommodate this? Many thanks again for your help!

User NameEntity CodeBusiness AreaLevel 1Level 2Level 3Level 4
Howard Ratner98.1.3456Web Hosting 1Y
Nick Kemp98.1.3456Web Hosting 1Y
Nick Kemp98.1.3456Web Hosting 2Y
Cheryl Williams98.1.3456Web Hosting 2Y
Howard Ratner97.1.3456Web Hosting 1Y
To be defined97.1.3456Web Hosting 1Y
Roger Hirst97.1.3456Web Hosting 1Y
Nick Kemp97.1.3456Web Hosting 1Y
Gerald Richards26.3.1117HRyyyy

<tbody>
</tbody>
 

brandnewbie

New Member
Joined
Jul 21, 2012
Messages
6

ADVERTISEMENT

Try this:-
Results sheet(2)
Code:
[COLOR=Navy]Sub[/COLOR] MG21Jul07
[COLOR=Navy]Dim[/COLOR] Rng         [COLOR=Navy]As[/COLOR] Range
[COLOR=Navy]Dim[/COLOR] Dn          [COLOR=Navy]As[/COLOR] Range
[COLOR=Navy]Dim[/COLOR] n           [COLOR=Navy]As[/COLOR] [COLOR=Navy]Long[/COLOR]
[COLOR=Navy]Dim[/COLOR] Col         [COLOR=Navy]As[/COLOR] [COLOR=Navy]Integer[/COLOR]
[COLOR=Navy]Dim[/COLOR] c            [COLOR=Navy]As[/COLOR] [COLOR=Navy]Long[/COLOR]
[COLOR=Navy]Set[/COLOR] Rng = Range(Range("B1"), Range("B" & Rows.Count).End(xlUp))
    ReDim ray(1 To Rng.Count, 1 To 6)
        [COLOR=Navy]With[/COLOR] CreateObject("scripting.dictionary")
            .CompareMode = vbTextCompare
[COLOR=Navy]For[/COLOR] [COLOR=Navy]Each[/COLOR] Dn [COLOR=Navy]In[/COLOR] Rng
    [COLOR=Navy]If[/COLOR] Not .Exists(Dn.Value) [COLOR=Navy]Then[/COLOR]
        n = n + 1
        .Add Dn.Value, n
            [COLOR=Navy]For[/COLOR] Col = 1 To 6
                ray(n, Col) = IIf(UCase(Dn(, Col)) = "Y", Dn.Offset(, -1), Dn(, Col))
            [COLOR=Navy]Next[/COLOR] Col
    [COLOR=Navy]Else[/COLOR]
        [COLOR=Navy]For[/COLOR] Col = 3 To 6
            [COLOR=Navy]If[/COLOR] UCase(Dn(, Col)) = "Y" [COLOR=Navy]Then[/COLOR] ray(.Item(Dn.Value), Col) = Dn.Offset(, -1)
         [COLOR=Navy]Next[/COLOR] Col
    [COLOR=Navy]End[/COLOR] If
[COLOR=Navy]Next[/COLOR]
c = .Count
[COLOR=Navy]End[/COLOR] With
[COLOR=Navy]With[/COLOR] Sheets("sheet2")
    .Range("A1").Resize(c, 6) = ray
    .Columns.AutoFit
[COLOR=Navy]End[/COLOR] [COLOR=Navy]With[/COLOR]
[COLOR=Navy]End[/COLOR] [COLOR=Navy]Sub[/COLOR]
Regards Mick

Hi Mick,

Thanks again for your solution. Looks like I'm stuck with using code to resolve this.

In running the code you've kindly provided, I've picked up on some other issues with the data set which I hope you might be able to help with. Basically, the Entity Code is not a unique identifier for Business Area and may have a one to many relationship. I have pasted a revised version of the table in the hope that a slight amendment to the code can accommodate this? Many thanks again for your help!

User NameEntity CodeBusiness AreaLevel 1Level 2Level 3Level 4
Howard Ratner98.1.3456Web Hosting 1Y
Nick Kemp98.1.3456Web Hosting 1Y
Nick Kemp98.1.3456Web Hosting 2Y
Cheryl Williams98.1.3456Web Hosting 2Y
Howard Ratner97.1.3456Web Hosting 1Y
To be defined97.1.3456Web Hosting 1Y
Roger Hirst97.1.3456Web Hosting 1Y
Nick Kemp97.1.3456Web Hosting 1Y
Gerald Richards26.3.1117HRyyyy

<tbody>
</tbody>
 

MickG

MrExcel MVP
Joined
Jan 9, 2008
Messages
14,841
If this is not the solution, please post the expected results as well as the basic data.
Code:
[COLOR="Navy"]Sub[/COLOR] MG22Jul34
[COLOR="Navy"]Dim[/COLOR] Rng         [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Dn          [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] n           [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Col         [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
[COLOR="Navy"]Dim[/COLOR] c           [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Twn         [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("B1"), Range("B" & Rows.Count).End(xlUp))
    ReDim ray(1 To Rng.Count, 1 To 6)
        [COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
            .CompareMode = vbTextCompare
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    Twn = Dn & Dn.Offset(, 1)
    [COLOR="Navy"]If[/COLOR] Not .Exists(Twn) [COLOR="Navy"]Then[/COLOR]
        n = n + 1
        .Add Twn, n
            [COLOR="Navy"]For[/COLOR] Col = 1 To 6
                ray(n, Col) = IIf(UCase(Dn(, Col)) = "Y", Dn.Offset(, -1), Dn(, Col))
            [COLOR="Navy"]Next[/COLOR] Col
    [COLOR="Navy"]Else[/COLOR]
        [COLOR="Navy"]For[/COLOR] Col = 3 To 6
            [COLOR="Navy"]If[/COLOR] UCase(Dn(, Col)) = "Y" [COLOR="Navy"]Then[/COLOR] ray(.Item(Twn), Col) = Dn.Offset(, -1)
         [COLOR="Navy"]Next[/COLOR] Col
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR]
c = .Count
[COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]With[/COLOR] Sheets("sheet2")
    .Range("A1").Resize(c, 6) = ray
    .Columns.AutoFit
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Regards Mick
 

brandnewbie

New Member
Joined
Jul 21, 2012
Messages
6

ADVERTISEMENT

If this is not the solution, please post the expected results as well as the basic data.
Code:
[COLOR=Navy]Sub[/COLOR] MG22Jul34
[COLOR=Navy]Dim[/COLOR] Rng         [COLOR=Navy]As[/COLOR] Range
[COLOR=Navy]Dim[/COLOR] Dn          [COLOR=Navy]As[/COLOR] Range
[COLOR=Navy]Dim[/COLOR] n           [COLOR=Navy]As[/COLOR] [COLOR=Navy]Long[/COLOR]
[COLOR=Navy]Dim[/COLOR] Col         [COLOR=Navy]As[/COLOR] [COLOR=Navy]Integer[/COLOR]
[COLOR=Navy]Dim[/COLOR] c           [COLOR=Navy]As[/COLOR] [COLOR=Navy]Long[/COLOR]
[COLOR=Navy]Dim[/COLOR] Twn         [COLOR=Navy]As[/COLOR] [COLOR=Navy]String[/COLOR]
[COLOR=Navy]Set[/COLOR] Rng = Range(Range("B1"), Range("B" & Rows.Count).End(xlUp))
    ReDim ray(1 To Rng.Count, 1 To 6)
        [COLOR=Navy]With[/COLOR] CreateObject("scripting.dictionary")
            .CompareMode = vbTextCompare
[COLOR=Navy]For[/COLOR] [COLOR=Navy]Each[/COLOR] Dn [COLOR=Navy]In[/COLOR] Rng
    Twn = Dn & Dn.Offset(, 1)
    [COLOR=Navy]If[/COLOR] Not .Exists(Twn) [COLOR=Navy]Then[/COLOR]
        n = n + 1
        .Add Twn, n
            [COLOR=Navy]For[/COLOR] Col = 1 To 6
                ray(n, Col) = IIf(UCase(Dn(, Col)) = "Y", Dn.Offset(, -1), Dn(, Col))
            [COLOR=Navy]Next[/COLOR] Col
    [COLOR=Navy]Else[/COLOR]
        [COLOR=Navy]For[/COLOR] Col = 3 To 6
            [COLOR=Navy]If[/COLOR] UCase(Dn(, Col)) = "Y" [COLOR=Navy]Then[/COLOR] ray(.Item(Twn), Col) = Dn.Offset(, -1)
         [COLOR=Navy]Next[/COLOR] Col
    [COLOR=Navy]End[/COLOR] If
[COLOR=Navy]Next[/COLOR]
c = .Count
[COLOR=Navy]End[/COLOR] With
[COLOR=Navy]With[/COLOR] Sheets("sheet2")
    .Range("A1").Resize(c, 6) = ray
    .Columns.AutoFit
[COLOR=Navy]End[/COLOR] [COLOR=Navy]With[/COLOR]
[COLOR=Navy]End[/COLOR] [COLOR=Navy]Sub[/COLOR]
Regards Mick

This seems to have done the trick!!! Still testing the results but, thank you so much regardless, you've saved my bacon!!
 

brandnewbie

New Member
Joined
Jul 21, 2012
Messages
6
Your welcome
Mick

Hi there,

Further testing (admittedly of my slightly tweaked version of the code to cater for the extra column to be returned and condition to be met?) has showed up some odd results. I simply don't know any VBA so probably best leave it to the pros. I've dumped an extract of the real data here in the hope that captures all eventualities and that someone will be able to help. Sorry to keep this query going on - I probably should have done this from the outset!

So the following source data:

User Name
Entity
Entity Description
Account Description
Level 1
Level 2
Level 3
Level 4
David Hoole
To be confirmed
All
Advertising Promotion
Y
Sara Girard
To be confirmed
All
Advertising Promotion
Y
Y
David Hoole
To be confirmed
All
Advertising Promotion
Y
Mike Florek
To be confirmed
AM US
Advertising Promotion
Y
Michael Voss
To be confirmed
AM US
Advertising Promotion
Y
Gerrard Preston
To be confirmed
All Titles
Commercial Reprints
Y
Andy Douglas
To be confirmed
All Titles
Commercial Reprints
Y
Dean Sanderson
To be confirmed
All Titles
Commercial Reprints
Y
Jessica Rutt
To be confirmed
All Titles
Commercial Reprints – Rightslink
Y
David Hoole
To be confirmed
All Titles
Commercial Reprints – Rightslink
Y
Debashish Brahmachari
To be confirmed
ABC India
Commercial Reprints – ABC
Y
Tony Bocquet
To be confirmed
ABC Asia
Commercial Reprints – ABC
Y
Javier Cazana
To be confirmed
ABC Brazil & ABC Ibero
Commercial Reprints – ABC
Y
David Swinbanks
To be confirmed
All Titles
Commercial Reprints – ABC
Y

<tbody>
</tbody>



Should result in this:
Entity
Entity Description
Account Description
Level 1
Level 2
Level 3
Level 4
To be confirmed
All
Advertising Promotion
Sara Girard
Sara Girard
David Hoole
To be confirmed
AM US
Advertising Promotion
Michael Voss
Mike Florek
To be confirmed
All Titles
Commercial Reprints
Gerrard Preston
Andy Douglas
Dean Sanderson
To be confirmed
All Titles
Commercial Reprints – Rightslink
Jessica Rutt
David Hoole
To be confirmed
ABC India
Commercial Reprints – ABC
Debashish Brahmachari
To be confirmed
ABC Asia
Commercial Reprints – ABC
Tony Bocquet
To be confirmed
ABC Brazil & ABC Ibero
Commercial Reprints – ABC
Javier Cazana
To be confirmed
All Titles
Commercial Reprints – ABC
David Swinbanks

<tbody>
</tbody>


However, having tried to 'amend' the code as follows:

Sub brandnewbie()
Dim Rng As Range
Dim Dn As Range
Dim n As Long
Dim Col As Integer
Dim c As Long
Dim Twn As String
Set Rng = Range(Range("B1"), Range("B" & Rows.Count).End(xlUp))
ReDim ray(1 To Rng.Count, 1 To 7)
With CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
For Each Dn In Rng
Twn = Dn & Dn.Offset(, 1)
If Not .Exists(Twn) Then
n = n + 1
.Add Twn, n
For Col = 1 To 7
ray(n, Col) = IIf(UCase(Dn(, Col)) = "Y", Dn.Offset(, -1), Dn(, Col))
Next Col
Else
For Col = 4 To 7
If UCase(Dn(, Col)) = "Y" Then ray(.Item(Twn), Col) = Dn.Offset(, -1)
Next Col
End If
Next
c = .Count
End With
With Sheets("macro results")
.Range("A1").Resize(c, 7) = ray
.Columns.AutoFit
End With
End Sub


I am getting this:
Entity
Entity Description
Account Description
Level 1
Level 2
Level 3
Level 4
To be confirmed
All
Advertising Promotion
Sara Girard
Sara Girard
David Hoole
To be confirmed
All
Advertising Promotion
David Hoole
To be confirmed
AM US
Advertising Promotion
Michael Voss
Mike Florek
To be confirmed
All Titles
Commercial Reprints
Jessica Rutt
Andy Douglas
David Swinbanks
To be confirmed
ABC India
Commercial Reprints – ABC
Debashish Brahmachari
To be confirmed
ABC Asia
Commercial Reprints – ABC
Tony Bocquet
To be confirmed
ABC Brazil & ABC Ibero
Commercial Reprints – ABC
Javier Cazana

<tbody>
</tbody>


Thanks a lot again for any help anyone can give!!!
 

MickG

MrExcel MVP
Joined
Jan 9, 2008
Messages
14,841
Try this:-
I think I been looking at the wrong Column !!!
Code:
[COLOR="Navy"]Sub[/COLOR] MG25Jul17
[COLOR="Navy"]Dim[/COLOR] Rng         [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Dn          [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] n           [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Col         [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
[COLOR="Navy"]Dim[/COLOR] c           [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Twn         [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("B1"), Range("B" & Rows.count).End(xlUp))
    ReDim ray(1 To Rng.count, 1 To 7)
        [COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
            .CompareMode = vbTextCompare
        [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
            Twn = Dn.Offset(, 1) & Dn.Offset(, 2)
                [COLOR="Navy"]If[/COLOR] Not .Exists(Twn) [COLOR="Navy"]Then[/COLOR]
                    n = n + 1
                .Add Twn, n
                    [COLOR="Navy"]For[/COLOR] Col = 1 To 7
                        ray(n, Col) = IIf(UCase(Dn(, Col)) = "Y", Dn.Offset(, -1), Dn(, Col))
                     [COLOR="Navy"]Next[/COLOR] Col
                [COLOR="Navy"]Else[/COLOR]
                    [COLOR="Navy"]For[/COLOR] Col = 4 To 7
                        [COLOR="Navy"]If[/COLOR] UCase(Dn(, Col)) = "Y" [COLOR="Navy"]Then[/COLOR] ray(.Item(Twn), Col) = Dn.Offset(, -1)
                    [COLOR="Navy"]Next[/COLOR] Col
                [COLOR="Navy"]End[/COLOR] If
        [COLOR="Navy"]Next[/COLOR]
c = .count
[COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]With[/COLOR] Sheets("Macro_Results")
    .Range("A1").Resize(c, 7) = ray
    .Columns.AutoFit
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Regards Mick
 

Watch MrExcel Video

Forum statistics

Threads
1,123,125
Messages
5,599,856
Members
414,342
Latest member
K Darrell Smith

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
Top