Please help me simplify this one.

[ryan0521]

I have 3 sheets that contain data as shown below:

Posted sheet:
[TABLE="width: 660"]
<colgroup><col><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Item Code[/TD]
[TD]Name[/TD]
[TD]Date[/TD]
[TD]Trans. Type[/TD]
[TD]Trans. #[/TD]
[TD]UOM[/TD]
[TD]Pack[/TD]
[TD]Quantity[/TD]
[TD]Total[/TD]
[/TR]
[TR]
[TD]101[/TD]
[TD]Patty -Whopper [/TD]
[TD]3/30/2018[/TD]
[TD]Out[/TD]
[TD]18873[/TD]
[TD]CAS[/TD]
[TD]144[/TD]
[TD]2[/TD]
[TD]288[/TD]
[/TR]
[TR]
[TD]103[/TD]
[TD]Patty -Chicken Roya [/TD]
[TD]3/2/2018[/TD]
[TD]Out[/TD]
[TD]18646[/TD]
[TD]PCS[/TD]
[TD]1[/TD]
[TD]36[/TD]
[TD]36[/TD]
[/TR]
[TR]
[TD]103[/TD]
[TD]Patty -Chicken Roya [/TD]
[TD]3/6/2018[/TD]
[TD]In[/TD]
[TD]18690[/TD]
[TD]PCS[/TD]
[TD]1[/TD]
[TD]36[/TD]
[TD]36[/TD]
[/TR]
[TR]
[TD]103[/TD]
[TD]Patty -Chicken Roya [/TD]
[TD]3/9/2018[/TD]
[TD]In[/TD]
[TD]18721[/TD]
[TD]PCS[/TD]
[TD]1[/TD]
[TD]48[/TD]
[TD]48[/TD]
[/TR]
</tbody>[/TABLE]


Out Sheet:
[TABLE="width: 1041"]
<colgroup><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Aloha Code[/TD]
[TD]Item Name[/TD]
[TD]Req. Qty[/TD]
[TD]Approved Qty[/TD]
[TD]UOM[/TD]
[TD]Requested By[/TD]
[TD]Requested On[/TD]
[TD]Approved On[/TD]
[TD]Approved By[/TD]
[TD]Released ON[/TD]
[TD]Received On[/TD]
[/TR]
[TR]
[TD]103[/TD]
[TD]Chk Royale - 144 Pcs[/TD]
[TD]432[/TD]
[TD]3[/TD]
[TD]36[/TD]
[TD]BK - Care Four[/TD]
[TD]3/2/2018[/TD]
[TD]3/2/2018[/TD]
[TD]BK-Dareen[/TD]
[TD]3/2/2018[/TD]
[TD]3/2/2018[/TD]
[/TR]
[TR]
[TD]103[/TD]
[TD]Chk Royale - 144 Pcs[/TD]
[TD]3456[/TD]
[TD]24[/TD]
[TD]288[/TD]
[TD]BK - Care Four[/TD]
[TD]3/21/2018[/TD]
[TD]3/21/2018[/TD]
[TD]BK-Dareen[/TD]
[TD]3/21/2018[/TD]
[TD]3/21/2018[/TD]
[/TR]
</tbody>[/TABLE]


In Sheet:
[TABLE="width: 1096"]
<colgroup><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Aloha Code[/TD]
[TD]Item Name[/TD]
[TD]Req. Qty[/TD]
[TD]Approved Qty[/TD]
[TD]UOM[/TD]
[TD]Requested By[/TD]
[TD]Requested On[/TD]
[TD]Approved On[/TD]
[TD]Approved By[/TD]
[TD]Released ON[/TD]
[TD]Received On[/TD]
[/TR]
[TR]
[TD]103[/TD]
[TD]Chk Royale - 144 Pcs[/TD]
[TD]432[/TD]
[TD]3[/TD]
[TD]36[/TD]
[TD]BK-Dareen[/TD]
[TD]3/6/2018[/TD]
[TD]3/6/2018[/TD]
[TD]BK - Care Four[/TD]
[TD]3/6/2018[/TD]
[TD]3/6/2018[/TD]
[/TR]
[TR]
[TD]103[/TD]
[TD]Chk Royale - 144 Pcs[/TD]
[TD]576[/TD]
[TD]4[/TD]
[TD]48[/TD]
[TD]BK-Dareen[/TD]
[TD]3/6/2018[/TD]
[TD]3/6/2018[/TD]
[TD]BK - Petromin Exit14[/TD]
[TD]3/6/2018[/TD]
[TD]3/8/2018[/TD]
[/TR]
</tbody>[/TABLE]


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.

 

[ryan0521]

I also make an additional 1 day for Date in posted.