Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Display unique values in a range.
I am looking for a procedure that will
loop through items in a range, and return the unique items starting in a target cell. Range 1 2 2 2 3 4 5 3 4 Range 2 2 3 4 5 such that when I run the macro each item in Range 1 is looked at until Range 1 =" ". The result being Range 2. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Display unique values in a range.
Here's a simple macro to do this
Dim cLastRow As Long Dim i As Long Dim j As Long Dim prevVal cLastRow = Cells(Rows.Count, "A").End(xlUp).Row prevVal = "" For i = 1 To cLastRow If Cells(i, "A").Value < prevVal Then j = j + 1 Cells(j, "B").Value = Cells(i, "A").Value prevVal = Cells(i, "A").Value End If Next i -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "future" wrote in message om... I am looking for a procedure that will loop through items in a range, and return the unique items starting in a target cell. Range 1 2 2 2 3 4 5 3 4 Range 2 2 3 4 5 such that when I run the macro each item in Range 1 is looked at until Range 1 =" ". The result being Range 2. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Display unique values in a range.
Or you could go to <data<filter<advanced filter select "copy to another
place" and tick "unique records." regards Bill K "Bob Phillips" wrote in message ... Here's a simple macro to do this Dim cLastRow As Long Dim i As Long Dim j As Long Dim prevVal cLastRow = Cells(Rows.Count, "A").End(xlUp).Row prevVal = "" For i = 1 To cLastRow If Cells(i, "A").Value < prevVal Then j = j + 1 Cells(j, "B").Value = Cells(i, "A").Value prevVal = Cells(i, "A").Value End If Next i -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "future" wrote in message om... I am looking for a procedure that will loop through items in a range, and return the unique items starting in a target cell. Range 1 2 2 2 3 4 5 3 4 Range 2 2 3 4 5 such that when I run the macro each item in Range 1 is looked at until Range 1 =" ". The result being Range 2. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Display unique values in a range.
I want the copy to range to be on a separate sheet. Is it possible to
change "B" to be a target cell on another sheet? *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Display unique values in a range.
this code only looks to see if a value is unique to its previous value.
any way to see if it is unique to all previous values? Sub Unique() Dim cLastRow As Long Dim i As Long Dim j As Long Dim prevVal cLastRow = Cells(Rows.Count, "A").End(xlUp).Row prevVal = "" For i = 1 To cLastRow If Cells(i, "A").Value < prevVal Then j = j + 1 Cells(j, "B").Value = Cells(i, "A").Value prevVal = Cells(i, "A").Value End If Next i End Sub |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Display unique values in a range.
Try this
Sub Unique() Dim cLastRow As Long Dim i As Long Dim j As Long Dim prevVal Dim c As Range cLastRow = Cells(Rows.Count, "A").End(xlUp).Row prevVal = "" For i = 1 To cLastRow Set c = Worksheets("Sheet2").Columns("B").Find(Cells(i, "A")) If c Is Nothing Then j = j + 1 Worksheets("Sheet2").Cells(j, "B").Value = Cells(i, "A").Value prevVal = Cells(i, "A").Value End If Next i End Sub -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "future" wrote in message om... this code only looks to see if a value is unique to its previous value. any way to see if it is unique to all previous values? Sub Unique() Dim cLastRow As Long Dim i As Long Dim j As Long Dim prevVal cLastRow = Cells(Rows.Count, "A").End(xlUp).Row prevVal = "" For i = 1 To cLastRow If Cells(i, "A").Value < prevVal Then j = j + 1 Cells(j, "B").Value = Cells(i, "A").Value prevVal = Cells(i, "A").Value End If Next i End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Display only unique values in CONCATENATE formula | Excel Discussion (Misc queries) | |||
Display Unique Values | Excel Worksheet Functions | |||
Display Unique Values Question | Excel Worksheet Functions | |||
count and display unique values | Excel Worksheet Functions | |||
Modifying a Formula To display only Unique Values | Excel Worksheet Functions |