Forum Discussion
Merging Data Based on Duplicate Cell Data
Hi jmurphy2020
You can use merge function. By clicking on Data >> Get Data >> Combine Queries >> Merge.
Note that you need to create query by simply clicking on each data source and click on
Data >> Get Data >> From Other Sources >> From Table/Range.
This takes you to power query editor. You can make any change here is you desire. then click on Click and Load [you can choose close and load to from the drop down list] to select the desired destination.
After this is done then use the merge function. By selecting the query created based on each data and then you choose which data source should be the primary data and select common column for the merged file.
Let me know if this works or if you need any further clarification.
wumolad Thank you for the instructions. Unfortunately, that did not work as needed. Excel would not recognize the tables for the queries. I did try something similar when I first gathered the data from the three different sources - they were all done through queries and did not merge properly at the time. I ended up copying\pasting all the rows from all three sources into a new worksheet.
Logic is bit unclear. If for each account it could few rows with empty Box No, then take all Current Orig Atty (and other Current...) values from them returning as one combined text, or that's not the case? And it could be not empty such values for the records with not empty Box No or not?
SergeiBaklan I know I am not explaining it very well. I will try to clarify.
- Three queries were created to gather data from three separate data sources
- Data in all three sources were similar, but not exact text (for example, the client name from one source could be Mr. Fred Flinstone, while another data source could have Flinstone, Fred as the client name)
- Data text in most fields are based on how a user entered the text into that data source
- Not all fields were in all three data sources (the "Current ... Atty" fields were only in one data source and the "Box No" was in two other data sources without the "Current ... Atty" fields)
- I need to move the data in the "Current ... Atty" fields (those rows do not have box numbers) to the row that have a box number and blank "Current ... Atty" fields
- The common data for the rows is the Account No
- There could be 2 or more rows with the same Account No
- This process will reduce the overall number of rows (those without box numbers will be deleted)
