Solved

Combine variable data from multiple redundant rows in Excel

Posted on 2015-01-07
3
208 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 32

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

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

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

Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

831 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