MS Excel for Mac v16.47.1 |. Merge two sheets with all fields into one based on a matched field

In Windows Excel, I would suggest using the Power Query tools in the Get & Transform group of the Data ribbon to append one table to the other.

Mac Excel doesn't have that capability at the moment. As a workaround, I suggest the following:

• Use a COUNTIF formula on RxA to identify those rows that have matching values in Field A compared to AWS. If COUNTIF returns 0, you should just copy those rows directly over to AWS. 
• For the remaining rows, use a VLOOKUP formula to bring values from RxA over to AWS

Assuming that RxA is on Sheet1 and AWS on Sheet2, the COUNTIF and VLOOKUP formulas would be something like:

=COUNTIF(A2,Sheet2!A:A)  'Formula added to RxA
=IFERROR(VLOOKUP($A2,Sheet1!$A$2:$D$271,COLUMNS($G2:G2)+1,FALSE),"")  'Formula added to AWS

Select allOpen in new window

This Pivot table works: ODBCFILE03.xlsx


edit:sadly this below does NOT work.

Mac does have MS Query if that helps; but ODBC is not included, driver required, I use this one http://www.actualtech.com/download.php It's not free except to evaluate. No such hassle on Windows Excel.

Data GetExternal From Database accesses MS Query (which looks to me like MS want us to use power query.. and that looks like SQL has vanished. edit it hasn't but the external programme MS Query I can no longer find so I think that has gone, so it seems they are leaving SQL available under the hood to maintain backwards compatibilty.

ODBCFILE.xlsx

Edit: the above all updated. It requires a workaroundbecause the commercial ODBC driver does not support FULL JOINS.

Here's my syntax that too a fair old while to generate (me not an expert, so it was fun)
I've assumed the data does not match as that's more difficult.
 

I think this may be viewed as archaic tech, but it's still present on my Mac, witness the above that took me several hours to set up and a few minutes to do.

Anthony