Count Part Numbers

M

Matt Carter

I would like to know how many part numbers I have.
I have taken all the computers I have listed in a Visio document (2007) and
exported to an Excel 2007 report and now I have a listing of all the systems
in my organization and I would like to know how many part number (xyz) I have.
I know I have done something like this before with someone's help or did by
accident.

What the result looks like is a relisting of the part numbers organized
possibly in a new tab. It told me Part X and said this is listed 2 times and
shows the two part number lines. Then has a line break and then lists Part Y
and the 23 times it showed and the 23 lines.

My output goal is to give 40 new systems to other people as we are getting
new ones in and I want to roll them down to those who have the oldest systems
and then move up as others are available.


If that helps.

Thank you.
 
B

Bob Phillips

Assuming that the data is in A1:An

In H1, add

=A1

In H2, add this formula and copy down

=IF(ISERROR(MATCH(0,COUNTIF(H$1:H1,$A$1:$A$20&""),0)),"",
INDEX(IF(ISBLANK($A$1:$A$20),"",$A$1:$A$20),MATCH(0,COUNTIF(H$1:H1,$A$1:$A$20&""),0)))

this is an array formula, so commut with Ctl-Shift-Enter, not just Enter

On I1 add this formula, and copy down

=COUNTIF(A:A,H1)

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
Top