Status Report - Return incomplete Predecessors.

Excel_Blonde

New Member
Joined
Aug 8, 2018
Messages
44
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.

Job NoOp NoPredecessorStatus
B/7510Inventory
B/752010Pending
B/753020Complete
B/7610Complete
B/762010Inventory
B/763020Pending
B/764030Complete
B/765040Pending
B/7710Complete
B/772010Complete
B/773020Pending

<tbody>
</tbody>
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
Can you tell us in words the rule/s that get B/75 and B/76 returned based on that table?
 
Upvote 0
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.
 
Upvote 0
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:

B/75Yes
B/76Yes
B/77No

<colgroup><col><col></colgroup><tbody>
</tbody>

Im not sure if that is helpful or not.
 
Upvote 0
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:

B/75Yes
B/76Yes
B/77No

<tbody>
</tbody>

Im not sure if that is helpful or not.


Thank you. I'll pass it over and see how we get on. :)
 
Upvote 0
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 :LOL::confused:
 
Upvote 0
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 :LOL::confused:

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


Excel 2013/2016
ABCDE
1Job NoOp NoPredecessorStatus
2B/7510InventoryWrong
3B/752010PendingWrong
4B/753020Complete
5B/7610CompleteWrong
6B/762010InventoryWrong
7B/763020PendingWrong
8B/764030CompleteOk
9B/765040Pending
10B/7710CompleteOk
11B/772010CompleteOk
12B/773020Pending
Proposal
Cell Formulas
RangeFormula
E2=IF(A3=A2,IF(AND(D3="Complete",D2<>"Complete"),"Wrong",IF(E3="Wrong","Wrong","Ok")),"")
 
Upvote 0
How about

Excel 2013/2016
ABCDE
1Job NoOp NoPredecessorStatus
2B/7510InventoryWrong
3B/752010PendingWrong
4B/753020Complete
5B/7610CompleteWrong
6B/762010InventoryWrong
7B/763020PendingWrong
8B/764030CompleteOk
9B/765040Pending
10B/7710CompleteOk
11B/772010CompleteOk
12B/773020Pending

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Proposal

Worksheet Formulas
CellFormula
E2=IF(A3=A2,IF(AND(D3="Complete",D2<>"Complete"),"Wrong",IF(E3="Wrong","Wrong","Ok")),"")

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>


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. :)
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,215
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top