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
Eli
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?
Eli
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).
http://ca.geocities.com/b_davidso/Web_Page_Files/Excel/pivottable1.html
Regards,
Barrie
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
Range(Examine).Activate
'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
Fail:
End Sub
