extracting unique items
 

Hi I received this great response to an earlier post that I have made
concerning the following sweep:
http://www.standardsbigbid.com/londo...Help.php#works,
...and would need the kind of illustration, a pretty much excel novice person
like myself, would need. Thanks for your (listening) time :-
-----Original Message-----
Hi, the following macro will extract unique values. The
input range is Rng1 and the output range is Rng2.

Sub ListUniqueVal()

Dim Rng1 As Range
Dim Rng2 As Range
Dim Cell As Range
Dim UniqueValues As New Collection

Set Rng1 = Range("B4:C10")
Set Rng2 = Range("E4")
On Error Resume Next
For Each Cell In Rng1
UniqueValues.Add Cell.Value, CStr(Cell.Value)
Next Cell

For Each Item In UniqueValues
Rng2.Value = Item
Set Rng2 = Rng2.Offset(1, 0)
Next Item

End Sub


John Mansfield
pdbook.com

extracting unique items
 

Hi Tury

Try Advanced filter
http://www.contextures.com/xladvfilter01.html#FilterUR

--
Regards Ron de Bruin
http://www.rondebruin.nl


"turay" wrote in message ...
Hi I received this great response to an earlier post that I have made
concerning the following sweep:
http://www.standardsbigbid.com/londo...Help.php#works,
..and would need the kind of illustration, a pretty much excel novice person
like myself, would need. Thanks for your (listening) time :-
-----Original Message-----
Hi, the following macro will extract unique values. The
input range is Rng1 and the output range is Rng2.

Sub ListUniqueVal()

Dim Rng1 As Range
Dim Rng2 As Range
Dim Cell As Range
Dim UniqueValues As New Collection

Set Rng1 = Range("B4:C10")
Set Rng2 = Range("E4")
On Error Resume Next
For Each Cell In Rng1
UniqueValues.Add Cell.Value, CStr(Cell.Value)
Next Cell

For Each Item In UniqueValues
Rng2.Value = Item
Set Rng2 = Rng2.Offset(1, 0)
Next Item

End Sub


John Mansfield
pdbook.com

extracting unique items
 

Thanks Ron de Bruin for the useful link.
Woudl anyone know of a formula and its expression that helps you avoid
typing, manually, the amounts in systematic order (e.g. from 1.00-1.99 and
son on)? Thanks again.
turay
"Ron de Bruin" kirjoitti viestissä
...
Hi Tury

Try Advanced filter
http://www.contextures.com/xladvfilter01.html#FilterUR

--
Regards Ron de Bruin
http://www.rondebruin.nl


"turay" wrote in message
...
Hi I received this great response to an earlier post that I have made
concerning the following sweep:
http://www.standardsbigbid.com/londo...Help.php#works,
..and would need the kind of illustration, a pretty much excel novice
person
like myself, would need. Thanks for your (listening) time :-
-----Original Message-----
Hi, the following macro will extract unique values. The
input range is Rng1 and the output range is Rng2.

Sub ListUniqueVal()

Dim Rng1 As Range
Dim Rng2 As Range
Dim Cell As Range
Dim UniqueValues As New Collection

Set Rng1 = Range("B4:C10")
Set Rng2 = Range("E4")
On Error Resume Next
For Each Cell In Rng1
UniqueValues.Add Cell.Value, CStr(Cell.Value)
Next Cell

For Each Item In UniqueValues
Rng2.Value = Item
Set Rng2 = Rng2.Offset(1, 0)
Next Item

End Sub


John Mansfield
pdbook.com

extracting unique items
 

the code provided will not do what you are looking for, nor will advanced
filter. Either would give you a list of all entries, but only show each
entry one time (thus a list of unique entries, but not onetime entries).
You would then need to use countif to see how many times each item in the
"unique" list is actually a unique/single entry in the original list. But,
since you don't have a list of all the entries in the sweepstakes, then
there is no way you can extract anything. What are you hoping to
accomplish? I don't see Excel playing any role in you efforts, so being
novice should offer no impediment.

--
Regards,
Tom Ogilvy

"turay" wrote in message
...
Hi I received this great response to an earlier post that I have made
concerning the following sweep:
http://www.standardsbigbid.com/londo...Help.php#works,
..and would need the kind of illustration, a pretty much excel novice
person
like myself, would need. Thanks for your (listening) time :-
-----Original Message-----
Hi, the following macro will extract unique values. The
input range is Rng1 and the output range is Rng2.

Sub ListUniqueVal()

Dim Rng1 As Range
Dim Rng2 As Range
Dim Cell As Range
Dim UniqueValues As New Collection

Set Rng1 = Range("B4:C10")
Set Rng2 = Range("E4")
On Error Resume Next
For Each Cell In Rng1
UniqueValues.Add Cell.Value, CStr(Cell.Value)
Next Cell

For Each Item In UniqueValues
Rng2.Value = Item
Set Rng2 = Rng2.Offset(1, 0)
Next Item

End Sub


John Mansfield
pdbook.com

extracting unique items
 

Thanks for your honest assessment.
turay
"Tom Ogilvy" kirjoitti viestissä
...
the code provided will not do what you are looking for, nor will advanced
filter. Either would give you a list of all entries, but only show each
entry one time (thus a list of unique entries, but not onetime entries).
You would then need to use countif to see how many times each item in the
"unique" list is actually a unique/single entry in the original list.
But,
since you don't have a list of all the entries in the sweepstakes, then
there is no way you can extract anything. What are you hoping to
accomplish? I don't see Excel playing any role in you efforts, so being
novice should offer no impediment.

--
Regards,
Tom Ogilvy

"turay" wrote in message
...
Hi I received this great response to an earlier post that I have made
concerning the following sweep:
http://www.standardsbigbid.com/londo...Help.php#works,
..and would need the kind of illustration, a pretty much excel novice
person
like myself, would need. Thanks for your (listening) time :-
-----Original Message-----
Hi, the following macro will extract unique values. The
input range is Rng1 and the output range is Rng2.

Sub ListUniqueVal()

Dim Rng1 As Range
Dim Rng2 As Range
Dim Cell As Range
Dim UniqueValues As New Collection

Set Rng1 = Range("B4:C10")
Set Rng2 = Range("E4")
On Error Resume Next
For Each Cell In Rng1
UniqueValues.Add Cell.Value, CStr(Cell.Value)
Next Cell

For Each Item In UniqueValues
Rng2.Value = Item
Set Rng2 = Rng2.Offset(1, 0)
Next Item

End Sub


John Mansfield
pdbook.com