[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Combine variable data from multiple redundant rows in Excel

Posted on 2015-01-07
3
Medium Priority
?
238 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
[X]
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
3 Comments
 
LVL 22

Accepted Solution

by:
rspahitz earned 1200 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 800 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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

649 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