Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    Dec 2001
    Posts
    174
    Thanks
    0
    Thanked 0 Times in 0 Posts

    find unique include row above and below (excel2000+)

    Hi all!
    I have a very large file containing many records --each record consists of 3 rows (see attachment) -I need to keep only unique records defined by the cell value starting with "D???????" i.e. first cell in row 2 of each record.
    Hope I have made myself clear
    Smbs

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

    Re: find unique include row above and below (excel2000+)

    You could try this macro. It will not be very fast, but it should do the job:

    Sub DeleteDuplicates()
    Dim lngRow1 As Long
    Dim lngRow2 As Long
    Application.ScreenUpdating = False
    lngRow1 = 2
    Do While Not Range("A" & lngRow1) = ""
    lngRow2 = lngRow1 + 3
    Do While Not Range("A" & lngRow2) = ""
    If Range("A" & lngRow1) = Range("A" & lngRow2) Then
    Range((lngRow2 - 1) & ":" & (lngRow2 + 1)).Delete
    Else
    lngRow2 = lngRow2 + 3
    End If
    Loop
    lngRow1 = lngRow1 + 3
    Loop
    Application.ScreenUpdating = True
    End Sub

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: find unique include row above and below (excel2000+)

    Does the macro below give you what you want?

    <code>
    Public Sub DelDups()
    Dim oSH As Worksheet
    Dim I As Long, J As Long, lLastRow As Long
    Set oSH = ActiveSheet
    lLastRow = oSH.Range("B65536").End(xlUp).Row - 1
    Application.ScreenUpdating = False
    For I = lLastRow To 3 Step -3
    For J = I - 3 To 0 Step -3
    If oSH.Range("A1").Offset(I - 1, 0).Value = oSH.Range("A1").Offset(J - 1, 0).Value Then
    oSH.Range(Range("a1").Offset(I - 2, 0), Range("A1").Offset(I, 0)).EntireRow.Delete
    Exit For
    End If
    Next J
    Next I
    Application
    </code>
    Legare Coleman

  4. #4
    2 Star Lounger
    Join Date
    Dec 2001
    Posts
    174
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: find unique include row above and below (excel2000+)

    many thanx Legare works fine on small file at home but seeing we loop row by row I am worried that if sheet contains 5000+ rows how will the speed be?!! Will give it a try tomorrow at work!!
    Once again many thanx
    Smbs

  5. #5
    2 Star Lounger
    Join Date
    Dec 2001
    Posts
    174
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: find unique include row above and below (excel2000+)

    Thanx Hans works fine but as I stated in reply to Legare and as you mentioned I might have "velocity" problems on large file!
    Will let you know tomorrow
    Smbs

  6. #6
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: find unique include row above and below (excel2000+)

    <P ID="edit" class=small>(Edited by macropod on 31-May-06 20:17. Simpler formula)</P>Hi smbs,

    A fast way with a large worksheet is to:
    . insert an empty row 1
    . insert the following formula into cellF2
    =IF(LEFT(A2)="D",COUNTIF(A$2:A2,A2),IF(LEFT(A1)="D ",F1,F3))
    . copy this formula down to the last row
    . sort on column F
    . delete all rows with a value >1
    . delete column F
    . delete the inserted row

    You could automate the process with vba, making it faster still.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  7. #7
    2 Star Lounger
    Join Date
    Dec 2001
    Posts
    174
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: find unique include row above and below (excel2000+)

    Many thanx Macropod will give it a try
    Hans And Legare used your macros today on spreadsheet with over 6000 records and both worked fine there is a delay but not a problem
    Many thanx
    Smbs

Posting Permissions

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