1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Duplicate merge data Macro

Discussion in 'Business Applications' started by raceform, Jan 17, 2013.

Thread Status:
Not open for further replies.
Advertisement
  1. raceform

    raceform Thread Starter

    Joined:
    Feb 6, 2012
    Messages:
    32
    I wish to use a macro to sort and delete duplicates.
    column A contains names column G,H,I,J can contain values relative to the name on the row or could be a blank cell.
    When I update my data base there may be a duplicate name with different values in G,H,I,J.
    What I would like to achieve is to merge the new data to the old data but if the new data has a blank then keep the original value
    Please see attached file.
    Many thanks
    Steve.
    Excel 2007 Office
    Windows 7
     

    Attached Files:

  2. Garf13LD

    Garf13LD

    Joined:
    Apr 17, 2012
    Messages:
    455
    Where is your need data stored?
     
  3. raceform

    raceform Thread Starter

    Joined:
    Feb 6, 2012
    Messages:
    32
    Data is stored on Microsoft Office Excel 2007 on desktop called ratings
    What I do weekly is to import new data as plain text and paste in my ratings sheet (the book is called ratings with 1 sheet in it also called ratings) I paste the new data in cell A2 and tell excel to move all cells down (column A to column J)There is a Header on row 1
    After pasting I might have 14000 Duplicate cells in column A
    so a duplicate name could be in cell A2 and cell A14672
    I wish the macro to sort all of column A:A
    see new sheet.
    Thanks again
    Steve
     

    Attached Files:

  4. 20_2_Many

    20_2_Many

    Joined:
    Jun 29, 2012
    Messages:
    518
    Raceform - after you insert your new data, the way you stated, the sheet will have to be sorted by Col A.
    This may do what you need -

    Sub MergeDupe()
    lastrow = Range("A" & Rows.Count).End(xlUp).Row
    'Sheet must be sorted on Col A

    For i = lastrow To 3 Step -1
    Range("A" & i).Select
    If ActiveCell = ActiveCell.Offset(-1, 0) Then
    If ActiveCell.Offset(-1, 6) = "" Then
    ActiveCell.Offset(-1, 6) = ActiveCell.Offset(0, 6)
    End If
    If ActiveCell.Offset(-1, 7) = "" Then
    ActiveCell.Offset(-1, 7) = ActiveCell.Offset(0, 7)
    End If
    If ActiveCell.Offset(-1, 8) = "" Then
    ActiveCell.Offset(-1, 8) = ActiveCell.Offset(0, 8)
    End If
    If ActiveCell.Offset(-1, 9) = "" Then
    ActiveCell.Offset(-1, 9) = ActiveCell.Offset(0, 9)
    End If
    ActiveCell.EntireRow.Delete
    End If
    Next i
    End Sub
     
  5. 20_2_Many

    20_2_Many

    Joined:
    Jun 29, 2012
    Messages:
    518
    My Bad - forgot to change 3 Step - 1 to 2 Step - 1
     
  6. raceform

    raceform Thread Starter

    Joined:
    Feb 6, 2012
    Messages:
    32
    Thanks 20_2_Many
    Does the trick just the ticket
    Many Thanks have a Great Day
    Steve (raceform)
     
  7. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1085698

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice