No Join Kind dropdown in Merge dialog

xlWatcher

New Member
Joined
Feb 20, 2020
Messages
6
Office Version
2016
Platform
Windows
Using Exel 2016 and Windows 10
I am trying to merge two sheets with a common column which contains text. The two sheets have different columns of information. There are no duplicates of the common column in either sheet.
When I get the data from file from workbook, for the two sheets, the Merge dialog does NOT have the Join Kind dropdown. It only has a checkbox to “Only include matching rows”. So I am not able to specify that an outer join is needed. I left the box unchecked to get all rows, but I’m only getting the rows common to BOTH sheets. I if there are rows in the second file that aren’t in the first, they are lost, but there is no Join Kind option to specify all rows of each sheet. Also the column labels of one of the files changed to column1, column2 etc. when vied in Power Query, and I don’t know why. However, when I change data in the source file, the new file does refresh.
Is it a requirement when doing this that one sheet be a subset of the other or can each sheet have items that the other doesn’t have.? How can I specify the Join Kind?
Thank you for any help!
 

Some videos you may like

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
4,658
I am not sure is that what you want but maybe

merge.png

 
Last edited:

xlWatcher

New Member
Joined
Feb 20, 2020
Messages
6
Office Version
2016
Platform
Windows
Thank you so much for such a fast reply. But my problem is that i don't HAVE the Hoin Kind dropdown so that I could specify a full join to get all records from both sheets. What am I missing that I only have a checkbox that says "Only include matching rows" which I leave unchecked.
 

xlWatcher

New Member
Joined
Feb 20, 2020
Messages
6
Office Version
2016
Platform
Windows
This is the Merge dialog. And as you can see, the column labels of david1 are messed up also; they became column1, column2... did I get an incomplete version of Excel 2016???
Thank you!
1582573011511.png
1582573011511.png
 

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
4,658
first: Promote headers in david1 table then try merge again
second check your Office for update
version.png


merge.png
 
Last edited:

xlWatcher

New Member
Joined
Feb 20, 2020
Messages
6
Office Version
2016
Platform
Windows
I think this is the problem: I don't have a link for updates??? I will try entering my product ID code and try to dig through Microsoft to find out how to fix that. Thanks to you for making me aware of that. I wonder, though, if the pre-updated 2016 lacked the Join Kind dropdown because if it did, how did sheets get ,merged??
Yes, I do promote headers in the Power Query screen., so that part does get fixed. I don't know why it's like that in the first place.
thank you for your time. I'll see if I can get updates & if that changes things.
 

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
4,658
check Windows update, there should be Office update also
if the pre-updated 2016 lacked the Join Kind dropdown because if it did, how did sheets get ,merged??
maybe on another computer, who knows :)
post a link to the shared excel file with your problem and we will see
 

xlWatcher

New Member
Joined
Feb 20, 2020
Messages
6
Office Version
2016
Platform
Windows
I'm sorry...I'm not sure how to do that :(
thank you
 

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
4,658
use google drive, one drive or any similar
share file
then post link to this file here
 

Forum statistics

Threads
1,089,175
Messages
5,406,617
Members
403,099
Latest member
Sialoquent

This Week's Hot Topics

Top