Web Applications Stack Exchange is a question and answer site for power users of web applications. It only takes a minute to sign up.
Sign up to join this community
How to combine and sort two sets of data not always overlapping? see example below. Thx
edit: Example in google sheet
https://docs.google.com/spreadsheets/d/15Vp_G_GX5fqD-OBXBv--TQDGv8TWzdlCdqHy15UEFvY/edit#gid=0
1st set of data
| A header | B header |
|---|---|
| A | Yes |
| C | No |
| D | Yes |
| F | No |
2nd set of data
| A header | C header |
|---|---|
| A | red |
| B | blue |
| D | yellow |
| F | Re |
Expected result
| A header | B header | C Header |
|---|---|---|
| A | Yes | Red |
| B | Blue | |
| C | No | |
| D | yes | Yellow |
Thanks
I've added a new sheet ("Erik Help") with the following formula solution:
=ArrayFormula({'Data 1'!A1:C1,'Data 2'!B1:C1;{TEXT(DATE(YEAR(TODAY()),SEQUENCE(12),1),"mmm"),IFERROR(VLOOKUP(TEXT(DATE(YEAR(TODAY()),SEQUENCE(12),1),"mmm"),'Data 1'!A:C,{2,3},FALSE),{"",""}),IFERROR(VLOOKUP(TEXT(DATE(YEAR(TODAY()),SEQUENCE(12),1),"mmm"),'Data 2'!A:C,{2,3},FALSE),{"",""})}})
Explaining this fully will not be possible here. But I'll provide an overview.
Anything set between curly brackets { } forms a virtual array (i.e., a custom grouping of data).
First, the headers are pulled from the two Data sheets.
The semicolon means "move down to the next row and continue."
This section TEXT(DATE(YEAR(TODAY()),SEQUENCE(12),1),"mmm") (which appears three times) forms a three-letter listing of all months, not just the ones that happen to be currently in one of the Data sheets.
That month-list is places in the first column. In the second and third columns, that listing is looked up in Data 1 and any matching results are returned from columns 2 and 3 (i.e., {2, 3}). The same is then repeated for the Data 2 sheet.
ADDENDUM (after new comment and changed data from OP)
The above solution was built around the original sample data keying off of clear month names.
As it turns out, the actual usage does not have months as the key data but rather names (or some other random ID).
Here is the above formula rewritten around two new sheets named Data A and Data B:
=ArrayFormula({'Data A'!A1:C1,'Data B'!B1:C1;SORT({UNIQUE({FILTER('Data A'!A2:A,'Data A'!A2:A<>"");FILTER('Data B'!A2:A,'Data B'!A2:A<>"")}),IFERROR(VLOOKUP(UNIQUE({FILTER('Data A'!A2:A,'Data A'!A2:A<>"");FILTER('Data B'!A2:A,'Data B'!A2:A<>"")}),'Data 1'!A:C,{2,3},FALSE),{"",""}),IFERROR(VLOOKUP(UNIQUE({FILTER('Data A'!A2:A,'Data A'!A2:A<>"");FILTER('Data B'!A2:A,'Data B'!A2:A<>"")}),'Data 2'!A:C,{2,3},FALSE),{"",""})})})
