Forum Discussion
Merging Data Based on Duplicate Cell Data
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)
With Power Query perhaps something like this
- append all queries
- group by accounts without aggregation
- in tables fill fields and keep only first rows
- expand tables
More is in attached file.