Hello everyone,
I am coming across with coming up a formula to summarize the description.
basically I would like to find the Serial number in column A. If it is other or reserve, then no further steps to be taken, other than that, I would like to find the respective payment type, period start, period and in column K,L,M and retrieve the value in column F.
the lines marked in red are the expect results.
Thank you very much in advance for your help!
Let me know if you Need further clarification.
Best regards
[TABLE="width: 1376"]
<tbody>[TR]
[TD]Serial No.[/TD]
[TD]Month[/TD]
[TD]Invoice Type[/TD]
[TD]Invoice No.[/TD]
[TD]Supplier[/TD]
[TD]Description[/TD]
[TD]Amount
gross[/TD]
[TD]Tax [/TD]
[TD]Tax amount
amount[/TD]
[TD]Net Amount[/TD]
[TD]Payment type[/TD]
[TD]Period Start[/TD]
[TD]Period End[/TD]
[/TR]
[TR]
[TD="align: right"]7522[/TD]
[TD="align: right"]jun-18[/TD]
[TD]Payment[/TD]
[TD="align: right"]84445[/TD]
[TD]GE[/TD]
[TD]Assessment_fee_01.04-30.04.2018_SN_7522[/TD]
[TD] $ 71.000,00 [/TD]
[TD="align: right"]0[/TD]
[TD]$ - [/TD]
[TD] $ 71.000,00 [/TD]
[TD]Assessment_fee[/TD]
[TD]01.04[/TD]
[TD]30.04.2018[/TD]
[/TR]
[TR]
[TD="align: right"]7523[/TD]
[TD="align: right"]jun-18[/TD]
[TD]Payment[/TD]
[TD="align: right"]84446[/TD]
[TD]GE[/TD]
[TD]Evaluation_fee_01.05-31.05.2018_SN_7523[/TD]
[TD] $ 45.226,83 [/TD]
[TD="align: right"]0[/TD]
[TD]$ - [/TD]
[TD] $ 45.226,83 [/TD]
[TD]Evaluation_fee[/TD]
[TD]01.05[/TD]
[TD]31.05.2018[/TD]
[/TR]
[TR]
[TD="align: right"]7524[/TD]
[TD="align: right"]jun-18[/TD]
[TD]Payment[/TD]
[TD="align: right"]84447[/TD]
[TD]GE[/TD]
[TD][/TD]
[TD] $ 174.966,00 [/TD]
[TD="align: right"]0[/TD]
[TD]$ - [/TD]
[TD] $ 174.966,00 [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]7525[/TD]
[TD="align: right"]jun-18[/TD]
[TD]Payment[/TD]
[TD="align: right"]84448[/TD]
[TD]GE[/TD]
[TD][/TD]
[TD] $ 45.000,00 [/TD]
[TD="align: right"]0[/TD]
[TD]$ - [/TD]
[TD] $ 45.000,00 [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]8522[/TD]
[TD="align: right"]jun-18[/TD]
[TD]Payment[/TD]
[TD="align: right"]89542[/TD]
[TD]GE[/TD]
[TD][/TD]
[TD] $ 35.818,00 [/TD]
[TD="align: right"]0[/TD]
[TD]$ - [/TD]
[TD] $ 35.818,00 [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]65442[/TD]
[TD="align: right"]jun-18[/TD]
[TD]Payment[/TD]
[TD="align: right"]89549[/TD]
[TD]GE[/TD]
[TD][/TD]
[TD] $ 795.153,00 [/TD]
[TD="align: right"]0[/TD]
[TD]$ - [/TD]
[TD] $ 795.153,00 [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]6542[/TD]
[TD="align: right"]jun-18[/TD]
[TD]Payment[/TD]
[TD="align: right"]89588[/TD]
[TD]GE[/TD]
[TD][/TD]
[TD] $ 66.500,00 [/TD]
[TD="align: right"]0[/TD]
[TD]$ - [/TD]
[TD] $ 66.500,00 [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]other[/TD]
[TD="align: right"]jun-18[/TD]
[TD]Payment[/TD]
[TD="align: right"]89842[/TD]
[TD]GE[/TD]
[TD][/TD]
[TD] $ 2.000.000,00 [/TD]
[TD="align: right"]0[/TD]
[TD]$ - [/TD]
[TD] $ 2.000.000,00 [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]other[/TD]
[TD="align: right"]jun-18[/TD]
[TD]Payment[/TD]
[TD="align: right"]88542[/TD]
[TD]GE[/TD]
[TD][/TD]
[TD] $ 100.850,99 [/TD]
[TD="align: right"]0[/TD]
[TD]$ - [/TD]
[TD] $ 100.850,99 [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Reserve[/TD]
[TD="align: right"]jun-18[/TD]
[TD]Payment[/TD]
[TD="align: right"]13050[/TD]
[TD]GE[/TD]
[TD][/TD]
[TD] $ 54.000,00 [/TD]
[TD="align: right"]0[/TD]
[TD]$ - [/TD]
[TD] $ 54.000,00 [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Reserve[/TD]
[TD="align: right"]jun-18[/TD]
[TD]Payment[/TD]
[TD="align: right"]13049[/TD]
[TD]GE[/TD]
[TD][/TD]
[TD] $ 52.500,00 [/TD]
[TD="align: right"]0[/TD]
[TD]$ - [/TD]
[TD] $ 52.500,00 [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]85236[/TD]
[TD="align: right"]jun-18[/TD]
[TD]Payment[/TD]
[TD]SR959-05-18-I[/TD]
[TD]GE[/TD]
[TD][/TD]
[TD] $ 24.800,00 [/TD]
[TD="align: right"]0[/TD]
[TD]$ - [/TD]
[TD] $ 24.800,00 [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]V78852[/TD]
[TD="align: right"]jun-18[/TD]
[TD]Payment[/TD]
[TD="align: right"]852[/TD]
[TD]GE[/TD]
[TD][/TD]
[TD] $ 67.095,00 [/TD]
[TD="align: right"]0[/TD]
[TD]$ - [/TD]
[TD] $ 67.095,00 [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]895532[/TD]
[TD="align: right"]jun-18[/TD]
[TD]Payment[/TD]
[TD="align: right"]853[/TD]
[TD]GE[/TD]
[TD][/TD]
[TD] $ 47.310,00 [/TD]
[TD="align: right"]0[/TD]
[TD]$ - [/TD]
[TD] $ 47.310,00 [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]8889[/TD]
[TD="align: right"]jun-18[/TD]
[TD]Payment[/TD]
[TD="align: right"]5551[/TD]
[TD]GE[/TD]
[TD][/TD]
[TD] $ 1.000,00 [/TD]
[TD="align: right"]0[/TD]
[TD]$ - [/TD]
[TD] $ 1.000,00 [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Reserve[/TD]
[TD="align: right"]jun-18[/TD]
[TD]Payment[/TD]
[TD="align: right"]88820[/TD]
[TD]GE[/TD]
[TD][/TD]
[TD] $ 599,97 [/TD]
[TD="align: right"]0[/TD]
[TD]$ - [/TD]
[TD] $ 599,97 [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody><colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup>[/TABLE]
I am coming across with coming up a formula to summarize the description.
basically I would like to find the Serial number in column A. If it is other or reserve, then no further steps to be taken, other than that, I would like to find the respective payment type, period start, period and in column K,L,M and retrieve the value in column F.
the lines marked in red are the expect results.
Thank you very much in advance for your help!
Let me know if you Need further clarification.
Best regards
[TABLE="width: 1376"]
<tbody>[TR]
[TD]Serial No.[/TD]
[TD]Month[/TD]
[TD]Invoice Type[/TD]
[TD]Invoice No.[/TD]
[TD]Supplier[/TD]
[TD]Description[/TD]
[TD]Amount
gross[/TD]
[TD]Tax [/TD]
[TD]Tax amount
amount[/TD]
[TD]Net Amount[/TD]
[TD]Payment type[/TD]
[TD]Period Start[/TD]
[TD]Period End[/TD]
[/TR]
[TR]
[TD="align: right"]7522[/TD]
[TD="align: right"]jun-18[/TD]
[TD]Payment[/TD]
[TD="align: right"]84445[/TD]
[TD]GE[/TD]
[TD]Assessment_fee_01.04-30.04.2018_SN_7522[/TD]
[TD] $ 71.000,00 [/TD]
[TD="align: right"]0[/TD]
[TD]$ - [/TD]
[TD] $ 71.000,00 [/TD]
[TD]Assessment_fee[/TD]
[TD]01.04[/TD]
[TD]30.04.2018[/TD]
[/TR]
[TR]
[TD="align: right"]7523[/TD]
[TD="align: right"]jun-18[/TD]
[TD]Payment[/TD]
[TD="align: right"]84446[/TD]
[TD]GE[/TD]
[TD]Evaluation_fee_01.05-31.05.2018_SN_7523[/TD]
[TD] $ 45.226,83 [/TD]
[TD="align: right"]0[/TD]
[TD]$ - [/TD]
[TD] $ 45.226,83 [/TD]
[TD]Evaluation_fee[/TD]
[TD]01.05[/TD]
[TD]31.05.2018[/TD]
[/TR]
[TR]
[TD="align: right"]7524[/TD]
[TD="align: right"]jun-18[/TD]
[TD]Payment[/TD]
[TD="align: right"]84447[/TD]
[TD]GE[/TD]
[TD][/TD]
[TD] $ 174.966,00 [/TD]
[TD="align: right"]0[/TD]
[TD]$ - [/TD]
[TD] $ 174.966,00 [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]7525[/TD]
[TD="align: right"]jun-18[/TD]
[TD]Payment[/TD]
[TD="align: right"]84448[/TD]
[TD]GE[/TD]
[TD][/TD]
[TD] $ 45.000,00 [/TD]
[TD="align: right"]0[/TD]
[TD]$ - [/TD]
[TD] $ 45.000,00 [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]8522[/TD]
[TD="align: right"]jun-18[/TD]
[TD]Payment[/TD]
[TD="align: right"]89542[/TD]
[TD]GE[/TD]
[TD][/TD]
[TD] $ 35.818,00 [/TD]
[TD="align: right"]0[/TD]
[TD]$ - [/TD]
[TD] $ 35.818,00 [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]65442[/TD]
[TD="align: right"]jun-18[/TD]
[TD]Payment[/TD]
[TD="align: right"]89549[/TD]
[TD]GE[/TD]
[TD][/TD]
[TD] $ 795.153,00 [/TD]
[TD="align: right"]0[/TD]
[TD]$ - [/TD]
[TD] $ 795.153,00 [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]6542[/TD]
[TD="align: right"]jun-18[/TD]
[TD]Payment[/TD]
[TD="align: right"]89588[/TD]
[TD]GE[/TD]
[TD][/TD]
[TD] $ 66.500,00 [/TD]
[TD="align: right"]0[/TD]
[TD]$ - [/TD]
[TD] $ 66.500,00 [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]other[/TD]
[TD="align: right"]jun-18[/TD]
[TD]Payment[/TD]
[TD="align: right"]89842[/TD]
[TD]GE[/TD]
[TD][/TD]
[TD] $ 2.000.000,00 [/TD]
[TD="align: right"]0[/TD]
[TD]$ - [/TD]
[TD] $ 2.000.000,00 [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]other[/TD]
[TD="align: right"]jun-18[/TD]
[TD]Payment[/TD]
[TD="align: right"]88542[/TD]
[TD]GE[/TD]
[TD][/TD]
[TD] $ 100.850,99 [/TD]
[TD="align: right"]0[/TD]
[TD]$ - [/TD]
[TD] $ 100.850,99 [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Reserve[/TD]
[TD="align: right"]jun-18[/TD]
[TD]Payment[/TD]
[TD="align: right"]13050[/TD]
[TD]GE[/TD]
[TD][/TD]
[TD] $ 54.000,00 [/TD]
[TD="align: right"]0[/TD]
[TD]$ - [/TD]
[TD] $ 54.000,00 [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Reserve[/TD]
[TD="align: right"]jun-18[/TD]
[TD]Payment[/TD]
[TD="align: right"]13049[/TD]
[TD]GE[/TD]
[TD][/TD]
[TD] $ 52.500,00 [/TD]
[TD="align: right"]0[/TD]
[TD]$ - [/TD]
[TD] $ 52.500,00 [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]85236[/TD]
[TD="align: right"]jun-18[/TD]
[TD]Payment[/TD]
[TD]SR959-05-18-I[/TD]
[TD]GE[/TD]
[TD][/TD]
[TD] $ 24.800,00 [/TD]
[TD="align: right"]0[/TD]
[TD]$ - [/TD]
[TD] $ 24.800,00 [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]V78852[/TD]
[TD="align: right"]jun-18[/TD]
[TD]Payment[/TD]
[TD="align: right"]852[/TD]
[TD]GE[/TD]
[TD][/TD]
[TD] $ 67.095,00 [/TD]
[TD="align: right"]0[/TD]
[TD]$ - [/TD]
[TD] $ 67.095,00 [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]895532[/TD]
[TD="align: right"]jun-18[/TD]
[TD]Payment[/TD]
[TD="align: right"]853[/TD]
[TD]GE[/TD]
[TD][/TD]
[TD] $ 47.310,00 [/TD]
[TD="align: right"]0[/TD]
[TD]$ - [/TD]
[TD] $ 47.310,00 [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]8889[/TD]
[TD="align: right"]jun-18[/TD]
[TD]Payment[/TD]
[TD="align: right"]5551[/TD]
[TD]GE[/TD]
[TD][/TD]
[TD] $ 1.000,00 [/TD]
[TD="align: right"]0[/TD]
[TD]$ - [/TD]
[TD] $ 1.000,00 [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Reserve[/TD]
[TD="align: right"]jun-18[/TD]
[TD]Payment[/TD]
[TD="align: right"]88820[/TD]
[TD]GE[/TD]
[TD][/TD]
[TD] $ 599,97 [/TD]
[TD="align: right"]0[/TD]
[TD]$ - [/TD]
[TD] $ 599,97 [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody><colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup>[/TABLE]