Calculating Service Fill Rate

[Mike JF]

Dear Expert
I am a new to excel and i need to calculate the fill rate using the below
data.
My data is large , est around 2000 SKU lines
I try search from the post here, but no luck.
Appreciate your expertise.

Data Sample

Plate No. Date Ord.Ord No. SKU. Supp.
ABC123,12/1/08,123,Code1,Yes
ABC123,12/01/08,123,Code2,No
ABC124,13/01/08,125,Code3,Yes
ABC124,13/01/08,125,Code4,Yes
ABC125,13/01/08,126,Code5,Yes

I wish to get the answer how Plate No. with full parts supplied (yes) in
this case Ans: is 2 (ABC124 and ABC125) In addtion i may need to select by
date range.
Thank You
Mike

 

[Fred Smith]

You'll need to provide more information. Why isn't the answer 3? ABC123 has
"Yes" in the row.

Regards,
Fred

 

[Mike JF]

Hi Fred
apologize for not been clear.
The reason because ABC123 require 2 lines of SKU, but only one SKU is
supllied,
so ABC123 is not counted as "Yes".

In summary, if one vehicle require 10 lines of SKU and all 10 lines SKU are
supplied, then status is "yes"
Hope i explained well.
Thank You

 

[Ashish Mathur]

Hi,

Try this approach.

1. Highlight the range (incl. header row) go to Data > Filter > Advanced
Filter
2. Select Copy to another location
3. In the list box, select D4:E9 (row 4 is the header row)
4. Leave criteria blank
5. In the copy to box, select any one blank cell, say I5
6. Check the box for unique records only
7. Assume that the output (incl. the header row) is in range I5:J9
8. Now in cell E14, type condition and in cell F14, type the heading of the
first column I.e. Plate No.
9. In cell E15, type
=COUNTIF($I$6:$I$9,I6)=SUMPRODUCT(($I$6:$I$9=I6)*($J$6:$J$9="Yes"))
10. In cell F15, type =DCOUNTA(I5:J9,F14,E14:E15). This should get the
answer

Hope this helps.
--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[Mike JF]

Hi Mathur,
I followed your instruction but not getting the answer. In cell e15, i shows
false, and in cell f15 shows 0. The correct answer shld be 2. In additional i
also need to select by date range.

btw i notice the last part of this array (J6:J9="yes"), but actually these
range (j6:J9) are dates in the sheet
=COUNTIF($I$6:$I$9,I6)=SUMPRODUCT(($I$6:$I$9=I6)*($J$6:$J$9="Yes"))

Thank

 

[Ashish Mathur]

Hi,

Changed the reference to the column which has Yes I.e. if Yes, No appears in
column L, then in the sumproduct formula, the range should be
($L$6:$L$9="Yes")

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com