Count incidences of part numbers

I have a file downloaded daily from Oracle that lists our critical shortages by part number. These part numbers can have multiple incidences of shortages. My task is to sort the file by part number, then establish the number of different part numbers that have shortages reported. For example, part number 123-000 has 3 lines, or incidences, of shortages. This would count as one. Part number 123-001 has one line, or incident, of shortage, and this would also count as one. I scrolled through the list of solutions under "Count", and didn't have any luck. Help is obviously appreciated.

Jeff

Hi
so you want to count the number of unique part numbers. If your part
numbers are stored in column A try the following formula:
=SUMPRODUCT(1/COUNTIF(A1:A1000,A1:A1000&""))

--
Regards
Frank Kabel
Frankfurt, Germany
"jmdaniel" schrieb im Newsbeitrag
...
I have a file downloaded daily from Oracle that lists our critical
shortages by part number. These part numbers can have multiple
incidences of shortages. My task is to sort the file by part number,
then establish the number of different part numbers that have shortages
reported. For example, part number 123-000 has 3 lines, or incidences,
of shortages. This would count as one. Part number 123-001 has one
line, or incident, of shortage, and this would also count as one. I
scrolled through the list of solutions under "Count", and didn't have
any luck. Help is obviously appreciated.

Jeff

Hi Jeff,

You could put al of the part numbers in a column, say A, on another sheet,
and add this formula to B1
=MIN(1,COUNTIF(Sheet1!$A$1:$A$100,A1))
and copy down column B
--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"jmdaniel" wrote in message
...
I have a file downloaded daily from Oracle that lists our critical
shortages by part number. These part numbers can have multiple incidences of
shortages. My task is to sort the file by part number, then establish the
number of different part numbers that have shortages reported. For example,
part number 123-000 has 3 lines, or incidences, of shortages. This would
count as one. Part number 123-001 has one line, or incident, of shortage,
and this would also count as one. I scrolled through the list of solutions
under "Count", and didn't have any luck. Help is obviously appreciated.

Jeff

I don't know exactly your set up, but if I were in your shoes, I would
create a complete parts list in one sheet. This sheet would contain the
formulas to count incidents of shortages on the other worksheet, which is
the one linked to the database which I wouldn't put any formulas on (if I
could help it).

On the sheet which has the part numbers, I would put the formula next to
each part number --(COUNTIF(Sheet2!A$1:A$9999,D1)0)

D1 being the first part number in your list of all part numbers. This would
give you a 1 if the part number on your main sheet was found somewhere in
the range of your database linked sheet. If it was not found, you would get
a 0.

Since you may have many part numbers, many of them not needing any attention
(no shortages), I would then record a macro from the main sheet, which
selects the part number, and the value returned by the formula.. and sort
descending on the value, putting the items with the most shortages at the
top. You could then record another macro to sort by part number; and then
sort back and forth with the click of a button.

Hope that helps.



"jmdaniel" wrote in message
...
I have a file downloaded daily from Oracle that lists our critical
shortages by part number. These part numbers can have multiple incidences of
shortages. My task is to sort the file by part number, then establish the
number of different part numbers that have shortages reported. For example,
part number 123-000 has 3 lines, or incidences, of shortages. This would
count as one. Part number 123-001 has one line, or incident, of shortage,
and this would also count as one. I scrolled through the list of solutions
under "Count", and didn't have any luck. Help is obviously appreciated.

Jeff

just get a div/0 error...

which sheet does the formula go on, and what sheet is "Sheet1"? and what will copying down column B get me? sorry to not be seeing where this is going.

----- Bob Phillips wrote: -----

Hi Jeff,

You could put al of the part numbers in a column, say A, on another sheet,
and add this formula to B1
=MIN(1,COUNTIF(Sheet1!$A$1:$A$100,A1))
and copy down column B
--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"jmdaniel" wrote in message
...
I have a file downloaded daily from Oracle that lists our critical
shortages by part number. These part numbers can have multiple incidences of
shortages. My task is to sort the file by part number, then establish the
number of different part numbers that have shortages reported. For example,
part number 123-000 has 3 lines, or incidences, of shortages. This would
count as one. Part number 123-001 has one line, or incident, of shortage,
and this would also count as one. I scrolled through the list of solutions
under "Count", and didn't have any luck. Help is obviously appreciated.
Jeff

Hi
IMHO this is not possible :-)
what is the exact formula you have entered and also have you change the
range A1:A1000 to your needs

--
Regards
Frank Kabel
Frankfurt, Germany

jmdaniel wrote:
just get a div/0 error...

Frank, the only way I got it to work correctly was to subtract 1 from the
value returned.. but I suppose this detail is the least of his concerns.

=SUMPRODUCT(1/COUNTIF(A1:A1000,A1:A1000&""))-1

"Frank Kabel" wrote in message
...
Hi
IMHO this is not possible :-)
what is the exact formula you have entered and also have you change the
range A1:A1000 to your needs

--
Regards
Frank Kabel
Frankfurt, Germany

jmdaniel wrote:
just get a div/0 error...

Jeff,

This assumes that the downloaded data is on sheet1.

You then create a list of part numbers on another sheet, in column A, and
put that formula in column B.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"jmdaniel" wrote in message
...
which sheet does the formula go on, and what sheet is "Sheet1"? and what
will copying down column B get me? sorry to not be seeing where this is
going.

----- Bob Phillips wrote: -----

Hi Jeff,

You could put al of the part numbers in a column, say A, on another
sheet,
and add this formula to B1
=MIN(1,COUNTIF(Sheet1!$A$1:$A$100,A1))
and copy down column B
--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"jmdaniel" wrote in message
...
I have a file downloaded daily from Oracle that lists our critical
shortages by part number. These part numbers can have multiple
incidences of
shortages. My task is to sort the file by part number, then establish
the
number of different part numbers that have shortages reported. For
example,
part number 123-000 has 3 lines, or incidences, of shortages. This
would
count as one. Part number 123-001 has one line, or incident, of
shortage,
and this would also count as one. I scrolled through the list of
solutions
under "Count", and didn't have any luck. Help is obviously
appreciated.
Jeff

Hi Dave
yes this formula counts a blank row also as one occurence. The benefit
is that it does not throw a #DIV/0 error if a blank row is within the
range. This would happen for example with the formula
=SUMPRODUCT(1/COUNTIF(A1:A1000,A1:A1000))


I should have mentioned this trade-off though. so thanks for pointing
this out :-)

--
Regards
Frank Kabel
Frankfurt, Germany

Dave R. wrote:
Frank, the only way I got it to work correctly was to subtract 1 from
the value returned.. but I suppose this detail is the least of his
concerns.

=SUMPRODUCT(1/COUNTIF(A1:A1000,A1:A1000&""))-1

"Frank Kabel" wrote in message
...
Hi
IMHO this is not possible :-)
what is the exact formula you have entered and also have you change
the range A1:A1000 to your needs

--
Regards
Frank Kabel
Frankfurt, Germany

jmdaniel wrote:
just get a div/0 error...