Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 02-14-2020, 04:21 PM
lynn0712 lynn0712 is offline How to merge 2 Excel tables? Windows 7 64bit How to merge 2 Excel tables? Office 2016
Novice
How to merge 2 Excel tables?
 
Join Date: Dec 2017
Posts: 14
lynn0712 is on a distinguished road
Default How to merge 2 Excel tables?

I have 2 tables on one Excel workbook: _CustomerList and _SalesList. I created a one to many relationship between them based on a column called, Customer. How can I merge the 2 tables, with the end result being a worksheet that has columns from both tables. Examples for the tables are in the attached spreadsheet.

Example Tables.xlsx
Reply With Quote
  #2  
Old 02-15-2020, 04:23 PM
p45cal p45cal is offline How to merge 2 Excel tables? Windows 10 How to merge 2 Excel tables? Office 2019
Expert
 
Join Date: Apr 2014
Posts: 360
p45cal has a spectacular aura aboutp45cal has a spectacular aura aboutp45cal has a spectacular aura about
Default

Quote:
Originally Posted by lynn0712 View Post
I created a one to many relationship between them based on a column called, Customer.
How did you do this?


You have Power Query/Get & Transform in your version of Excel?


In the attached, two queries to put the tables into queries & connections, and one to merge. M Code for the merge could be:
Code:
let
    Source = Table.NestedJoin(Table1, {"Customer"}, Table2, {"Customer"}, "Table2", JoinKind.LeftOuter),
    #"Expanded Table2" = Table.ExpandTableColumn(Source, "Table2", {"Description", "Amount"}, {"Description", "Amount"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Table2",{{"Amount", Currency.Type}})
in
     #"Changed Type"
I've put the resultant table to the right of your desired result table. If the data changes in either of the source tables you just need to refresh the green table with a right-click and choose Refresh.
Attached Files
File Type: xlsx msofficeforums44409Example Tables.xlsx (19.9 KB, 2 views)
Reply With Quote
  #3  
Old 02-16-2020, 02:42 PM
lynn0712 lynn0712 is offline How to merge 2 Excel tables? Windows 7 64bit How to merge 2 Excel tables? Office 2016
Novice
How to merge 2 Excel tables?
 
Join Date: Dec 2017
Posts: 14
lynn0712 is on a distinguished road
Default

Thank you!
Reply With Quote
Reply

Tags
merge tables

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
How to merge 2 Excel tables? Nesting Tables in a Merge Document TeresaRyanKS Mail Merge 2 02-04-2020 12:09 PM
How to merge 2 Excel tables? Populating Tables with data when available only - Using Mail merge stevejohhnym Mail Merge 1 06-25-2019 12:50 AM
How to merge 2 Excel tables? Mail Merge does not split tables if entry has a number in front B-Man Mail Merge 5 05-19-2019 07:25 AM
How to Merge Two tables in excel MShahid777 Excel 1 09-02-2018 02:43 PM
mail merge from access query or two tables peter Mail Merge 0 08-24-2010 05:32 PM


All times are GMT -7. The time now is 05:10 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2020, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2020 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft