Forum Discussion
Merging Data Based on Duplicate Cell Data
Hello all. I have a spreadsheet with well over 60,000 rows of data that were pulled from three separate sources. Several rows have the same account number, but the remaining fields for that row are similar but not exact. I need to take information from three cells in one row and put it in the other row then delete the prior row. Any easy way to do this??
I am using Excel 2016 on a Windows computer.
For example the original table looks like this:
| Box No | Account No | Client Name | Matter Description | Dead No | Kill Date | Current Orig Atty | Current Resp Atty | Current Billing Atty | Dead File Atty |
| T1234567890 | 98765-4321 | Flinstone, Fred | General Matters | 123456 | 5/4/1995 | BCA | |||
| 98765-4321 | Freddy Flintone | Purchase of 555 Pebble Lane | SDF | SDF | GTR |
And I need it to look like this . . .
| Box No | Account No | Client Name | Matter Description | Dead No | Kill Date | Current Orig Atty | Current Resp Atty | Current Billing Atty | Dead File Atty |
| T1234567890 | 98765-4321 | Flinstone, Fred | General Matters | 123456 | 5/4/1995 | SDF | SDF | GTR | BCA |
6 Replies
jmurphy2020 I think what you want might be achieved using a pivot table where you group the data based on Account No. You can also use custom fields in the pivot table creation if you need to process the data to be displayed. I wish I could be more specific / helpful but without a file it is hard.
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?
