Forum Discussion

KuntalSingh's avatar
KuntalSingh
Helper V
1 year ago

Match the column have positive value with Negative value that have same reference number

Please help on one logic

same number in CC column have + and -

value in Amountindoccurr column means nullyfi then comment is blank and same number in CC column have  same  value either + or - in Amountindoccurr column then comment is Highy priority

 

Excel formula

1.Apply formula in  Column CC to remove alphabets from Reference(L Column) AND Concatenate with ABS(amt docu currency) after Please implement your logic to get the output

 

 

ScriptsVendorVendorNameBusinessAreaCompanyCodeFiscalYearDocumentTypeDocumentDatePostingKeyPostingDateDocumentNumberReferenceAmountindoccurrDocumentCurrencyAmountinlocalcurrLocalCurrencyClearingDocumentClearingDateTextNetDueDateConcatenationCC
S(25-60)-12004235596G&G OUTFITTERS INCUS052024RE4533131455305.1E+09242772-166.75USD-166.75USD   4/9/202451001530992024US05242772166.75
S(25-60)-12000011986G&G OUTFITTERS INCUS052024RE4533131453385.1E+09242772-166.75USD-166.75USD20005467724/14/20244/9/202451000427342024US05242772166.75
S(25-60)-12003871958CINTAS CORPORATION NUS012024RE4529331455305.1E+094178789859-205.94USD-205.94USD   3/2/202451018370002024US014178789859205.94
S(25-60)-12000029014CINTAS CORPORATION 100US012024RE4529331455105.1E+094178789859-205.94USD-205.94USD   3/2/202451005247402024US014178789859205.94
S(25-60)-12003871958CINTAS CORPORATION NUS012024RE4530731455305.1E+094180383750-209.39USD-209.39USD   3/16/202451000625092024US014180383750209.39
S(25-60)-12000011956CINTAS CORPORATION NO 2US012024RE4530731455065.1E+094180383750-209.39USD-209.39USD   3/16/202451009005242024US014180383750209.39
S(25-60)-22000014020NYSCO PRODUCTS LLCUS052024RE4459221455305.1E+090236725-IN91.4USD91.4USD  VR:Full Accrual4/1/202251008240022024US050236725-IN91.4
S(25-60)-22000014020NYSCO PRODUCTS LLCUS052024RE4459231454575.1E+090236725-IN-91.4USD-91.4USD    51010235452024US050236725-IN91.4
S(25-60)-22000022228W W GRAINGERUS012024RE4500721455305.1E+099648709799217.97USD217.97USD  VR:Pay-as-billed7/20/202351003365112024US019648709799217.97
S(25-60)-22000022228W W GRAINGERUS012024RE4500731454225.1E+099648709799-217.97USD-217.97USD  VR:Pay-as-billed7/20/202351012950142024US019648709799217.97
S(25-60)-22004015811CINTAS CORPORATION NUS052024RE4528131455305.1E+09I312210921-234.72USD-234.72USD  VR:Pay-as-billed2/19/202451024855002024US05I312210921234.72
S(25-60)-22000031331CINTAS FIRE PROTECTIONUS242024RE4528121453205.1E+09I312210921234.72USD234.72USD1008315074/3/2024VR:Pay-as-billed2/19/202451000080072024US24I312210921234.72
S(25-60)-22000031331CINTAS FIRE PROTECTIONUS242024RE4528131452995.1E+09I312210921-234.72USD-234.72USD1008315074/3/2024VR:Pay-as-billed2/19/202451000540022024US24I312210921234.72
S(25-60)-22000022228W W GRAINGERUS012024RE4528131455305.1E+099943375106-207.97USD-207.97USD   4/19/202451003750022024US019943375106207.97
S(25-60)-22000022228W W GRAINGERUS012023RE4528131452885.1E+099943375106-207.97USD-207.97USD   4/19/202451013625832023US019943375106207.97
S(25-60)-22000022228W W GRAINGERUS012024RE4528131454575.1E+099943375106-207.97USD-207.97USD   4/19/202451011440152024US019943375106207.97
S(25-60)-22000022228W W GRAINGERUS012024RE4528121454885.1E+099943375106207.97USD207.97USD  MR8M4/19/202451005320102024US019943375106207.97
S(25-60)-22000022228W W GRAINGERUS012024RE4528121454275.1E+099943375106207.97USD207.97USD  MR8M4/19/202451006805052024US019943375106207.97
S(25-60)-22003871958CINTAS CORPORATION NUS012024RE4530731455305.1E+094180383750-209.39USD-209.39USD   3/16/202451000625092024US014180383750209.39
S(25-60)-22000011956CINTAS CORPORATION NO 2US012024RE4530731455065.1E+094180383750-209.39USD-209.39USD   3/16/202451009005242024US014180383750209.39
S(25-60)-22000011956CINTAS CORPORATION NO 2US012024RE4530721455075.1E+094180383750209.39USD209.39USD  MR8M3/16/202451014410102024US014180383750209.39
S(25-60)-22000011956CINTAS CORPORATION NO 2US012024RE4530731455075.1E+094180383750-209.39USD-209.39USD20001180108/4/2024 3/16/202451005250672024US014180383750209.39
S(25-60)-22000011956CINTAS CORPORATION NO 2US012024RE4530721455075.1E+094180383750209.39USD209.39USD20001180108/4/2024MR8M3/16/202451008885072024US014180383750209.39
S(25-60)-22000029014CINTAS CORPORATION 100US012024RE4530731455105.1E+094180383750-209.39USD-209.39USD   3/16/202451004805942024US014180383750209.39
 
Need Help

2 Replies

    • KuntalSingh's avatar
      KuntalSingh
      Helper V
      1 year ago

      Column cc is the combination of column Reference AND Concatenate with ABS(Amountindoccurr)and   column  L is Amountindoccurr