Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    2 Star Lounger
    Join Date
    Jul 2004
    Location
    Edgecomb, Maine
    Posts
    108
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Combining Data in a sheet (Office Excel 2003 sp2)

    I have a spreadsheet with over 3000 rows of names and mail and/or email addresses, an example is attached. It starts out as two sheets, two separate sources of data, both inconsistent and incomplete. All have a name, some have email, some just snailmail, some both.

    For instance;
    First Last Email Address City State Zip
    Joe Smith joesmith@email.com
    Jane Smith 8 Anystreet Mytown ME 04103
    Sam White swhite@email.com
    John Doe jdoe@email.com 3 Dory Lane Boston MA 02210

    Joe Smith 8 Anystreet Mytown ME 04103
    Jane Smith jsmith@email.com

    (Sorry for the spacing mess, I couldn't figure out the spacing, my tabs and spaces don't show up for some reason, see attached worksheet.)

    I'm trying to figure out the easiest way to combine and pare it down to one list with name, email, and snailmail on one line, and not repeated. Can anyone help???

    Thanks in advance!

    --Jim
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Combining Data in a sheet (Office Excel 2003 sp2)

    Is it safe to assume that there are no duplicates within the data from each sheet?

  3. #3
    2 Star Lounger
    Join Date
    Jul 2004
    Location
    Edgecomb, Maine
    Posts
    108
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combining Data in a sheet (Office Excel 2003 sp2)

    No, there may be duplicated names ... in my example the last two, Jane and Joe Smith, are repeated.

    --Jim

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Combining Data in a sheet (Office Excel 2003 sp2)

    Yes, I know that, but you mentioned that the data were originally from two sheets. I assumed that the empty row separated the data from the two sheets, i.e. rows 2 through 5 represent data from one sheet (without duplicates), and rows 7 and 8 represent data from the other sheet (also without duplicates).
    In other words, each of the lists when viewed separately contains unique names, but the combined list obviously contains duplicate names.
    Is that correct, or are my assumptions way off?

  5. #5
    2 Star Lounger
    Join Date
    Jul 2004
    Location
    Edgecomb, Maine
    Posts
    108
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combining Data in a sheet (Office Excel 2003 sp2)

    That is correct, sorry I misunderstood your question.

    --Jim

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Combining Data in a sheet (Office Excel 2003 sp2)

    I would start by separating into 3 sheets (autofilter would help):

    1) names with both email and snailmail
    2) names with snailmail and no email
    3) names with email and no snailmail

    On each insert a new col A and create a (temporary) field of combined last and first names
    1) Use Vlookup in sheet2 to find the emails (from sheet3) that match the names in sheet2
    2) Use Vlookup in sheet3 to find the snailmails (from sheet2) that match the names in sheet3

    Now you should have 3 lists with both email and snailmails (and many duplicates)
    3) Combine all three lists together into one sheet
    4) Use advanced filter to create a copy of the records with all unique items.

    In steps 1/2 you may have to manually look for names that do not find a hit (due to differences in spelling or whatever)
    You may need a step 5 to manually remove "near duplicates" based on differences in spellings of names, addresses, etc

    Steve

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Combining Data in a sheet (Office Excel 2003 sp2)

    OK. I'd keep the data in separate sheets, that makes it easier to handle (although it's not strictly necessary).
    The attached version contains a macro that will attempt to fill in the first sheet.
    Attached Files Attached Files

  8. #8
    2 Star Lounger
    Join Date
    Jul 2004
    Location
    Edgecomb, Maine
    Posts
    108
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combining Data in a sheet (Office Excel 2003 sp2)

    sdckapr, I never thought of splitting to 3 sheets that way, I will give that a shot.

    Hans, My client wants the end result to be one point of contact with snailmail and email addresses together if they have both, without duplicate contact names. It does seem as though it would be easier to combine the first and last name for the process, then separate them in the final list (after the email and snailmail are together)>

    Thank-you both for your input, I'll be back with more questions that I may run into using vlookup.

    --Jim

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Combining Data in a sheet (Office Excel 2003 sp2)

    The macro in the workbook I posted will create a complete list with e-mail and snail mail addresses in the first sheet.

  10. #10
    2 Star Lounger
    Join Date
    Jul 2004
    Location
    Edgecomb, Maine
    Posts
    108
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combining Data in a sheet (Office Excel 2003 sp2)

    Hans,

    Sorry, I didn't see the macro until I looked. Now I have to ask how do I apply that macro to the worksheet with all the data?

    Thanks so much for your time.

    --Jim

    <img src=/S/bravo.gif border=0 alt=bravo width=16 height=30>

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Combining Data in a sheet (Office Excel 2003 sp2)

    Do the following:

    Put the first list in a worksheet, with column headers in the first row, as in your example.
    In H2, enter the formula =A2&" "&B2 to get the full name.
    Fill down to the last filled row.

    Do the same for the second list in another worksheet. It doesn't really matter which the "first" and "second" lists are, the result will be the same either way.

    You can copy the macro into this workbook, but it isn't necessary. You can also run it from theworkbook I posted, as long as the workbook with the real data is the active workbook.
    Activate the Visual Basic Editor (Alt+F11) and substitute the correct names for the worksheets (if necessary) in the lines

    Set ws1 = Worksheets("Sheet1")

    and

    Set ws2 = Worksheets("Sheet2")

    You can now run the macro, for example by clicking anywhere within it and pressing F5.

  12. #12
    2 Star Lounger
    Join Date
    Jul 2004
    Location
    Edgecomb, Maine
    Posts
    108
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combining Data in a sheet (Office Excel 2003 sp2)

    Hi Hans,

    Thank-you for your patience with me. Attached is the top row with all the column headers, and the first line of data. It actually goes to column 'N'. I have both sheets setup with the same column headers. When I edited the macro to change the sheet names, it worked, but when I ran the macro, it displayed the information in my sample sheet, not the actual data sheets I thought it would show. Before I run the macro, should I copy the info from one worksheet to the other, and run it on one worksheet? I'm not well-versed in macros, as you can tell, but I can usually wiggle my way through to success ... but need a little extra help right now.

    As you will see, I got the names into one column no problem.

    Thanks again,

    --Jim
    Attached Files Attached Files

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Combining Data in a sheet (Office Excel 2003 sp2)

    You *must* make sure that the workbook you want to process is the active workbook when you run the macro. If the sample workbook happens to be the active one when you run the macro, that will be the one the macro works upon.

    The workbook should have two sheets, with no duplication of names within one sheet. There will be names that occur on both sheets of course. I assume that 'mrec' is one of those sheets, you'll have to provide the name for the other one yourself since the workbook you attached contains only one sheet.

    Since the structure of the sheet is different from the one you attached earlier, the code will be slightly different too - this kind of code is hoghly dependent on the exact data structure. Here is the modifled version of the macro:
    <code>
    Sub MergeData()
    Dim ws1 As Worksheet
    Dim ws2 As Worksheet
    Dim m1 As Long
    Dim m2 As Long
    Dim r2 As Long
    Dim c2 As Long
    Dim oCell As Range

    Set ws1 = Worksheets("mrec")
    m1 = ws1.Range("A65536").End(xlUp).Row
    ' **** fill in the name of the second sheet in the next line ****
    Set ws2 = Worksheets("????")
    m2 = ws2.Range("A65536").End(xlUp).Row

    ' Loop through rows of ws2
    For r2 = 2 To m2
    ' Do we find a matching name on ws1?
    Set oCell = ws1.Range("A2:A" & m1).Find( _
    What:=ws2.Range("A" & r2), LookIn:=xlValues, _
    LookAt:=xlWhole, MatchCase:=False)
    If Not oCell Is Nothing Then
    ' If so, fill cells in the matching row, columns 2 to 14 (B to N)
    For c2 = 2 To 14
    If oCell.Offset(0, c2 - 1) = "" And Not ws2.Cells(r2, c2) = "" Then
    oCell.Offset(0, c2 - 1) = ws2.Cells(r2, c2)
    End If
    Next c2
    Else
    ' Append entire row
    ws2.Rows(r2).Copy _
    Destination:=ws1.Range("A65536").End(xlUp).Offset( 1, 0)
    End If
    Next r2
    End Sub
    </code>
    Don't forget to fill in the correct sheet name in the line below the comment.

  14. #14
    2 Star Lounger
    Join Date
    Jul 2004
    Location
    Edgecomb, Maine
    Posts
    108
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combining Data in a sheet (Office Excel 2003 sp2)

    Hi Hans,

    Thanks again for your patience. Attached is the top few lines of the real deal, 2 worksheets, the macro. When I run the macro, nothing happens. I'm not sure where I should look for the data to be displayed. You will see there are differences in the top few lines that I'm trying to pull into one list. I have a total of 1708 email addresses in the mrec worksheet, and 3987 emails in the mreis worksheet. That should give me one list with 5695 email addresses, and of that list, certain ones that originate from the mrec list will have a snailmail address as well.

    What else do I need to do or change in the macro so it will work? It's the only active workbook.

    Thanks,

    --Jim
    Attached Files Attached Files

  15. #15
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Combining Data in a sheet (Office Excel 2003 sp2)

    The problem is that the names in the mrec sheet have a trailing space after them ("A Lund ") and those in the mreis sheet don't ("A Lund"). The attached version uses the Trim function to solve this. When you run the macro, the mreis sheet will be updated.
    Attached Files Attached Files

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •