Formula help please

[Joe Gieder]

First thank you for looking at this and sorry for the long post and formula,
if it's not clear please let me know.

I'm trying to figure out how to re-write the below array formula:

=COUNT(1/FREQUENCY(IF(LEFT('[Spares Quotes List V22
26Apr2007.xls]All'!$A$2:$A$5003,4)=RIGHT($A41,4),IF('[Spares Quotes List V22
26Apr2007.xls]All'!$F$2:$F$5003<>"",IF('[Spares Quotes List V22
26Apr2007.xls]All'!$P$2:$P$5003<>"",MATCH('[Spares Quotes List V22
26Apr2007.xls]All'!$P$2:$P$5003,'[Spares Quotes List V22
26Apr2007.xls]All'!$P$2:$P$5003,0)))),ROW('[Spares Quotes List V22
26Apr2007.xls]All'!$P$2:$P$5003)-ROW('[Spares Quotes List V22
26Apr2007.xls]All'!$P$2)+1))

What this formula does is compares the id number in column A on both
worksheets and then looks at column F to make sure it's not blank then counts
all the unique supplier names in column P that aren't blank. The problem I
have just run into is say the supplier has 3 parts and only prices 2 of them,
the formula counts the supplier as complete when they aren't. Can this
formula be written a different way to still only count the unique supplier
names but have the criteria be to count them only if all parts are quoted?

Thank you in advance for your help and sorry for the long post.

Joe

 

[Bob Phillips]

I don't quite understand. If F or P are blank, it doesn't count as an
instance, so what are blank in your examples that get counted?

--
HTH

Bob

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

 

[Joe Gieder]

Thank you for looking at this formula. I hope I have helped answer your
question.
Say a supplier has 5 parts that they need to provide a price for and they
only provide a quote on 1 of them, the way the formula works now is that once
there is a price any one of those parts the formula counts that supplier as
having fulfilled their obligation when they realy haven't. I'm trying to see
if the formula can be written to ignore that supplier unless all the parts
have been quoted.

Supplier Price
abc 1
abc 5
abc
def 10
def 25
would like the formula to say 1 supplier left open
Total suppliers = 2 (differnt formula)
Suppliers fulfilling their quotes = 1
Supliers left to quote = 1

Currently the formula equals
Suppliers fulfilling their quotes = 2

I don't quite understand. If F or P are blank, it doesn't count as an
instance, so what are blank in your examples that get counted?

--
HTH

Bob

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

First thank you for looking at this and sorry for the long post and
formula,
if it's not clear please let me know.

I'm trying to figure out how to re-write the below array formula:

=COUNT(1/FREQUENCY(IF(LEFT('[Spares Quotes List V22
26Apr2007.xls]All'!$A$2:$A$5003,4)=RIGHT($A41,4),IF('[Spares Quotes List
V22
26Apr2007.xls]All'!$F$2:$F$5003<>"",IF('[Spares Quotes List V22
26Apr2007.xls]All'!$P$2:$P$5003<>"",MATCH('[Spares Quotes List V22
26Apr2007.xls]All'!$P$2:$P$5003,'[Spares Quotes List V22
26Apr2007.xls]All'!$P$2:$P$5003,0)))),ROW('[Spares Quotes List V22
26Apr2007.xls]All'!$P$2:$P$5003)-ROW('[Spares Quotes List V22
26Apr2007.xls]All'!$P$2)+1))

What this formula does is compares the id number in column A on both
worksheets and then looks at column F to make sure it's not blank then
counts
all the unique supplier names in column P that aren't blank. The problem I
have just run into is say the supplier has 3 parts and only prices 2 of
them,
the formula counts the supplier as complete when they aren't. Can this
formula be written a different way to still only count the unique supplier
names but have the criteria be to count them only if all parts are quoted?

Thank you in advance for your help and sorry for the long post.

Joe

 

[Bob Phillips]

But that is where I disagree with you, or don't understand, because that
formula counts the number of different values in column P for a given value
in A. Your latest example suddenly ignores P. But as I said, If F or P are
blank, it doesn't count as an instance to be counted anyway.
--
HTH

Bob

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

Thank you for looking at this formula. I hope I have helped answer your
question.
Say a supplier has 5 parts that they need to provide a price for and they
only provide a quote on 1 of them, the way the formula works now is that
once
there is a price any one of those parts the formula counts that supplier
as
having fulfilled their obligation when they realy haven't. I'm trying to
see
if the formula can be written to ignore that supplier unless all the parts
have been quoted.

Supplier Price
abc 1
abc 5
abc
def 10
def 25
would like the formula to say 1 supplier left open
Total suppliers = 2 (differnt formula)
Suppliers fulfilling their quotes = 1
Supliers left to quote = 1

Currently the formula equals
Suppliers fulfilling their quotes = 2

I don't quite understand. If F or P are blank, it doesn't count as an
instance, so what are blank in your examples that get counted?

--
HTH

Bob

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

First thank you for looking at this and sorry for the long post and
formula,
if it's not clear please let me know.

I'm trying to figure out how to re-write the below array formula:

=COUNT(1/FREQUENCY(IF(LEFT('[Spares Quotes List V22
26Apr2007.xls]All'!$A$2:$A$5003,4)=RIGHT($A41,4),IF('[Spares Quotes
List
V22
26Apr2007.xls]All'!$F$2:$F$5003<>"",IF('[Spares Quotes List V22
26Apr2007.xls]All'!$P$2:$P$5003<>"",MATCH('[Spares Quotes List V22
26Apr2007.xls]All'!$P$2:$P$5003,'[Spares Quotes List V22
26Apr2007.xls]All'!$P$2:$P$5003,0)))),ROW('[Spares Quotes List V22
26Apr2007.xls]All'!$P$2:$P$5003)-ROW('[Spares Quotes List V22
26Apr2007.xls]All'!$P$2)+1))

What this formula does is compares the id number in column A on both
worksheets and then looks at column F to make sure it's not blank then
counts
all the unique supplier names in column P that aren't blank. The
problem I
have just run into is say the supplier has 3 parts and only prices 2 of
them,
the formula counts the supplier as complete when they aren't. Can this
formula be written a different way to still only count the unique
supplier
names but have the criteria be to count them only if all parts are
quoted?

Thank you in advance for your help and sorry for the long post.

Joe

 

[Joe Gieder]

You are absolutly correct, the formula does not count the blanks. I just have
a hard time explaining what's going on but know what's happening. I think
part of the problem lies within the MATCH part of the formula since it looks
for the first match where both coulmn F and P are not blank and counts it.
What I'm tying to do is look in column A for the matching ID number then look
in column P for the suppliers matching the ID in column A (A is used like a
filter) then look in column F to see if there are any blanks corresponding to
the supplier in column P. The supplier name can appear multiple times (no
matter how many times the same name appears it's only counted once) sometimes
a price will be in column F and sometimes there wont be. If all the same
supplier names have a value in column F have a price I want to count it if
any one of them are blank I don't. What is happening is the supplier is
getting counted even when there's no value in one of the corresponding column
F cells.

A F P
001 1 abc
001 2 abc
001 5 abc
001 2 def
001 def
001 1 ghi
002 10 abc
003 20 abc
the ID to use 001 and the result should show 2 because supplier def has a
blank in coulmn F even though there's a value with the other def.

I hope this helps.
Thanks Joe

But that is where I disagree with you, or don't understand, because that
formula counts the number of different values in column P for a given value
in A. Your latest example suddenly ignores P. But as I said, If F or P are
blank, it doesn't count as an instance to be counted anyway.
--
HTH

Bob

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

Thank you for looking at this formula. I hope I have helped answer your
question.
Say a supplier has 5 parts that they need to provide a price for and they
only provide a quote on 1 of them, the way the formula works now is that
once
there is a price any one of those parts the formula counts that supplier
as
having fulfilled their obligation when they realy haven't. I'm trying to
see
if the formula can be written to ignore that supplier unless all the parts
have been quoted.

Supplier Price
abc 1
abc 5
abc
def 10
def 25
would like the formula to say 1 supplier left open
Total suppliers = 2 (differnt formula)
Suppliers fulfilling their quotes = 1
Supliers left to quote = 1

Currently the formula equals
Suppliers fulfilling their quotes = 2

I don't quite understand. If F or P are blank, it doesn't count as an
instance, so what are blank in your examples that get counted?

--
HTH

Bob

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

First thank you for looking at this and sorry for the long post and
formula,
if it's not clear please let me know.

I'm trying to figure out how to re-write the below array formula:

=COUNT(1/FREQUENCY(IF(LEFT('[Spares Quotes List V22
26Apr2007.xls]All'!$A$2:$A$5003,4)=RIGHT($A41,4),IF('[Spares Quotes
List
V22
26Apr2007.xls]All'!$F$2:$F$5003<>"",IF('[Spares Quotes List V22
26Apr2007.xls]All'!$P$2:$P$5003<>"",MATCH('[Spares Quotes List V22
26Apr2007.xls]All'!$P$2:$P$5003,'[Spares Quotes List V22
26Apr2007.xls]All'!$P$2:$P$5003,0)))),ROW('[Spares Quotes List V22
26Apr2007.xls]All'!$P$2:$P$5003)-ROW('[Spares Quotes List V22
26Apr2007.xls]All'!$P$2)+1))

What this formula does is compares the id number in column A on both
worksheets and then looks at column F to make sure it's not blank then
counts
all the unique supplier names in column P that aren't blank. The
problem I
have just run into is say the supplier has 3 parts and only prices 2 of
them,
the formula counts the supplier as complete when they aren't. Can this
formula be written a different way to still only count the unique
supplier
names but have the criteria be to count them only if all parts are
quoted?

Thank you in advance for your help and sorry for the long post.

Joe