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>
 

Some videos you may like

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,110
Office Version
  1. 365
Platform
  1. Windows
Can you tell us in words the rule/s that get B/75 and B/76 returned based on that table?
 

Excel_Blonde

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

Well-known Member
Joined
Oct 20, 2009
Messages
8,110
Office Version
  1. 365
Platform
  1. Windows
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.
 

Excel_Blonde

New Member
Joined
Aug 8, 2018
Messages
44

ADVERTISEMENT

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

Excel_Blonde

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

Excel_Blonde

New Member
Joined
Aug 8, 2018
Messages
44

ADVERTISEMENT

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??
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,474
Office Version
  1. 365
Platform
  1. Windows
How about

<b>Excel 2013/2016</b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style="font-weight: bold;;">Job No</td><td style="font-weight: bold;;">Op No</td><td style="font-weight: bold;;">Predecessor</td><td style="font-weight: bold;;">Status</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">B/75</td><td style="text-align: right;;">10</td><td style="text-align: right;;"></td><td style=";">Inventory</td><td style=";">Wrong</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">B/75</td><td style="text-align: right;;">20</td><td style="text-align: right;;">10</td><td style=";">Pending</td><td style=";">Wrong</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style=";">B/75</td><td style="text-align: right;;">30</td><td style="text-align: right;;">20</td><td style=";">Complete</td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style=";">B/76</td><td style="text-align: right;;">10</td><td style="text-align: right;;"></td><td style=";">Complete</td><td style=";">Wrong</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style=";">B/76</td><td style="text-align: right;;">20</td><td style="text-align: right;;">10</td><td style=";">Inventory</td><td style=";">Wrong</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style=";">B/76</td><td style="text-align: right;;">30</td><td style="text-align: right;;">20</td><td style=";">Pending</td><td style=";">Wrong</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style=";">B/76</td><td style="text-align: right;;">40</td><td style="text-align: right;;">30</td><td style=";">Complete</td><td style=";">Ok</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style=";">B/76</td><td style="text-align: right;;">50</td><td style="text-align: right;;">40</td><td style=";">Pending</td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style=";">B/77</td><td style="text-align: right;;">10</td><td style="text-align: right;;"></td><td style=";">Complete</td><td style=";">Ok</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">11</td><td style=";">B/77</td><td style="text-align: right;;">20</td><td style="text-align: right;;">10</td><td style=";">Complete</td><td style=";">Ok</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">12</td><td style=";">B/77</td><td style="text-align: right;;">30</td><td style="text-align: right;;">20</td><td style=";">Pending</td><td style=";"></td></tr></tbody></table><p style="width:6.4em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Proposal</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">E2</th><td style="text-align:left">=IF(<font color="Blue">A3=A2,IF(<font color="Red">AND(<font color="Green">D3="Complete",D2<>"Complete"</font>),"Wrong",IF(<font color="Green">E3="Wrong","Wrong","Ok"</font>)</font>),""</font>)</td></tr></tbody></table></td></tr></table><br />
 

Excel_Blonde

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

Watch MrExcel Video

Forum statistics

Threads
1,109,251
Messages
5,527,636
Members
409,778
Latest member
MagalieD

This Week's Hot Topics

Top