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]

Hi & welcome to the board.
Try this

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("B2", Ws.Range("B" & 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("A2", 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]

Hi Fluff,

Thanks for the code. Its not quite what I was looking for though - this copy's the T/O column on each sheet and transfers them all into column A of the master sheet. What am I trying to do is to take each year and combine them into one sheet with a row per customer, and turnover for each year in a separate column. The top customers can fluctuate between years.

For example in 2010 the top 5 customers were J Smith; £200,000 , J Green - £35,000, J Brown - £34,000 J Red - £20,000 & J Black - £19,500, but in 2011 the top 5 were J Green - £600,000, J Black - £450,000, J Blue - £35,000, J Smith - £30,000 and J Pink - £19,775 and so on. What I would like to do is collate the data from each sheet then into the master thus:

[TABLE="width: 584"]
<colgroup><col><col span="2"><col span="5"></colgroup><tbody>[TR]
[TD][/TD]
[TD="colspan: 7"]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]
[TR]
[TD] J Smith [/TD]
[TD] £ 200,000.00[/TD]
[TD] £ 30,000.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD] J Green [/TD]
[TD] £ 35,000.00[/TD]
[TD] £ 600,000.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD] J Brown [/TD]
[TD] £ 34,000.00[/TD]
[TD] £ - [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD] J Red [/TD]
[TD] £ 20,000.00[/TD]
[TD] £ - [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD] J Black [/TD]
[TD] £ 19,500.00[/TD]
[TD] £ 450,000.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD] J Blue [/TD]
[TD] £ - [/TD]
[TD] £ 35,000.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD] J Pink [/TD]
[TD] £ - [/TD]
[TD] £ 19,775.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Does this make sense? I'm trying to get column A to list all of the top customers once only and then columns B-H to list their respective T/O for each year.

 

[Fluff]

In your op you said that the sheet names were called Top Customers 2009-10 etc, is this correct?

 

[Brick Transport]

yes, in total there are 7: Top Customers 2009-10, 2010-11, 2011-12, 2012-13, 2013-14, 2014-15 & 2015-16, and then Master.

 

[Brick Transport]

I should point out that I don't necessarily need Master to be in any particular order as I can filter & sort once all the data has been transferred

 

[Fluff]

Try adding a msgbox like this

            Col = Right(Ws.Name, 2) - 8
            MsgBox Col
            NxtRw = MstSht.Range("A" & Rows.Count).End(xlUp).Offset(1).Row

& step through the code using F8. does the msgbox show 2, then 3 etc?

 

[Brick Transport]

OK, yes it does, but it still only transfers the T/O into Column A....

 

[Fluff]

On the individual sheets do you have a header in row 1 with data starting in row 2?
Also do you have any merged cells in any sheet?

 

[Brick Transport]

Hi, no there's no merged cells in any of the sheets. In the Top Customers sheets row 1 is a header: A1: Customer Name; A2: T/O and in Master rows 1 & 2 are headers:

[TABLE="width: 584"]
<tbody>[TR]
[TD][/TD]
[TD]Turnover[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/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]