Status Report - Return incomplete Predecessors.

[Excel_Blonde]

Hi there,

I have my IT guru trying to create a report that will tell me if previous operations within a work structure have not been completed (Either Inventory or Pending status). I was wondering if any of you great people can help. For example from the data in the table below I would B/75 and B/76 to be returned. I believe they attempting this using SQL but are trying to get the formula correct in excel first but is just falling short. Any help would be greatly appreciated.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Job No[/TD]
[TD]Op No[/TD]
[TD]Predecessor[/TD]
[TD]Status[/TD]
[/TR]
[TR]
[TD]B/75[/TD]
[TD]10[/TD]
[TD][/TD]
[TD]Inventory[/TD]
[/TR]
[TR]
[TD]B/75[/TD]
[TD]20[/TD]
[TD]10[/TD]
[TD]Pending[/TD]
[/TR]
[TR]
[TD]B/75[/TD]
[TD]30[/TD]
[TD]20[/TD]
[TD]Complete[/TD]
[/TR]
[TR]
[TD]B/76[/TD]
[TD]10[/TD]
[TD][/TD]
[TD]Complete[/TD]
[/TR]
[TR]
[TD]B/76[/TD]
[TD]20[/TD]
[TD]10[/TD]
[TD]Inventory[/TD]
[/TR]
[TR]
[TD]B/76[/TD]
[TD]30[/TD]
[TD]20[/TD]
[TD]Pending[/TD]
[/TR]
[TR]
[TD]B/76[/TD]
[TD]40[/TD]
[TD]30[/TD]
[TD]Complete[/TD]
[/TR]
[TR]
[TD]B/76[/TD]
[TD]50[/TD]
[TD]40[/TD]
[TD]Pending[/TD]
[/TR]
[TR]
[TD]B/77[/TD]
[TD]10[/TD]
[TD][/TD]
[TD]Complete[/TD]
[/TR]
[TR]
[TD]B/77[/TD]
[TD]20[/TD]
[TD]10[/TD]
[TD]Complete[/TD]
[/TR]
[TR]
[TD]B/77[/TD]
[TD]30[/TD]
[TD]20[/TD]
[TD]Pending[/TD]
[/TR]
</tbody>[/TABLE]

 

[steve the fish]

Can you tell us in words the rule/s that get B/75 and B/76 returned based on that table?

 

[Excel_Blonde]

Can you tell us in words the rule/s that get B/75 and B/76 returned based on that table?

Hi Steve,

Thanks for your reply. We want to look at the Job No and the latest completed operation and return the Job No if any of the predecessors have a status of Inventory or Pending. I hope this answers your question.

 

[steve the fish]

I cant see any way to do that in one formula. Maybe someone else can but i cant. I can sort of help by using a helper formula:

In G2:

=IFERROR(INDEX($A$2:$A$12, MATCH(0,COUNTIF($G$1:G1, $A$2:$A$12), 0)),"")

Enter CTRL-SHIFT-ENTER and drag down until no new values appear.

In H2:

=IF(G2<>"",IF(SUM(COUNTIFS($A$2:$A$12,G2,$B$2:$B$12,"<>"&MAXIFS($B$2:$B$12,$A$2:$A$12,G2),$D$2:$D$12,{"Inventory","Pending"})),"Yes","No"),"")

This would then produce:

[TABLE="width: 195"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]B/75[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD]B/76[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD]B/77[/TD]
[TD]No
[/TD]
[/TR]
</tbody>[/TABLE]

Im not sure if that is helpful or not.

 

[Excel_Blonde]

I cant see any way to do that in one formula. Maybe someone else can but i cant. I can sort of help by using a helper formula:

In G2:

=IFERROR(INDEX($A$2:$A$12, MATCH(0,COUNTIF($G$1:G1, $A$2:$A$12), 0)),"")

Enter CTRL-SHIFT-ENTER and drag down until no new values appear.

In H2:

=IF(G2<>"",IF(SUM(COUNTIFS($A$2:$A$12,G2,$B$2:$B$12,"<>"&MAXIFS($B$2:$B$12,$A$2:$A$12,G2),$D$2:$D$12,{"Inventory","Pending"})),"Yes","No"),"")

This would then produce:

[TABLE="width: 195"]
<tbody>[TR]
[TD]B/75[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD]B/76[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD]B/77[/TD]
[TD]No[/TD]
[/TR]
</tbody>[/TABLE]

Im not sure if that is helpful or not.

Thank you. I'll pass it over and see how we get on.

 

[Excel_Blonde]

Thank you. I'll pass it over and see how we get on.

No unfortunately this isn't quite what I'm looking for. Thank you very much for trying though.

This only looks at anything Inventory or Pending but these in most cases will be correct. Its only where complete appears after the inventory or pending that I want it to be highlighted.
I will keep going and update if I ever come up with a solution

 

[Excel_Blonde]

No unfortunately this isn't quite what I'm looking for. Thank you very much for trying though.

This only looks at anything Inventory or Pending but these in most cases will be correct. Its only where complete appears after the inventory or pending that I want it to be highlighted.
I will keep going and update if I ever come up with a solution

I'm still struggling with this one. I've tried all sorts but not getting anywhere. Can anyone help??

 

[Fluff]

How about

Excel 2013/2016
	A	B	C	D	E
1	Job No	Op No	Predecessor	Status
2	B/75	10		Inventory	Wrong
3	B/75	20	10	Pending	Wrong
4	B/75	30	20	Complete
5	B/76	10		Complete	Wrong
6	B/76	20	10	Inventory	Wrong
7	B/76	30	20	Pending	Wrong
8	B/76	40	30	Complete	Ok
9	B/76	50	40	Pending
10	B/77	10		Complete	Ok
11	B/77	20	10	Complete	Ok
12	B/77	30	20	Pending
Proposal

Cell Formulas
Range	Formula
E2	=IF(A3=A2,IF(AND(D3="Complete",D2<>"Complete"),"Wrong",IF(E3="Wrong","Wrong","Ok")),"")

 

[Excel_Blonde]

How about

Excel 2013/2016

	A	B	C	D	E
Job No	Op No	Predecessor	Status
B/75	Inventory	Wrong
B/75	Pending	Wrong
B/75	Complete
B/76	Complete	Wrong
B/76	Inventory	Wrong
B/76	Pending	Wrong
B/76	Complete	Ok
B/76	Pending
B/77	Complete	Ok
B/77	Complete	Ok
B/77	Pending

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]10[/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]20[/TD]
[TD="align: right"]10[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]30[/TD]
[TD="align: right"]20[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]10[/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]20[/TD]
[TD="align: right"]10[/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]30[/TD]
[TD="align: right"]20[/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]40[/TD]
[TD="align: right"]30[/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]50[/TD]
[TD="align: right"]40[/TD]

[TD="align: center"]10[/TD]

[TD="align: right"]10[/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]

[TD="align: right"]20[/TD]
[TD="align: right"]10[/TD]

[TD="align: center"]12[/TD]

[TD="align: right"]30[/TD]
[TD="align: right"]20[/TD]

</tbody>

Proposal​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E2[/TH]
[TD="align: left"]=IF(A3=A2,IF(AND(D3="Complete",D2<>"Complete"),"Wrong",IF(E3="Wrong","Wrong","Ok")),"")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Thank you for your suggestion. But this doesn't quite work. Row 2 and 3 should be ok and row 4 should be wrong. Row 5,6 & 7 should ok but row 8 should be wrong. Rows 10 and 11 are correct.

I have found a work around though:

In Column E - =IF(AND(D2="Complete",OR(D1="Iventory",D1="Pending")),"Yes","No")
In Column F - =IF(B2<B1,"No",E2)

I should then be able to pull the job number from the Yes results in Column E. I'm just waiting for someone to double check this for me.

Thank you for your responses.