Solved

Combine variable data from multiple redundant rows in Excel

Posted on 2015-01-07
3
202 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 31

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
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 will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

746 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now