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.
Maybe an Advanced filter / Only unique records.
Artik
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?
Check KB for some methods
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
Remove duplicates, yes.
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.
[vba]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[/vba]
Are you saying that you want to sort column wise for each row?
____________________________________________
Nihil simul inventum est et perfectum
Abusus non tollit usum
Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
James Thurber
I'd like to sort each column I produce. Before I go to the [VBA]Next i[/VBA] 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.
This works:
[vba]
With Sheets("AutoList")
Data.Sort key1:=Data(1, 1)
End With
[/vba]
If data is a range on Autolist, the With statement is not required. You would need it if you were referring to an address.
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
In fact, just this works:
[VBA]Data.Sort key1:=Data(1, 1)
[/VBA]
Even more parsimonious
[VBA]Data.Sort Data(1)[/VBA]
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
I don't know what an Autolist is but this is just a list of data I created with the Macro.
I'm referring to your sheet name.
[VBA]
With Sheets("AutoList")
[/VBA]
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
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.
[VBA] ActiveWorkbook.Names.Add Name:="Names", RefersTo:=Data.Address[/VBA]
I used this code to add each column as a name based on the name of the "heading" above the column of data:
[vba] 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[/vba]
Last edited by flea333; 08-31-2010 at 10:39 AM.
[VBA][/VBA]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
Posting Permissions
Reply With Quote
