Jul 27 2020 09:38 AM
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 |
Jul 27 2020 10:06 AM
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.
Jul 27 2020 11:47 AM
@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.
Jul 27 2020 12:23 PM
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?
Jul 27 2020 12:35 PM
@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.
Jul 27 2020 12:45 PM
@Sergei Baklan I know I am not explaining it very well. I will try to clarify.
Jul 27 2020 04:30 PM
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.