MrExcel Publishing
Your One Stop for Excel Tips & Solutions

How to sort data without repeating records?

Posted by Eli Weiss on June 13, 2001 10:07 PM

How can I sort my data so that I will get only one record of any kind with no repeats
I mean that if I sort my data on column A
then if I have the same data more then once
lets say in A1 and A3
the result will show me only the first one
thanks in advance for any help

Posted by Richard S on June 13, 2001 10:50 PM

Try a pivot table

Posted by Richard S on June 13, 2001 10:51 PM

Re: Try a Pivot Table

Posted by Eli Weiss on June 13, 2001 11:00 PM

How? please show me!

First, Thank you
Can you provide an example?

Posted by Barrie Davidson on June 14, 2001 9:09 AM

Re: How? please show me!

Eli, have a look at my demonstration of a pivot table (pictures are much easier to relate to).


Posted by BruceT on June 22, 2001 1:45 AM

try this (paste into a VBA window & use ALT+F8 to call

Sub unique_values()
'Creates a sorted list of unique values starting at Target

Dim Examine As String 'topmost cell in the range of values
Dim Target As String 'the topmost cell of the output
Dim ThisPrompt As String

ThisPrompt = "Where is the Label at the top of the values to test ?" _
& Chr(13) & Chr(13) & "Remember ;" & Chr(13) & Chr(13) & _
"It will reappear at the top of your unique values"

On Error GoTo Fail:
Examine = InputBox(ThisPrompt)
ThisPrompt = "Where is the Label to be put ?" _
& Chr(13) & Chr(13) & "Remember ;" & Chr(13) & Chr(13) & _
"You will need blank cells under the label" & _
Chr(13) & "and AAA is the same as aaa"

Target = InputBox(ThisPrompt)

Range(Target).Clear 'needed to stop filtering falling over
'filter then insert unique values starting at Target
Range(Examine, ActiveCell.End(xlDown)).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range( _
Target), Unique:=True
'now sort the values
Range(Target).Select 'musn't remove this line
Range(Target, ActiveCell.End(xlDown)).Select
Selection.Sort Key1:=Range(Target), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

End Sub