Generate a new dataset from two existings datasets with conditions-CodePudding

I have two dataset with the same columns, and I would like to create a new one in another sheet with all rows from the first dataset and add to it specific rows from the second one.

My first dataset is like:

| Item Type | Item Numb | Start Date | End date   |
---------------------------------------------------
| 1         | 1         | 17/02/2022 | 21/02/2022 |
| 1         | 2         | 19/02/2022 | 24/02/2022 |
| 2         | 1         | 15/02/2022 | 18/02/2022 |
| 2         | 2         | 17/02/2022 | 20/02/2022 |
| 3         | 1         | 21/02/2022 | 25/02/2022 |

And the second one is like:

| Item Type | Item Numb | Start Date | End date   |
---------------------------------------------------
| 1         | 2         | 17/02/2022 | 20/02/2022 |
| 2         | 2         | 17/02/2022 | 20/02/2022 |
| 2         | 3         | 20/02/2022 | 23/02/2022 |
| 3         | 1         | 20/02/2022 | 23/02/2022 |
| 4         | 1         | 21/02/2022 | 24/02/2022 |
| 4         | 2         | 23/02/2022 | 28/02/2022 |

So now, I would like in a new sheet to retrieve the rows from the first dataset and add at the end the rows from the second one who are absent. If a Combination of "Item Type" and "Item Numb" is already imported I don't want to get them from the second dataset, but if this specific combination isn't in the first one so I would like to add the row.

That's what I need as the result:

| Item Type | Item Numb | Start Date | End date   |
---------------------------------------------------
| 1         | 1         | 17/02/2022 | 21/02/2022 |
| 1         | 2         | 19/02/2022 | 24/02/2022 |
| 2         | 1         | 15/02/2022 | 18/02/2022 |
| 2         | 2         | 17/02/2022 | 20/02/2022 |
| 3         | 1         | 21/02/2022 | 25/02/2022 |
| 2         | 3         | 20/02/2022 | 23/02/2022 |
| 4         | 1         | 21/02/2022 | 24/02/2022 |
| 4         | 2         | 23/02/2022 | 28/02/2022 |

Thanks in advance for your time folks!

CodePudding user response：

try:

=INDEX(ARRAY_CONSTRAIN(QUERY(SORTN(
 {Sheet1!A2:D, Sheet1!A2:A&Sheet1!B2:B; 
  Sheet2!A2:D, Sheet2!A2:A&Sheet2!B2:B}, 9^9, 2, 5, 1), 
 "where Col1 is not null", 0), 9^9, 4)