CODEWITHSUNDEEP
excelexcel-formula
Automation Monkey
Automation Monkey

Reputation: 365

Combine two varying size data sets in to one in Excel

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 :

Two Data Sets

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

Upvotes: 1

Views: 836

Answers (3)

Mayukh Bhattacharya
Mayukh Bhattacharya

Reputation: 27373

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

FORMULA_SOLUTION

• 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!

enter image description here

Follow the steps:

  1. 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,

  2. Rename the table as DataSetOne, do the same for the second range and rename it as DataSetTwo

  3. Goto Data Tab, From Get & Transform, click on Get Data, from Other Sources and click on Blank Query,

  4. This opens the PQ editor,

  5. 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:

enter image description here

Upvotes: 1

Harun24hr
Harun24hr

Reputation: 36945

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)))

enter image description here

Upvotes: 3

Foxfire And Burns And Burns
Foxfire And Burns And Burns

Reputation: 11988

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:

Consolidate data in multiple worksheets

Notice consolidate is designed to resume data and get a numeric result, but because dates are numbers in Excel, you may use it for this:

enter image description here

Upvotes: 0

Related Questions