asked on

Need help organizing the first column of an Excel sheet

Excel 2019. How do I put in the first column a list of three digit numbers like:

256
745
989
284
103
256

Note the first and last are the same.

I want to after putting in 100 numbers or so, be able to make Excel a) delete any duplicates and b) list the numbers in column A from lowest number to highest number.

ASKER CERTIFIED SOLUTION

Tom Farrar

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Tom Farrar

Having said that, as soon as you select Remove Duplicates it will do just that and there is no reversing the step. I normally copy the data (in your example Column A) and paste into another column then remove the duplicates. That way I have both the original list and the reduced-down list where duplicates have been removed.

Flyster

If by first column you mean column A, you can run this macro:

Sub RemoveNSort()
Dim lRow As Double

lRow = Cells(Rows.Count, 1).End(xlUp).Row
Columns("A:A").Select
   ActiveSheet.Range("$A$1:$A$" & lRow).RemoveDuplicates Columns:=1, Header:=xlNo
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add2 Key:=Range("A1"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet1").Sort
        .SetRange Range("A1:A" & lRow)
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
Range("A1").Select
End Sub

Open in new window

Paul

Bert2005

ASKER

I meant the copying.

How do I do the sort column for numbers instead of letters.

Oh I got it. Under Data >> Sort >> then use numbers.

Tom Farrar

Thanks, Bert2005!