View Full Version : [SOLVED:] Identify Unique Cells In Range
flea333
08-20-2010, 11:44 AM
Looking for efficient way to list out every unique cell within a range. I have repeat cell data and want to compile a list to use for a drop down selection with only the unique data.
I was thinking of using a recursive "ColumnDifferences" or looping through but those seem complicated. There must be a shorter way.
Artik
08-20-2010, 02:27 PM
Maybe an Advanced filter / Only unique records.
Artik
mikerickson
08-20-2010, 11:14 PM
What do you mean by unique?
From {a, a, b, c, d, d}, what do you want
{b, c} which are the unique elements.
or
{a, b, c, d} which has the duplicates removed?
mdmackillop
08-21-2010, 11:40 AM
Check KB for some methods
flea333
08-22-2010, 01:43 PM
Remove duplicates, yes.
flea333
08-24-2010, 08:52 PM
I've come up with code, based on another response I received earlier, to solve this problem. It takes a list from one specified range (that contains duplicates) and copies each unique value into a new list on a different specified range. My only issue is figuring out how to sort each produced range now. Then once it is sorted, I want to add a name to the range. I'm sure that's easy.
Dim Headings As Range
Dim autolist As Range
Dim DataStart As Integer
DataStart = 20
With Sheets("Export")
Set Headings = .Range("A12:R12")
End With
With Sheets("AutoList")
Set Data = .Range("A" & DataStart)
Range(Cells(20, 1), Cells(.Cells(Rows.Count, 1).End(xlUp).Row + 1, Headings.Columns.Count)).Clear
End With
'scroll through each column data
'copy each cell
For i = 1 To Headings.Columns.Count Step 1
With Sheets("Export")
Set Source = Range(.Cells(13, i), .Cells(Rows.Count, i).End(xlUp)) 'start at row 20, end at last element
End With
For Each cel In Source
If cel <> "" Then
With Sheets("AutoList")
Set Data = Range(.Cells(DataStart, i), .Cells(Rows.Count, i).End(xlUp).Offset(1))
Set c = Data.Find(cel)
If c Is Nothing Then
.Cells(Rows.Count, i).End(xlUp).Offset(1) = cel
End If
End With
End If
Next cel
'Sheets("AutoList").Data.Sort (Sheets("AutoList").Data.Cells(2, 1))
Next i
Bob Phillips
08-25-2010, 12:37 AM
Are you saying that you want to sort column wise for each row?
flea333
08-25-2010, 10:51 AM
I'd like to sort each column I produce. Before I go to the Next i section (which starts the copying of another column), I want to simply sort the range "Data." Don't know why Data.Sort doesn't work.
flea333
08-25-2010, 04:11 PM
This works:
With Sheets("AutoList")
Data.Sort key1:=Data(1, 1)
End With
mdmackillop
08-25-2010, 04:17 PM
If data is a range on Autolist, the With statement is not required. You would need it if you were referring to an address.
flea333
08-25-2010, 04:21 PM
In fact, just this works:
Data.Sort key1:=Data(1, 1)
mdmackillop
08-25-2010, 04:28 PM
Even more parsimonious
Data.Sort Data(1)
flea333
08-25-2010, 04:31 PM
I don't know what an Autolist is but this is just a list of data I created with the Macro.
mdmackillop
08-25-2010, 04:33 PM
I'm referring to your sheet name.
With Sheets("AutoList")
flea333
08-25-2010, 05:09 PM
Now how do I name the range "Data" at the end of each loop?
I want to name the range based on the "heading" value, which is the value cell up from the top of the range - offset(-1).
This code works, but doesn't seem to add the name into my worksheet. I want each of these columns of sorted data to become a dropdown list somewhere else in the workbook.
ActiveWorkbook.Names.Add Name:="Names", RefersTo:=Data.Address
flea333
08-31-2010, 08:43 AM
I used this code to add each column as a name based on the name of the "heading" above the column of data:
head = Sheets("AutoList").Cells(Data(1).Row - 1, i) 'grab the cell above the range Data
If InStr(head, " ") Then 'Names can't have spaces, remove them
head = Split(head, " ")
head = head(0)
End If
On Local Error Resume Next 'If name doens't exist can't delete, skip error
ActiveWorkbook.Names(head).Delete
On Local Error GoTo 0 'If error adding name, don't do anything
ActiveWorkbook.Names.Add Name:=head, RefersTo:="=AutoList!" & Data.Address
QuestionsBNP
12-20-2010, 02:07 PM
MyList.AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Worksheets("Datas").Cells(1, 1), Unique:=True
You can get unique values if you set MyList to the column range that you want to analyze
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.