Adding up how many time a number appears

[Mustang68]

I have a list of 10,000 part numbers. Many of the part numbers repeat. Is there a function that will give me a list of the part numbers and how many times they occur? In addition can the function also add up how mant times that part number was accepted or rejected? Sampe below:

39896 Reject
40675 Accept
41904 Reject
41904 Accept
41904 Accept
42117 Accept
42791 Accept
42855 Accept
42885 Accept
43132 Accept
43136 Accept
43737 Accept
43737 Accept
43737 Accept

Result:
Part# Accept Reject
39896 0 1
40675 1 0
41904 2 1
42117 1 0
42791 1 0
42855 2 0
43132 1 0
43136 1 0
43737 3 0
If I use the COUNTIF function I have to type in every part number to find the instances of that part number and there are hundreds of part numbers. Is there a function or group of functions that will do this for me automatically?

[vandan_tanna]

Have you tried pivot table?

If you need help, upload file and someone here can help you create a pivot table.

[jonboy6257]

I am not the best with excel or vba. But I had a similar situation and I used the following function to sort and mark repeated data. I then filtered the data by that addition. You can take it into a pivot table at that point and get the sum of the parts. Place the code in the cell you want to add the word duplicate and then drag down through all parts.

Please Login or Register  to view this content.

[Mustang68]

How do I upload a file?

[jonboy6257]

Click on quick reply, then click go advanced. In the top row of icons there should be a paperclip/. Click that and browse for your file!

[Mustang68]

Thanks. Here is the file. I'd like to have the result be how many times each part number appears and for each part number how many times it was accepted, and rejected.

[jonboy6257]

It's not working the way I want IE it isn't adding the duplicate parts 100% but here is an example of the book with a Pivot Table.part number accept reject.xlsx

[Mustang68]

Thanks Jonboy. I'll study it to see if I can figure out what's going on. It's on my list to get good at doing pivot tables. I feel so out of date.

[newdoverman]

I copied column A to a new location and Removed Duplicates then had columns for Accept and Reject.

[Mustang68]

THANKS!

That seems to work. Appreciate all the help.

[newdoverman]

Thanks for the feedback.