Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2006
    Posts
    65

    Unanswered: How to Return Unique values in VB - Excel

    Hi,
    i am looking to return unique values from one sheet to another in Excel using vB, the code is shown below but returns multiple values in records where there is more than one instance,
    can anyone please show me what to modify to return unique records only,
    many thanks
    ================================================== =======




    Dim iLoop As Long
    Dim iMasterLoop As Long

    Dim gCount As Long

    gCount = 2

    Set xlBookActive = ActiveWorkbook

    Set xlMainSheet = xlBookActive.Worksheets(1)
    Set xlGlobalClock = xlBookActive.Worksheets(5)


    xlGlobalClock.Cells.ClearContents

    For iLoop = 1 To 65000 Step 1
    If xlMainSheet.Range("A" & iLoop & ":A" & iLoop).Value = "" Then
    Exit For
    End If
    Next iLoop

    For iMasterLoop = 1 To (iLoop - 1) Step 1

    If xlMainSheet.Range("H" & iMasterLoop & ":H" & iMasterLoop).Text = "YES" And _
    xlMainSheet.Range("P" & iMasterLoop & ":P" & iMasterLoop).Value <> "" And _
    xlMainSheet.Range("R" & iMasterLoop & ":R" & iMasterLoop).Value <> "" Then


    If xlMainSheet.Range("O" & iMasterLoop & ":O" & iMasterLoop).Value <> "" And _
    xlMainSheet.Range("R" & iMasterLoop & ":R" & iMasterLoop).Value <> "" Then
    xlGlobalClock.Range("E" & gCount & ":E" & gCount).Value = DateDiff("d", xlMainSheet.Range("O" & iMasterLoop & ":O" & iMasterLoop).Value, xlMainSheet.Range("R" & iMasterLoop & ":R" & iMasterLoop).Value)
    End If

    'CLRNID
    xlGlobalClock.Range("A" & gCount & ":A" & gCount).Value = xlMainSheet.Range("A" & iMasterLoop & ":A" & iMasterLoop).Value
    xlGlobalClock.Range("B" & gCount & ":B" & gCount).Value = xlMainSheet.Range("C" & iMasterLoop & ":C" & iMasterLoop).Value
    xlGlobalClock.Range("C" & gCount & ":C" & gCount).Value = xlMainSheet.Range("P" & iMasterLoop & ":P" & iMasterLoop).Value
    xlGlobalClock.Range("D" & gCount & "" & gCount).Value = DateDiff("d", xlMainSheet.Range("P" & iMasterLoop & ":P" & iMasterLoop).Value, xlMainSheet.Range("R" & iMasterLoop & ":R" & iMasterLoop).Value)


    gCount = gCount + 1

    End If

    Next iMasterLoop

  2. #2
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    If it was me, I would just automate the advanced filter via VBA, which has the ability to extract unique values. Let Excel do the hard work for you!

    When data gets to a certain size (very large in Excel terms) - and if most of the records are unique - then the advanced filter becomes more and more inefficient. At that point it would be worth writing your own procedure to extract unique values, which could be done a variety of ways including by using ADO. However, with data sets circa 10,000 records x 26 fields the advanced filter is more than quick enough.
    Last edited by Colin Legg; 05-10-10 at 13:40.

  3. #3
    Join Date
    Apr 2006
    Posts
    65
    thanks Colin

Posting Permissions

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