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

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.

thanks Colin