ryan0521
Board Regular
- Joined
- Dec 7, 2016
- Messages
- 79
I have 3 sheets that contain data as shown below:
Posted sheet:
<colgroup><col><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>
Out Sheet:
<colgroup><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>
In Sheet:
<colgroup><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>
And then in the 4th sheet I made a formula in A3, on which it will check if the items listed in Posted sheet is also posted on Out sheet or In sheet, see my formula below, all I want is to simplify my formula because it's too long LOL.
=IF(Posted!A2<>"",IF(Posted!D2="In",IF(ISNUMBER(1/COUNTIFS(In!A:A,Posted!A2,In!K:K,Posted!C2,In!D:D,Posted!I2)),"",IF(ISNUMBER(1/COUNTIFS(In!A:A,Posted!A2,In!K:K,VALUE(Posted!C2)-1,In!D:D,Posted!I2)),"",IF(ISNUMBER(1/COUNTIFS(In!A:A,Posted!A2,In!K:K,Posted!C2,In!C:C,Posted!I2)),"",IF(ISNUMBER(1/COUNTIFS(In!A:A,Posted!A2,In!K:K,VALUE(Posted!C2)-1,In!C:C,Posted!I2)),"",IF(ISNUMBER(1/COUNTIFS(In!A:A,Posted!A2,In!K:K,Posted!C2,In!E:E,Posted!I2)),"",IF(ISNUMBER(1/COUNTIFS(In!A:A,Posted!A2,In!K:K,VALUE(Posted!C2)-1,In!E:E,Posted!I2)),"",Posted!A2)))))),IF(ISNUMBER(1/COUNTIFS(Out!A:A,Posted!A2,Out!J:J,Posted!C2,Out!D:D,Posted!I2)),"",IF(ISNUMBER(1/COUNTIFS(Out!A:A,Posted!A2,Out!J:J,VALUE(Posted!C2)-1,Out!D:D,Posted!I2)),"",IF(ISNUMBER(1/COUNTIFS(Out!A:A,Posted!A2,Out!J:J,Posted!C2,Out!C:C,Posted!I2)),"",IF(ISNUMBER(1/COUNTIFS(Out!A:A,Posted!A2,Out!J:J,VALUE(Posted!C2)-1,Out!C:C,Posted!I2)),"",IF(ISNUMBER(1/COUNTIFS(Out!A:A,Posted!A2,Out!J:J,Posted!C2,Out!E:E,Posted!I2)),"",IF(ISNUMBER(1/COUNTIFS(Out!A:A,Posted!A2,Out!J:J,VALUE(Posted!C2)-1,Out!E:E,Posted!I2)),"",Posted!A2))))))),"")
Please help me. Thank you so much.
Posted sheet:
Item Code | Name | Date | Trans. Type | Trans. # | UOM | Pack | Quantity | Total |
101 | Patty -Whopper | 3/30/2018 | Out | 18873 | CAS | 144 | 2 | 288 |
103 | Patty -Chicken Roya | 3/2/2018 | Out | 18646 | PCS | 1 | 36 | 36 |
103 | Patty -Chicken Roya | 3/6/2018 | In | 18690 | PCS | 1 | 36 | 36 |
103 | Patty -Chicken Roya | 3/9/2018 | In | 18721 | PCS | 1 | 48 | 48 |
<colgroup><col><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>
Out Sheet:
Aloha Code | Item Name | Req. Qty | Approved Qty | UOM | Requested By | Requested On | Approved On | Approved By | Released ON | Received On |
103 | Chk Royale - 144 Pcs | 432 | 3 | 36 | BK - Care Four | 3/2/2018 | 3/2/2018 | BK-Dareen | 3/2/2018 | 3/2/2018 |
103 | Chk Royale - 144 Pcs | 3456 | 24 | 288 | BK - Care Four | 3/21/2018 | 3/21/2018 | BK-Dareen | 3/21/2018 | 3/21/2018 |
<colgroup><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>
In Sheet:
Aloha Code | Item Name | Req. Qty | Approved Qty | UOM | Requested By | Requested On | Approved On | Approved By | Released ON | Received On |
103 | Chk Royale - 144 Pcs | 432 | 3 | 36 | BK-Dareen | 3/6/2018 | 3/6/2018 | BK - Care Four | 3/6/2018 | 3/6/2018 |
103 | Chk Royale - 144 Pcs | 576 | 4 | 48 | BK-Dareen | 3/6/2018 | 3/6/2018 | BK - Petromin Exit14 | 3/6/2018 | 3/8/2018 |
<colgroup><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>
And then in the 4th sheet I made a formula in A3, on which it will check if the items listed in Posted sheet is also posted on Out sheet or In sheet, see my formula below, all I want is to simplify my formula because it's too long LOL.
=IF(Posted!A2<>"",IF(Posted!D2="In",IF(ISNUMBER(1/COUNTIFS(In!A:A,Posted!A2,In!K:K,Posted!C2,In!D:D,Posted!I2)),"",IF(ISNUMBER(1/COUNTIFS(In!A:A,Posted!A2,In!K:K,VALUE(Posted!C2)-1,In!D:D,Posted!I2)),"",IF(ISNUMBER(1/COUNTIFS(In!A:A,Posted!A2,In!K:K,Posted!C2,In!C:C,Posted!I2)),"",IF(ISNUMBER(1/COUNTIFS(In!A:A,Posted!A2,In!K:K,VALUE(Posted!C2)-1,In!C:C,Posted!I2)),"",IF(ISNUMBER(1/COUNTIFS(In!A:A,Posted!A2,In!K:K,Posted!C2,In!E:E,Posted!I2)),"",IF(ISNUMBER(1/COUNTIFS(In!A:A,Posted!A2,In!K:K,VALUE(Posted!C2)-1,In!E:E,Posted!I2)),"",Posted!A2)))))),IF(ISNUMBER(1/COUNTIFS(Out!A:A,Posted!A2,Out!J:J,Posted!C2,Out!D:D,Posted!I2)),"",IF(ISNUMBER(1/COUNTIFS(Out!A:A,Posted!A2,Out!J:J,VALUE(Posted!C2)-1,Out!D:D,Posted!I2)),"",IF(ISNUMBER(1/COUNTIFS(Out!A:A,Posted!A2,Out!J:J,Posted!C2,Out!C:C,Posted!I2)),"",IF(ISNUMBER(1/COUNTIFS(Out!A:A,Posted!A2,Out!J:J,VALUE(Posted!C2)-1,Out!C:C,Posted!I2)),"",IF(ISNUMBER(1/COUNTIFS(Out!A:A,Posted!A2,Out!J:J,Posted!C2,Out!E:E,Posted!I2)),"",IF(ISNUMBER(1/COUNTIFS(Out!A:A,Posted!A2,Out!J:J,VALUE(Posted!C2)-1,Out!E:E,Posted!I2)),"",Posted!A2))))))),"")
Please help me. Thank you so much.