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

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
5,005
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
5,005
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
5,005
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
5,005
use google drive, one drive or any similar
share file
then post link to this file here
 

Watch MrExcel Video

Forum statistics

Threads
1,095,220
Messages
5,443,165
Members
405,219
Latest member
CraneS

This Week's Hot Topics

  • Copy entire row if CountA <>0 to another sheet
    [B]I want to copy entire row if CountA <>0 for column J7:AM7 (headers on J6:AM6) and so on till the last used cell is column D and paste the...
  • Select last used Row in Table
    I have created a Table in a Worksheet which is locked to prevent user errors and protect formula. Some of the cells require freetext entries which...
  • excel workbook: do not allow certain file name
    Hello all, Don't think this has ever been asked before, but how do I restrict file save [Before_Save Event] if the name of the file being saved...
  • fixing problem autofilter
    hello i need help about my code when i search by code in textbox it doesn't show anything this is my data [ATTACH type="full"...
  • “Weight”
    Hi, i’ve got a long sheet filled with weights such as kg,g,L & ml. i can build a formula to convert kg into g and liter into ml. How ever, my...
  • How to capitalize everything before a certain character?
    In column A, I have some text: Hello good day.mp3 Hello good day.flac etc. I'd like to capitalize everything before the period. I don't need the...
Top