Collating data

[Nova1979]

Hello all. Could someone please help. I am trying to pull all column data from multiple sheets (24 I total so far) into 1 master sheet without doubling up data. These are 9 digit numbers and some are on the same sheet multiple time and also across multiple sheets ie on sheet3, sheet4, sheet7 but I need it to show only once on the master sheet

On top of this is it possible to have it numerical? Or might this be too much and just easier to sort?

Thanks in advance.

 

[Nova1979]

Reference Sheet1

Reference Sheet2

Result

With any luck, this might clear things up a little

 

[Nova1979]

You can see there are duplicates in sheet1 and sheet2, however there are aslo duplicates on the same sheet.
(To replicate this, change Sheet1 row3 to 200105 X RING)
you can also see on the master (result) that with column A, this has expanded to columns B and C

 

[DanteAmor]

Ru this macro:

Sub Collating_data()
  Dim sh As Worksheet, dic As Object, ky As Variant
  Dim a As Variant, i As Long
  
  Sheets("Master").Cells.ClearContents
  Set dic = CreateObject("Scripting.Dictionary")
  For Each sh In Sheets(Array("Sheet1", "Sheet2"))
    a = sh.Range("A1:C" & sh.Range("A" & Rows.Count).End(xlUp).Row).Value2
    For i = 1 To UBound(a)
      dic(a(i, 1)) = a(i, 2) & "|" & a(i, 3)
    Next
    Erase a
  Next
  ReDim a(1 To dic.Count, 1 To 2)
  i = 1
  For Each ky In dic.keys
    a(i, 1) = Split(dic(ky), "|")(0)
    a(i, 2) = Split(dic(ky), "|")(1)
    i = i + 1
  Next
  Sheets("Master").Range("A1").Resize(dic.Count).Value = Application.Transpose(dic.keys)
  Sheets("Master").Range("B1").Resize(dic.Count, 2).Value = a
End Sub

_____________________________________
HOW TO INSTALL MACROs
If you are new to macros, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. To use the macro, go back to the worksheet with your data on it and press ALT+F8, select the macro name (Collating_data) from the list that appears and click the Run button. The macro will execute and perform the action(s) you asked for. If you will need to do this again in this same workbook, and if you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "Yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

 

[Nova1979]

No idea on macros. If I adjust data to reference sheet (add, delete or change) will the changes occur in the master sheet automatically like a formula?

 

[DanteAmor]

No idea on macros. If I adjust data to reference sheet (add, delete or change) will the changes occur in the master sheet automatically like a formula?

Above I put the explanation of how to use a macro.
No, after making changes you must run the macro.
It can be adapted to update automatically.

If you require a formula, hopefully someone on the board will help us find the solution.

 

[Nova1979]

I will try the macro

Thank you very much for you time and help. It is very much appreciated

 

[DanteAmor]

You are welcome & thanks for the feedback

 

[sandy666]

try Power Query (add-in from MS site for XL 2010)

let
    Source = Table.Combine({Table1, Table2}),
    RemDuplicates = Table.Distinct(Source),
    Sort = Table.Sort(RemDuplicates,{{"Column1", Order.Ascending}})
in
    Sort

Column1	Column2	Column3
200067	X	RING
200105	X	RING
200203	X	RING
599265	X	HEXAGON
742072	X	ELECTRICAL
1338696	X	ELECTRICAL
1651978	X	RING
6197158	X	COTTER
10165674	X	FITTING
10744980	X	HEXAGON
13794226	X	COTTER

 

[Nova1979]

Hi sandy. Thank you for your suggestion. Due to the system I am using and the security restrictions in place, I can not get access to add-ins. I had a gentleman help earlier and he gave me a macro.
I have spoken to the others utilising the workbook, and I they would idealy like a formula , where people do need to remeber to run the macro.

 

[sandy666]

security restrictions in place

you can use vba but not PQ add-in??? very weird
maybe update Office to 2016 version where PQ is built-in

anyway, @DanteAmor said:

It can be adapted to update automatically.