Merging Data Based on Duplicate Cell Data

Copper Contributor

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 NoAccount NoClient NameMatter DescriptionDead NoKill DateCurrent Orig AttyCurrent Resp AttyCurrent Billing AttyDead File Atty
T123456789098765-4321Flinstone, FredGeneral Matters1234565/4/1995   BCA
 98765-4321Freddy FlintonePurchase of 555 Pebble Lane  SDFSDFGTR 

 

 

And I need it to look like this . . .  

 

Box NoAccount NoClient NameMatter DescriptionDead NoKill DateCurrent Orig AttyCurrent Resp AttyCurrent Billing AttyDead File Atty
T123456789098765-4321Flinstone, FredGeneral Matters1234565/4/1995SDFSDFGTRBCA

 

6 Replies

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.  

@jmurphy2020 

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?

@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.

@Sergei Baklan 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)

@jmurphy2020 

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.