Data Seperation.

[nazeem]

It would be helpful if you can find a formula for these lines.

[TABLE="class: cms_table, width: 1218"]
<tbody>[TR]
[TD]Date[/TD]
[TD]Particulars[/TD]
[TD]Amount[/TD]
[TD]Result[/TD]
[/TR]
[TR]
[TD="align: right"]2/1/2016[/TD]
[TD]SB-TRANSFER 44342960 SB REF. 7038594 B/O NETWORK INTERNATIONAL LLC NI POS SETT 010116 109866251 Value DATE, 02/01/2016 - S11702254[/TD]
[TD="align: right"]619.6[/TD]
[TD="align: right"]109866251[/TD]
[/TR]
[TR]
[TD="align: right"]2/1/2016[/TD]
[TD]SB-TRANSFER 44342961 SB REF. 7038594 B/O NETWORK INTERNATIONAL LLC NI POS SETT 010116 109866269 Value DATE, 02/01/2016 - S11702259[/TD]
[TD="align: right"]3,710.82[/TD]
[TD="align: right"]109866269[/TD]
[/TR]
[TR]
[TD="align: right"]3/1/2016[/TD]
[TD]SB-TRANSFER 44366043 SB REF. 7041502 B/O NETWORK INTERNATIONAL LLC NI POS SETT 020116 109866079 Value DATE, 03/01/2016 - S12114960[/TD]
[TD="align: right"]1,055.01[/TD]
[TD="align: right"]109866079[/TD]
[/TR]
[TR]
[TD="align: right"]10/1/2016[/TD]
[TD]003769 INWARD CLEARING CHQ. NO:003769 Value DATE, 10/01/2016 - S14981635[/TD]
[TD="align: right"]1,365.00[/TD]
[TD="align: right"]3769[/TD]
[/TR]
[TR]
[TD]16/01/2016[/TD]
[TD]003770 INWARD CLEARING CHQ. NO:003770 Value DATE, 16/01/2016 - S17213951[/TD]
[TD="align: right"]2,865.00[/TD]
[TD="align: right"]3770[/TD]
[/TR]
[TR]
[TD]23/01/2016[/TD]
[TD]CASH RECEIPT CASH DEPOSIT- CASH DEPOSIT- Value DATE, 23/01/2016 - EN21338[/TD]
[TD="align: right"]1,505.00[/TD]
[TD="align: right"]1505230116[/TD]
[/TR]
[TR]
[TD]25/01/2016[/TD]
[TD]CASH RECEIPT CASH DEPOSIT- CASH DEPOSIT- Value DATE, 25/01/2016 - EN34426[/TD]
[TD="align: right"]1,100.00[/TD]
[TD="align: right"]1100250116[/TD]
[/TR]
[TR]
[TD="align: right"]3/1/2016[/TD]
[TD]SB-TRANSFER 44352901 SB REF. 7038604 B/O NETWORK INTERNATIONAL LLC DCC REVENUE 311215 109943035 Value DATE, 02/01/2016 - S11718081[/TD]
[TD="align: right"]11.23[/TD]
[TD="align: right"]109943035[/TD]
[/TR]
</tbody>[/TABLE]

 

[vicedo]

Are there any explanations as to where this result came from based on the data.

[TABLE="class: grid, width: 800"]
<tbody>[TR]
[TD]23/01/2016
[/TD]
[TD]CASH RECEIPT CASH DEPOSIT- CASH DEPOSIT- Value DATE, 23/01/2016 - EN21338
[/TD]
[TD]1505230116
[/TD]
[/TR]
[TR]
[TD]25/01/2016
[/TD]
[TD]CASH RECEIPT CASH DEPOSIT- CASH DEPOSIT- Value DATE, 25/01/2016 - EN34426
[/TD]
[TD]1100250116
[/TD]
[/TR]
</tbody>[/TABLE]

 

[nazeem]

Are there any explanations as to where this result came from based on the data.

[TABLE="class: grid, width: 800"]
<tbody>[TR]
[TD]23/01/2016[/TD]
[TD]CASH RECEIPT CASH DEPOSIT- CASH DEPOSIT- Value DATE, 23/01/2016 - EN21338[/TD]
[TD]1505230116[/TD]
[/TR]
[TR]
[TD]25/01/2016[/TD]
[TD]CASH RECEIPT CASH DEPOSIT- CASH DEPOSIT- Value DATE, 25/01/2016 - EN34426[/TD]
[TD]1100250116[/TD]
[/TR]
</tbody>[/TABLE]

Amount column + Date Column. 1505+230116 = 1505230116

So as to identify the amount from which date we have got credit.

 

[vicedo]

put the formula below in your result column, then format the date column as dd/mm/yy

=IF(ISNUMBER(FIND(":",B2)),VALUE(MID(B2,FIND(":",B2)+1,7)),IF(ISERR(VALUE(MID(B2,FIND("Value",B2)-10,10))),ROUND(C2,0)&SUBSTITUTE(T(TEXT(A2,"dd/mm/yy")),"/",""),VALUE(MID(B2,FIND("Value",B2)-10,10))))

 

[nazeem]

put the formula below in your result column, then format the date column as dd/mm/yy

=IF(ISNUMBER(FIND(":",B2)),VALUE(MID(B2,FIND(":",B2)+1,7)),IF(ISERR(VALUE(MID(B2,FIND("Value",B2)-10,10))),ROUND(C2,0)&SUBSTITUTE(T(TEXT(A2,"dd/mm/yy")),"/",""),VALUE(MID(B2,FIND("Value",B2)-10,10))))

Thanks a lot. It works.