Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 08-24-2020, 02:52 PM
Ricko_uk Ricko_uk is offline Merging all data between two sheets into a third one without duplicates or rows or columns Windows 10 Merging all data between two sheets into a third one without duplicates or rows or columns Office 2019
Novice
Merging all data between two sheets into a third one without duplicates or rows or columns
 
Join Date: Aug 2020
Posts: 10
Ricko_uk is on a distinguished road
Default Merging all data between two sheets into a third one without duplicates or rows or columns

Hi,


this must be an extremely common problem that people solved daily but could not find any solutions.

I have two sets of classic product inventory data, one in a table on Sheet 1 and one in a table on Sheet 2. They come from two different computer systems and need to merge them into a single table on Sheet 3 so that ALL data (both columns and items) are present in Sheet 3 BUT only once (i.e. no duplicates of either columns or entries/items).

- some columns are present in both Sheet 1 and 2, some only on Sheet 1 and some only on Sheet 2
- same for the rows, some (most) items (which have their unique referencing IDs) are present in both Sheet 1 and 2, some only on Sheet 1 and some only on Sheet 2

The only values that can be reliably used to correlated sheet 1 and 2 which to create sheet 3 are:
- Unique IDs for products what appear in both tables
- Columns names/titles are always fixed for those columns that appear in both tables

How do can I do it?

Thank you 😊

P.S. I posted something similar here but was not what I was solving the problem completely: Creating third sheet with data from first and second sheets by matching a unique ID
Reply With Quote
  #2  
Old 08-24-2020, 10:22 PM
Purfleet Purfleet is offline Merging all data between two sheets into a third one without duplicates or rows or columns Windows 10 Merging all data between two sheets into a third one without duplicates or rows or columns Office 2019
Expert
 
Join Date: Jun 2020
Location: Essex
Posts: 345
Purfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to behold
Default

you could do a index and match (vlookup) fairly easy which is probably how most people have done it for the last 20 years, but it can slow down a work book and you would need to do it again each time - this can be automated with VBA but takes some development time.

If the main goal is just merging the 2 tables i would look at power query - i have not spent that much time using it but for table merges it looks ideal.

You can also append data to the tables and refresh - not sure how you automate the joining new data extracts with power query but i am sure its possible you can even import directly from a folder.

Have a look at this blog post

https://www.myonlinetraininghub.com/...e-excel-sheets
Attached Files
File Type: xlsx Third sheet_Purfleet.xlsx (21.9 KB, 6 views)

Last edited by Purfleet; 08-24-2020 at 10:23 PM. Reason: typo
Reply With Quote
  #3  
Old 08-25-2020, 11:55 AM
Ricko_uk Ricko_uk is offline Merging all data between two sheets into a third one without duplicates or rows or columns Windows 10 Merging all data between two sheets into a third one without duplicates or rows or columns Office 2019
Novice
Merging all data between two sheets into a third one without duplicates or rows or columns
 
Join Date: Aug 2020
Posts: 10
Ricko_uk is on a distinguished road
Default

Thank you Purflee

what I need is a solution where (because of the large amount of times data is pasted in) the ONLY TWO operations I would do are 1) pasting data into sheet 1 and 2) pasting data in sheet 2.

Even better, if instead of pasting data into sheet 1 and 2, the spreadsheet contains only the Sheet 3 and pulls data from two external spreadsheets (the equivalent of sheet 1 and sheet 2) which obviously are always named the same and always placed in the same folder as the Sheet 3 spreadsheet.
That way I would not even need to cut and paste. Only copy 2 spreadsheet files (one with Sheet 1 data and one with Sheet 2 data) and when I open the spreadsheet it automatically pulls the data from the other 2 files and shows the combined data.

Any formulas/solutions?

I also tried using this formula but it does not work at all. I literally copied/pasted and used the exact same data shown in there but gives a #value error: How to extract unique values from multiple columns in Excel?
Reply With Quote
  #4  
Old 08-25-2020, 01:32 PM
Purfleet Purfleet is offline Merging all data between two sheets into a third one without duplicates or rows or columns Windows 10 Merging all data between two sheets into a third one without duplicates or rows or columns Office 2019
Expert
 
Join Date: Jun 2020
Location: Essex
Posts: 345
Purfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to behold
Default

You have 2 options as far as i know, but both will require some setup work.

1) VBA.
A macro could easily open an extract and combine data into 1 report with index and match, but you would need to know the file path and name and then be able to code it. not particularly hard but if you have never used VBA you would have a fairly steep learning curve


2) Power Query
As I said before I don’t know a lot about power query but from what i have read you can import extracts from a folder and hit refresh and the combining will be done - you would obviously need to do it once and set it up but then I should be a fairly quick refresh.
Reply With Quote
  #5  
Old 08-27-2020, 01:16 AM
p45cal's Avatar
p45cal p45cal is offline Merging all data between two sheets into a third one without duplicates or rows or columns Windows 10 Merging all data between two sheets into a third one without duplicates or rows or columns Office 2019
Expert
 
Join Date: Apr 2014
Posts: 863
p45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant future
Default

For both your recent threads power query will most likely be the best solution.
Could you supply Workbooks in the relevant threads,3 in one thread and 2 in the other?
Preferably showing the results you want.
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Transpose multiple data form rows in columns matched with a key Alex1s85 Excel 6 06-24-2020 07:08 AM
Merging all data between two sheets into a third one without duplicates or rows or columns How to Loop through rows and Columns to Offset Data Josh1012 Excel Programming 4 09-06-2018 03:20 AM
Insert rows when column A data changes then shade and autosum blanks in columns E to I kgoosen Excel Programming 0 08-30-2017 04:45 AM
Merging all data between two sheets into a third one without duplicates or rows or columns [EXCEL 2010] Merging data from three different sheets bremen22 Excel 1 08-20-2013 10:00 AM
Merging all data between two sheets into a third one without duplicates or rows or columns Merging three columns of data jdjette Excel 7 03-02-2012 04:48 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 03:00 AM.


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