Data Seperation.

[nazeem]

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

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

<tbody>
</tbody>

 

[vicedo]

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

23/01/2016	CASH RECEIPT CASH DEPOSIT- CASH DEPOSIT- Value DATE, 23/01/2016 - EN21338	1505230116
25/01/2016	CASH RECEIPT CASH DEPOSIT- CASH DEPOSIT- Value DATE, 25/01/2016 - EN34426	1100250116

<tbody>
</tbody>

 

[nazeem]

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

23/01/2016	CASH RECEIPT CASH DEPOSIT- CASH DEPOSIT- Value DATE, 23/01/2016 - EN21338	1505230116
25/01/2016	CASH RECEIPT CASH DEPOSIT- CASH DEPOSIT- Value DATE, 25/01/2016 - EN34426	1100250116

<tbody>
</tbody>

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.