Combining Multiple Sheets without duplicates and organising data into unique columns

[Brick Transport]

Hello, I have turned to MrExcel numerous times in the past for solutions to various problems I've encountered and found the help to be excellent! Well, now I have something that I am struggling to find an adequate solution to. I hope that someone can help me out.

So, I have 7 worksheets of "Top Customers" for the last 7 financial years. I want to combine these into one sheet, without duplicating the customers and arrange the t/o values into individual columns relating to the particular financial year:

[TABLE="class: grid, width: 523"]
<tbody>[TR]
[TD][/TD]
[TD="colspan: 7, align: center"]Turnover[/TD]
[/TR]
[TR]
[TD]CUSTOMER [/TD]
[TD]2010[/TD]
[TD]2011[/TD]
[TD]2012[/TD]
[TD]2013[/TD]
[TD]2014[/TD]
[TD]2015[/TD]
[TD]2016[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 523"]
<tbody>[TR]
[/TR]
[TR]
[TD="align: right"][/TD]
[/TR]
</tbody>[/TABLE]
The data is stored in each separate sheet with "customer name" in Column B & "turnover" in Column C, however the number of customers in each sheet varies. The Sheets are called Top Customers 2009-10, Top Customers 2010-11 etc etc, for example:

[TABLE="class: grid, width: 378"]
<tbody>[TR]
[TD]Customer[/TD]
[TD] T/O[/TD]
[/TR]
[TR]
[TD]J.Smith Ltd[/TD]
[TD] £188,341.73[/TD]
[/TR]
[TR]
[TD]J. Brown Ltd[/TD]
[TD] £61,972.91[/TD]
[/TR]
[TR]
[TD]J. Green Ltd[/TD]
[TD] £59,364.78[/TD]
[/TR]
[TR]
[TD]J. Red Ltd[/TD]
[TD] £30,572.22[/TD]
[/TR]
[TR]
[TD]J. Black Ltd[/TD]
[TD] £24,600.02[/TD]
[/TR]
[TR]
[TD]J. Jones Ltd[/TD]
[TD] £22,273.99[/TD]
[/TR]
</tbody>[/TABLE]

and so on.

How do I then take Column B from each sheet and combine them all into Column B of the master, avoiding duplicates and then take the corresponding t/o value from Column B in each sheet and transfer that into the relevant year column for the corresponding customer on the master sheet?

I'm not even sure if this is possible to do or not, but I've tried a few of the solutions posted here about combining columns etc but none seem to work with it being fairly complex.

Any help would be greatly appreciated as this is driving me bonkers trying to figure out!!! Please let me know if further information or a sample of the data would help and I can arrange that.

Huge thanks in advance,

 

[Fluff]

A1: Customer Name; A2: T/O

This is a bit confusing, Is the A2 a typo & meant to be B1? Also, in your OP you stated

The data is stored in each separate sheet with "customer name" in Column B & "turnover" in Column C

Could you lease confirm which is correct.

 

[Brick Transport]

Sorry!! My mistake - well spotted.... that should have read columns A & B.

To clarify - in each Top Customers sheet Row 1 is a header, Column A is titled Customer Name, Column B is titled T/O, data starts in row 2.

In the Master sheet, rows 1 & 2 currently are headers (these can be altered to make the process work if needs be), Col A Row 1 is blank, Col B Row 1 is titled Turnover, Cols C-H are blank (to allow for Row 1 B-H to be merged at a later date). Col A Row 2 is titled Customer, Cols B-H are 2010, 2011, 2012, 2013, 2014, 2015, 2016. If it makes things easier I can remove Row 1 from Master so that the current Row 2 becomes the header Row 1?

Hopefully this helps?

 

[Fluff]

Ok, give this a go

Sub CopyData()

    Dim ws As Worksheet
    Dim MstSht As Worksheet
    Dim NxtRw As Long
    Dim Col As Long
    Dim Rng As Range
    Dim Cl As Range
    
    Set MstSht = Sheets("Master")
    For Each ws In Worksheets
        If Not ws.Name = "Master" Then
            Col = Right(ws.Name, 2) - 8
            NxtRw = MstSht.Range("A" & Rows.Count).End(xlUp).Offset(1).Row
            With ws.Range("A2", ws.Range("A" & Rows.Count).End(xlUp))
                .copy MstSht.Range("A" & NxtRw)
                .Offset(, 1).copy MstSht.Cells(NxtRw, Col)
            End With
        End If
    Next ws
    With CreateObject("scripting.dictionary")
        For Each Cl In MstSht.Range("A3", MstSht.Range("A" & Rows.Count).End(xlUp))
            If Not .exists(Cl.Value) Then
                .Add Cl.Value, Cl.Row
            Else
                Cl.End(xlToRight).copy MstSht.Cells(.Item(Cl.Value), Cl.End(xlToRight).Column)
                If Rng Is Nothing Then
                    Set Rng = Cl
                Else
                    Set Rng = Union(Rng, Cl)
                End If
            End If
        Next Cl
    End With
    Rng.EntireRow.Delete
            
End Sub

 

[Brick Transport]

You are a legend!! Thank you so much this is exactly what I needed, works perfectly!! Awesome.

 

[Fluff]

Glad to help & thanks for the feedback

 

[Brick Transport]

Thanks for your help - I'd still be there next week copying, pasting and sorting & you're welcome! One more question though - if add additional Top Customer sheets in the future will this continue working?

 

[Fluff]

Yes, it should do. The code loops through all the worksheets & determines which column to copy the T/O to by the last 2 digits in the sheet name.

Although it will encounter a problem come 2110

 

[Brick Transport]

haha I won't be around then so that's someone else's problem ... Thank you again

 

[Fluff]

Your welcome