Ric asked in Computers & InternetSoftware · 1 decade ago

Count unique records in excel?

I have a list in excel that have orders numbers the order numbers repeat I only want to count the order number once. Is it possible to it with a formula? I do not want to used the subtotal function. I want to extract the orders submitted without altering the list

2 Answers

Relevance
  • 1 decade ago
    Favorite Answer

    Here is one way to do it using a macro. The following example assumes your order numbers are in column A. It will create a unique list of the order numbers in column M, sorted.

    If your order number column is not column A, and/or you wish the list to be in a differenct column, modify the macro:

    Change the three "A" references to the column letter your order numbers are in, i.e. "C", "G", etc.

    Change the two "M" references to the column letter you wish the list to appear in, i.e. "V", "Z", etc.

    Change the three "M:M" references to the column letter for the list, i.e. "V:V", "Z:Z", etc.

    Change the three "M1" references to the column letter for the list, i.e. "V1", "Z1", etc.

    Then copy the macro, modified if necessary, to the clipboard:

    Sub Special_Countif()

    Dim i, LastRowA

    LastRowA = Range("A" & Rows.Count).End(xlUp).Row

    Application.ScreenUpdating = False

    Columns("M").ClearContents

    For i = 1 To LastRowA

    If Application.CountIf(Range("M:M"), Cells(i, "A")) = 0 Then

    Cells(i, "M").Offset(1, 0).Value = Cells(i, "A").Value

    End If

    Next

    Columns("M:M").Select

    Selection.Sort Key1:=Range("M1"), Order1:=xlAscending, _

    Header:=xlYes, OrderCustom:=1, MatchCase:=False, _

    Orientation:=xlTopToBottom, DataOption1:=xlSortNormal

    Range("M1").Value = "P.O. Number"

    Columns("M:M").AutoFit

    Range("M1").Select

    End Sub

    Press ALT + F11

    In the menus at the top of the VBE, select INSERT > MODULE

    Paste the macro into the editing area to the right.

    Close the VBE and return to the worksheet.

    Press ALT + F8

    When the Macros window opens, highlight the macro and click 'Options..'

    Enter a letter to be used as a keyboard shortcut and click 'OK'.

    Close the Macros window.

    To run the macro press CTRL + your shortcut letter.

    Any time you wish to create a new list, just run the macro.

  • 1 decade ago

    Why not use the built in Data filter option.

    Choose the order number you want display the highlight the range to see how many rows.

    You could also use a pivot table to display the order numbers and count how many times it appears.

    If you need any more help, drop me an e-mail

    Hope this helps

Still have questions? Get your answers by asking now.