0

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

2
  • Please share a link to a sample sheet containing the above data and indicate where the formula results should go. As it stands, the volunteer contributors here do not know your layout or sheet names; and they would need to start their own spreadsheet and enter your data themselves, guessing where it might go, before they could even start developing a solution.
    – Erik Tyler
    Jan 13, 2022 at 15:28
  • Please see a link with two sheets to merge and the expected results in a 3rd one docs.google.com/spreadsheets/d/… thx Jan 13, 2022 at 18:12

1 Answer 1

0

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),{"",""})})})

4
  • Thanks! You outsmarted my example - I took "Month" as an example but I am after sorting random IDs. I have updated the spreadsheet with a slightly modified example. docs.google.com/spreadsheets/d/… Jan 14, 2022 at 8:07
  • See my newly added sheet ("Erik Help 2"). I renamed your added sheets for my ease in rewriting to your new example data. Do keep in mind for future reference on forums that we don't know anything but what you show us, and every detail matters to how a solution is approached. I've added the new formula to an ADDENDUM section in my posted reply as well.
    – Erik Tyler
    Jan 14, 2022 at 8:49
  • Thank you, Erik! I have been able to modify your formula so it fit my needs. Jan 15, 2022 at 6:41
  • Please take a moment to mark my post containing the solution as "Best Answer." This essentially "closes" the post; and it allows future site visitors to most quickly find the answer if it is relevant to their own needs.
    – Erik Tyler
    Jan 15, 2022 at 8:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.