collect all different numbers from column and sort them

  • Thread starter Thread starter toze
  • Start date Start date
T

toze

I have on sheet1 a column with thousands of numbers, some
equal some not.
On sheet2 I'm trying to get from that column only the
numbers that are different and sort them.
I believe I need a macro?
Can I get some help here?
 
One way:

Public Sub CopyAndSortUnique()
Dim rDest As Range
Application.ScreenUpdating = False
Set rDest = Sheets("Sheet2").Range("A1")
With Sheets("Sheet1").Columns("A:A")
.AdvancedFilter _
Action:=xlFilterInPlace, _
Unique:=True
.SpecialCells(xlCellTypeVisible).Copy rDest
.Parent.ShowAllData
End With
rDest.Sort key1:=rDest, _
order1:=xlAscending, _
header:=xlNo, _
MatchCase:=False
Application.ScreenUpdating = True
End Sub
 
You can do this manually, too:

Select the column. Choose Data/Advanced Filter. Select Filter In
Place and check the Unique checkbox. Click OK.

Choose Edit/GoTo.../Special and select Visible cells only. Click OK.

Edit/Copy. Select Sheet2, cell A1 and choose Edit/Paste.

Choose Data/Sort...
 
J.E. McGimpsey said:
One way:

Public Sub CopyAndSortUnique() ....
Click to expand...

Macros not needed, but they may be faster. FWIW, here's a purely formula
solution. If the original list of numbers is named TBL, and the distinct
values are to be extracted in ascending order with the smallest/topmost
value in cell C1 of some other worksheet, try these formulas.

C1:
=MIN(TBL)

C2: [array formula]
=MIN(IF(COUNTIF(C$1:C1,TBL)=0,TBL))

Select C2 and fill down as far as needed. The formula will evaluate to zero
when the distinct values in TBL have been exhausted.
 
Back
Top