Join 2 sheets into one

virtuosok

Board Regular
Joined
Sep 2, 2020
Messages
75
Office Version
  1. 2016
Platform
  1. Windows
Hi, I have 2 tabs within the same Excel workbook. Both tabs have exactly the same structure (i.e. number of columns and their headers), while the data itself is slightly different. These two tabs are getting updated independently on an ongoing basis.
Is there a way to generate a formula which would be combining the output from both sheets onto a new sheet in the existing workbook? As a one-off solution, copy/paste works just as well but it's not sustainable long term :)
My ranges are columns A-V and rows 12-50000 on both tabs (currently either tab has about 300 entries but this is growing fast). A related concern is to try and avoid empty rows in the middle of a new worksheet when joining 2 sheets... any advice appreciated!
 

Some videos you may like

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

sandy666

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,499
try Power Query (Get&Transform)
change your source ranges to Excel tables (Ctrl+T) then load both to the Power Query Editor then use Append feature (Table.Combine) and load result to the third sheet
 

virtuosok

Board Regular
Joined
Sep 2, 2020
Messages
75
Office Version
  1. 2016
Platform
  1. Windows
Thanks, this works, but is there a way to have third sheet auto-update whenever either of the first two sheets are changed?
 

sandy666

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,499
you can try with this in sheets module
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim con As WorkbookConnection
Dim Cname As String

For Each con In ActiveWorkbook.Connections
    If Left(con.Name, 8) = "Query - " Then
    Cname = con.Name
        With ActiveWorkbook.Connections(Cname).OLEDBConnection
            .BackgroundQuery = False  'or true, up to you
            .Refresh
        End With
    End If
Next
End Sub
or define refresh time in Connections - Properties - Refresh every (set how often you want to refresh in minutes)
 

virtuosok

Board Regular
Joined
Sep 2, 2020
Messages
75
Office Version
  1. 2016
Platform
  1. Windows
Apologies one more question. I can't seem to be able to query worksheets from already open workbook, this defaults to the workbook stored on the computer. What am I doing wrong?
Untitled.png
 

sandy666

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,499
I don't understand
You have two tables (Excel Tables) so use From Table for each source and it will be loaded into Power Query Editor then Append
I have 2 tabs within the same Excel workbook. Both tabs have exactly the same structure
Untitled.png


if you want to use From Workbook, your working file must be saved first
 

Watch MrExcel Video

Forum statistics

Threads
1,122,600
Messages
5,597,114
Members
414,125
Latest member
iQQ

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