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:
[TABLE="width: 168"]
<tbody>[TR]
[TD]
from tmmc

	I	J
87940-0R190-C0
87910-0R210-B1
87940-0R190-C0
87940-0R190-C0
87910-0R190-D1

<tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]4999
[/TD]

[TD="align: right"]43.0835
[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]5000
[/TD]

[TD="align: right"]38.8914
[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]5001
[/TD]

[TD="align: right"]15.5287
[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]5002
[/TD]

[TD="align: right"]25.0341
[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]5003
[/TD]

[TD="align: right"]83.3021
[/TD]

</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.[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[/TR]
</tbody>[/TABLE]

 

[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.