#1
|
|||
|
|||
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 ! |
#2
|
||||
|
||||
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 [Fmr MS MVP - Word] |
#3
|
|||
|
|||
What Paul asked and also...is this just a one and done or will this need to be done periodically?
|
#4
|
|||
|
|||
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. |
#5
|
|||
|
|||
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.
|
#6
|
|||
|
|||
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. |
#7
|
|||
|
|||
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!!!
|
#8
|
||||
|
||||
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 [Fmr MS MVP - Word] |
#9
|
|||
|
|||
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 |
#10
|
||||
|
||||
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 [Fmr MS MVP - Word] |
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 |