VB and VBA Users Source Code: Returning distinct values from an Excel Range
[
Home
|
Contents
|
Search
|
Reply
| Previous | Next ]
VB/VBA Source Code
Returning distinct values from an Excel Range
By:
Andrew Baker
Email (spam proof):
Email the originator of this post
Date:
Friday, September 06, 2002
Hits:
1273
Category:
Unspecified
Article:
Often you may want to filter an excel range which contains duplicate values, to show only distinct values. The following function performs this (note, there is an example of how to using the function in the header). 'Purpose : Returns distinct values from a range 'Inputs : RangeValues The range containing the values to return the distinct items in. ' RangeDistinct The range which will to be populated with distinct values. ' Cell The Cell to return the distinct value into. 'Outputs : Returns a distinct value from a range of values 'Author : Andrew Baker 'Date : 04/Sep/2002 'Notes : Example: ' [A] [B] [Results] ' NAMES '[4] ANDREW =DISTINCTVALUE(A$4:A$8,B$4:B$8,B4) ANDREW '[5] PETER =DISTINCTVALUE(A$4:A$8,B$4:B$8,B5) PETER '[6] ANDREW =DISTINCTVALUE(A$4:A$8,B$4:B$8,B6) JOHN '[7] JOHN =DISTINCTVALUE(A$4:A$8,B$4:B$8,B7) MARK '[8] PETER =DISTINCTVALUE(A$4:A$8,B$4:B$8,B8) '[8] MARK =DISTINCTVALUE(A$4:A$8,B$4:B$8,B9) 'Revisions : Function DISTINCTVALUE(RangeValues As Excel.Range, RangeDistinct As Excel.Range, Cell As Excel.Range) Dim oColDistinct As Collection, lCellIndex As Long Dim oThisCell As Excel.Range Dim lIndexCount As Long Application.Volatile True lCellIndex = Cell.Row - RangeDistinct.Row Set oColDistinct = New Collection DISTINCTVALUE = "" On Error Resume Next For Each oThisCell In RangeValues oColDistinct.Add "", CStr(oThisCell.Value) If Err.Number Then 'Duplicate value Err.Clear ElseIf lIndexCount = lCellIndex Then 'Found the index we want DISTINCTVALUE = oThisCell.Value Exit For Else lIndexCount = lIndexCount + 1 End If Next Set oColDistinct = Nothing End Function
Terms and Conditions
Support this site
Download a trial version of the Excel Workbook Rebuilder
