Combine two varying size data sets in to one in Excel-CodePudding

I have two data sets that are populated from two different tables which can vary in size as those tables change, and I'd like to combine them in to one set of data. I'm completely stumped on how to go about doing this, any ideas?

Please see example of data set 1 and 2, and how I'd like it to look in the right side data in the image :

The data used is as follows:

Data Set 1          Data Set 2          Data Set 1 & 2  
Employee    Start Date      Employee    Start Date      Employee    Start Date
Julie   01/12/2019      Lee 01/12/2019      Julie   01/12/2019
Daisy   01/12/2019      Anna    01/12/2019      Daisy   01/12/2019
Laura   01/12/2019      David   01/12/2019      Laura   01/12/2019
            Sam 01/12/2019      Lee 01/12/2019
            George  01/12/2019      Anna    01/12/2019
                        David   01/12/2019
                        Sam 01/12/2019
                        George  01/12/2019

CodePudding user response：

If you are on O365 then use VSTACK() formula.

=VSTACK(A3:B5,D3:E7)

If you data increase continuously then could try-

=VSTACK(FILTER(A3:B50000,A3:A50000<>""),FILTER(D3:E50000,D3:D50000<>""))

Edit: If you do not have VSTACK() then go with TEXTJOIN() and FILTERXML() like-

=LET(x,FILTERXML("<t><s>"&TEXTJOIN("</s><s>",TRUE,A3:B20,D3:E20)&"</s></t>","//s"),INDEX(x,SEQUENCE(COUNTA(x)/2,2)))

CodePudding user response：

Here are few more alternatives using VSTACK() & UNIQUE()

• Formula used in cell G2

=UNIQUE(VSTACK(A2:B5,D2:E7))

Or,

• Formula used in cell J2

=LET(rngOne,A2:B5,
rngTwo,D2:E7,
r,SEQUENCE(ROWS(rngOne) ROWS(rngTwo)),
c,SEQUENCE(,COLUMNS(rngOne)),
output,IFERROR(INDEX(rngOne,r,c),INDEX(rngTwo,r-ROWS(rngOne),c)),
UNIQUE(output))

Edit: We can also accomplish this task using Power Query quite efficiently!

Follow the steps:

Convert the ranges into a table, select any cell on the range and press CTRL T, this will ask you check my table as headers,
Rename the table as DataSetOne, do the same for the second range and rename it as DataSetTwo
Goto Data Tab, From Get & Transform, click on Get Data, from Other Sources and click on Blank Query,
This opens the PQ editor,
Place the below M-Code in Advance Editor which you will find in Home Tab, (remove the M-code which shows for the blank query),

let
    SourceOne = Excel.CurrentWorkbook(){[Name="DataSetOne"]}[Content],
    DataTypeOne = Table.TransformColumnTypes(SourceOne,{{"Employee", type text}, {"Start Date", type date}}),
    SourceTwo = Excel.CurrentWorkbook(){[Name="DataSetTwo"]}[Content],
    DataTypeTwo = Table.TransformColumnTypes(SourceTwo,{{"Employee", type text}, {"Start Date", type date}}),
    Append = Table.Combine({DataTypeOne, DataTypeTwo})
in
    Append

Output:

CodePudding user response：

If all workers are unique (each worker appears just once and there is no common workers between datasets) you can do it easily with consolidate function. You can consolidate 2 or more datasets as long as all of them share headers: