Return All the Different Results

[south0085]

The title of this question was poorly written. I apologize.

I have a column with part numbers in it. Column I. It has the same 120 or so part numbers repeaded over and over and over again.

I have another column (Column J) with prices in it. Many of these prices for each part are the same, but some are different. Example:

from tmmc I J 4999 87940-0R190-C0 43.0835 5000 87910-0R210-B1 38.8914 5001 87940-0R190-C0 15.5287 5002 87940-0R190-C0 25.0341 5003 87910-0R190-D1 83.3021 <tbody> </tbody> I want to find out all the different prices there are for each part. For example, 87940-0R190-C0 has three different part numbers. So the returned value would be 3. Kind of like a count, I guess. Thank you for your help.

<tbody>
</tbody>

 

[mumps]

This macro will place the unique part number names in column L and the number of occurrences for each in column M.

Sub Test()
    Dim LastRow As Long
    LastRow = Sheets("Sheet1").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Dim rng As Range
    Sheets("Sheet1").Range("I1:I" & LastRow).AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Range("I1:I" & LastRow), Unique:=True
    Set rnguniques = Sheets("Sheet1").Range("I2:I" & LastRow).SpecialCells(xlCellTypeVisible)
    If Sheets("Sheet1").FilterMode Then Sheets("Sheet1").ShowAllData
    For Each rng In rnguniques
        Cells(Rows.Count, "L").End(xlUp).Offset(1, 0) = rng
        Cells(Rows.Count, "M").End(xlUp).Offset(1, 0) = WorksheetFunction.CountIf(Range("I:I"), rng)
    Next rng
End Sub

It assumes you have headers in row 1.

 

[south0085]

Thank you much!

 

[mumps]

You are very welcome.