Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Combine variable data from multiple redundant rows in Excel

Posted on 2015-01-07
3
209 Views
Last Modified: 2015-01-16
Hello,

Is there a way to capture data from non-blank cells when multiple redundant rows exist in an Excel (2013) spreadsheet?

As an example, suppose you have a spreadsheet with a large number of rows which was created by pasting the contents of several other spreadsheets containing lists of contacts. And suppose you know that, as a result of combining the spreadsheets, some contacts are listed more than once but the redundant listings contain variable amounts of data which you don't want to lose.

For example, the following screenshot contains seven fictitious entries with typical contact data categories:
Fig. 1but notice that, based on email address, entries #2 & #6 are the same contact:
Fig. 2However, you don't want to simply delete the first or the second occurrence because each of them contains data not present in the other (ie #2 includes last name & address while #6 includes phone number.

For this particular contact, the best outcome would be a single entry which contains data in all fields.

As I wrote this question, I realized that a formula which consolidates data in this way would necessarily require some rules — and therefore, may not be solvable with a simple Excel formula. :

1) it should be based primarily on col H or I (phone or email address) since those are the most unique and therefore most reliable indicators as to whether or not two or more rows are redundant. For example, #1 & #5 are "probably" the same person (based on matching name & zip) but it's not 100% certain:
Fig. 32) some type of rule would be required for rows which although known to match (based on phone number and email address), contain differences in some other field, say "Address" for example.

3) are there other rules which I have not thought of?

Thanks
0
Comment
Question by:WeThotUWasAToad
3 Comments
 
LVL 22

Accepted Solution

by:
rspahitz earned 300 total points
ID: 40537199
One approach could be to create a new sheet that has a formula to search for duplicate information and show the merged data.
Assuming that the e-mail contains duplicates, you could sort on that field then create a simple formula to check to duplicates like this on a new sheet:
Z2 formula =IF(MasterSheet1~I1 = MasterSheet1!I2, "Dup", "")
copy this down across all rows.

Now you could create a formula to pull in all the data as such:

A1 formula =IF(Z2="Dup", MasterSheet1!A1 & MasterSheet1!A2, IF(Z1="Dup","", MasterSheet1!A1))

Apply this into each cell across and down and I think you'll get original data, merged data or blank rows (with the merged data above it)

--
If you don't know which columns could include the duplicates, you'll probably want to have some VBA navigate through each row and column searching for the dup's, but you'll have to determine some reasonable logic so that you don't get dup's on things like the state.

--
Oh, and what do you want to do if there's conflicting information, like an e-mail is used by two different people? My above formulas will merge those people into something like MaryGary, which may not be what you want, but you can examine those by looking at the rows that show "Dup"
0
 
LVL 33

Assisted Solution

by:Rob Henson
Rob Henson earned 200 total points
ID: 40537568
Yahoo! has an option on their Contacts page to do exactly this, combine duplicate Contact details.

I believe you can import to the Yahoo Contact list using CSV or TXT file, run the Duplicates Wizard and then Export back out as a CSV or TXT for importing into whichever other platform you use.

Thanks
Rob H
0
 

Author Closing Comment

by:WeThotUWasAToad
ID: 40554622
Thanks for the responses.
0

Featured Post

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
stuck in average-1 12 65
Distribute Values over date range 15 36
need formula to remove the first word in an excel cell 4 19
simple excel formula 4 14
Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

861 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question