No Join Kind dropdown in Merge dialog

xlWatcher

New Member
Joined
Feb 20, 2020
Messages
6
Office Version
  1. 2016
Platform
  1. 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!
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
I am not sure is that what you want but maybe

merge.png

 
Last edited:
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
first: Promote headers in david1 table then try merge again
second check your Office for update
version.png


merge.png
 
Last edited:
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
use google drive, one drive or any similar
share file
then post link to this file here
 
Upvote 0

Forum statistics

Threads
1,212,933
Messages
6,110,751
Members
448,295
Latest member
Uzair Tahir Khan

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top