Dear All, I'm seeking your help to figure out how to automate this problem.
problem Description:

Document ID = Column B + Column C+ Column D
Example : Document ID = 2985D-02CG-IRF-00008
*
What I want to Automate
*
For Column "K"
First Case
If Document is submitted for the first time and returned directly with Code "A" then the status is closed.
Second Case
If Document is submitted for the first time (Rev 0) and returned with Code "B, C, or D" then the status is Open. It shall be Re-submitted again. When Re-submitting this document for the second time (Rev 1), the previous status of the first submission (Rev 0) shall be changed automatically to "Responded". And So on for Rev 2, 3 if any.

Transmittal Doc CAT TYPE REV Submission CNSLT CNSLT Code Status
Number ID Date to CNSLT Reply date transmittal
No
2985D 02CG IRF-00008 0 2/20/2013 2/25/2014 2985D-TS-CNSLT-CNT-0402 B Responded
2985D-TS-CNT-CNSLT-1386
2985D 02CG IRF-00008 1 11/25/2013 12/24/2013 2985D-TS-CNSLT-CNT-0305 A Closed
2985D-TS-CNT-CNSLT-1293
2985D 02DF IRF-00055 0 11/25/2013 2/3/2014 2985D-TS-CNSLT-CNT-0364 B Responded
2985D-TS-CNT-CNSLT-1264
2985D 08SA IRF-00187 0 11/25/2014 11/30/2014 2985D-TS-CNSLT-CNT-0387 B Open
2985D-TS-CNT-CNSLT-1784
2985D 02DF IRF-00055 1 1/4/2014 6/4/2014 2985D-TS-CNSLT-CNT-0320 B Responded
2985D-TS-CNT-CNSLT-1664
2985D 03UE IRF-00957 0 6/4/2014 10/4/2014 2985D-TS-CNSLT-CNT-0374 A Closed
2985D-TS-CNT-CNSLT-1241
2985D 02DF IRF-00055 2 15/4/2014 20/4/2014 2985D-TS-CNSLT-CNT-0390 A Closed
2985D-TS-CNT-CNSLT-1237
2985D 00PS IRF-00772 0 19/5/2014 29/5/2014 2985D-TS-CNSLT-CNT-0337 B Open
2985D-TS-CNT-CNSLT-1294


Can someone help me with this, In a Matter of Fact, this is not the only request that I need, But at the beginning, I'd appreciate if someone can help me with this code, Thanks for your time in advance.