Zsofia Rozsa
asked on
MS Excel for Mac v16.47.1 |. Merge two sheets with all fields into one based on a matched field
I have two tables, "RxA" (270 rows of data in 3 fields) and "AWS" (408 rows of data with 7 fields). I would like to merge the two tables into 1 table with all 10 fields from both tables based on matching values in field A within both tables.
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
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.
SELECT * FROM AWSsheet |
LEFT JOIN RXAsheet; |
ON AWSsheet.AWS04 = RXAsheet.RXA01 |
SELECT * FROM AWSsheet |
LEFT JOIN RXAsheet |
ON AWSsheet.AWS04 = RXAsheet.RXA01 |
WHERE Rxa01 IS NULL; |
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
This question needs an answer!
Become an EE member today
7 DAY FREE TRIALMembers can start a 7-Day Free trial then enjoy unlimited access to the platform.
View membership options
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Mac Excel doesn't have that capability at the moment. As a workaround, I suggest the following:
Assuming that RxA is on Sheet1 and AWS on Sheet2, the COUNTIF and VLOOKUP formulas would be something like:
Open in new window