Microsoft Office Forums

Go Back   Microsoft Office Forums > Microsoft Excel > Excel

Reply
 
LinkBack Thread Tools Display Modes
  #1  
Old 07-22-2014, 07:53 PM
nwiabrad nwiabrad is offline Windows 7 64bit Office 2013
Novice
 
Join Date: Jul 2014
Posts: 5
nwiabrad is on a distinguished road
Question merging data sets in excel 2013

so I have a .csv files with sales leads info.
The database where I download this from provides husband and wife info.
Nice to have both spouses names but the database repeats the whole dataset.
Is there a way to take just one column (ie male's first name and merge it with the first name column of the females first name and just ignore the rest of that second dataset?)



EXAMPLE:

John Smith (555) 555-5555 1234 any street AnyCity, Georgia XXXXX-XXXX
Judy Smith (555) 555-5555 1234 any street AnyCity, Georgia XXXXX-XXXX

combined into

John Judy Smith (555) 555-5555 1234 any street AnyCity, Georgia XXXXX-XXXX


Any Help would be appreciated!
Thank you !
Reply With Quote
  #2  
Old 07-23-2014, 05:04 AM
macropod's Avatar
macropod macropod is offline Windows 7 32bit Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 19,358
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default

Yes, it's possible, but doing so requires more information about the data. For example:
• Are the given names always the only difference;
• Do all the data concern spouses (i.e. no singles); and
• are the data all in one column, or does each field (e.g. given name, surname, phone, etc.) have it's own column?
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
  #3  
Old 07-23-2014, 09:14 AM
gebobs gebobs is offline Windows 7 64bit Office 2010 64bit
Expert
 
Join Date: Mar 2014
Location: Atlanta
Posts: 832
gebobs has a spectacular aura aboutgebobs has a spectacular aura about
Default

What Paul asked and also...is this just a one and done or will this need to be done periodically?
Reply With Quote
  #4  
Old 07-23-2014, 10:40 AM
nwiabrad nwiabrad is offline Windows 7 64bit Office 2013
Novice
 
Join Date: Jul 2014
Posts: 5
nwiabrad is on a distinguished road
Default

gebobs and macropod - thank you for responding.

I'll start with Gebobs question first...

That depends :-) To give you a background on the scenario...

I'm an insurance agent - I download lead information from a leadlist database website.

It's downloaded in .csv format

I can download up to 10,000 "entries at a time" so there will always be large lists to deal with.

Once I download the list and sort by Zip code I will then send the .csv file to the call center manager for the appointment setters to call.

While it would be nice to have both spouses names for the telemarketers to call, I don't want to give them a .csv with two lines of datasets per household for fear they'll call each place twice and anger my prospects.

I tried to do it the easy way and ask the leadlist owner if he could have his database managers set a parameter on married leads to merge the first names of the leads so you download both spouses in one dataset and it's a new company and he doesn't have man-hours to undertake that herculean task.

So in essence, the short answer to your question is yes it will most likely be an ongoing issue whenever I get the .csv lead list download.
Reply With Quote
  #5  
Old 07-23-2014, 10:45 AM
gebobs gebobs is offline Windows 7 64bit Office 2010 64bit
Expert
 
Join Date: Mar 2014
Location: Atlanta
Posts: 832
gebobs has a spectacular aura aboutgebobs has a spectacular aura about
Default

In that case, I think you'll need some VBA. Paul's your man. I cut my VBA teeth in Access and for whatever reason can't wrap my head around Excel VBA without inducing several mini-strokes.
Reply With Quote
  #6  
Old 07-23-2014, 11:32 AM
nwiabrad nwiabrad is offline Windows 7 64bit Office 2013
Novice
 
Join Date: Jul 2014
Posts: 5
nwiabrad is on a distinguished road
Default

gebobs and macropod

John Doe 555-555-5555 123 anystreet anytown WA King 97xxx

53


$120,000

Jane Doe 555-555-5555 123 anystreet anytown WA King 97xxx

53


$120,000

I tried to cop/paste from excel with the borders added so you would see each set had it's own column but it didn't copy the borders over

Are the given names always the only difference; yes (they only include the salary of the first person so everything is the same except their first name
Do all the data concern spouses (i.e. no singles); and (no) there are some single people in the list as well – to give you an idea I started out with 5870 data sets, when I went to “data” tab and clicked remove duplicate datasets based on phone # and address it brought me down to about 3500 datasets
are the data all in one column, or does each field (e.g. given name, surname, phone, etc.) have it's own column? (see the example above)

Most sales lead databases I've ever used typically don't even include the spouses name in the database so it's no big deal if the best resource is to just do a delete duplicate data. However, if there is a fairly easy process to merge the two rows of data based on just needing to merge the first (personal, given) name then that would be sweet to know how to do so.
Reply With Quote
  #7  
Old 07-23-2014, 11:34 AM
nwiabrad nwiabrad is offline Windows 7 64bit Office 2013
Novice
 
Join Date: Jul 2014
Posts: 5
nwiabrad is on a distinguished road
Default

Gebobs....I know the feeling, I just hear the words database, VBS, SQL, or Oracle and my eyes start twitching and I come to a few minutes later... - Before I went back to selling insurance, I was a Network Admin. I dealt with servers, switches, routers and firewalls.... we had a team for everything data/database related and I thanked God for them daily!!!
Reply With Quote
  #8  
Old 07-23-2014, 07:32 PM
macropod's Avatar
macropod macropod is offline Windows 7 32bit Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 19,358
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default

nwiabrad: There is no way I can tell from your reply in post #6 how the data are laid out in Excel. Please attach a workbook to a post with some representative data (delete anything sensitive). You do this via the paperclip symbol on the 'Go Advanced' tab at the bottom of this screen.
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
  #9  
Old 07-23-2014, 08:54 PM
nwiabrad nwiabrad is offline Windows 7 64bit Office 2013
Novice
 
Join Date: Jul 2014
Posts: 5
nwiabrad is on a distinguished road
Default attached example

Ok I included a sample .xlsx file because it didn't list .csv files below.

The sample while it's a fictitious example the layout is valid for what I have to work with except it's in .csv format not .xlsx
Attached Files
File Type: xlsx sample_lead_list.xlsx (8.8 KB, 6 views)
Reply With Quote
  #10  
Old 07-24-2014, 12:07 AM
macropod's Avatar
macropod macropod is offline Windows 7 32bit Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 19,358
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default

There is little representative data in your workbook (e.g. no singles) and I suspect your CSV file doesn't have non-data lines at the top.

Am I to assume the heading row in your workbook exists in the CSV file (on row 1) and that there are no gaps between it and the data rows, or between the data rows?

PS: CSV files can be attached in zip archives or as .txt files.
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
Reply

Tags
merge, merge data, merge field

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
[EXCEL 2010] Merging data from three different sheets bremen22 Excel 1 08-20-2013 10:00 AM
Match two sets of data and display specific data lolly150 Excel 1 05-14-2012 10:33 PM
Mail merging and pulling varying data from Excel shannag1881 Mail Merge 0 10-05-2009 08:51 AM
Finding the complements between the two data sets psenku Excel 2 08-17-2009 11:15 PM
One bar chart for 3 sets of data rclloyd Excel 1 02-03-2006 09:51 AM


All times are GMT -7. The time now is 11:52 AM.


Powered by vBulletin® Version 3.8.1
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
SEO by vBSEO ©2011, Crawlability, Inc.
MSOfficeForums.com is not affiliated with Microsoft