PDA

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