Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Get Fabric Certified for FREE during Fabric Data Days. Don't miss your chance! Request now

Reply
KuntalSingh
Helper V
Helper V

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

‎09-11-2024 08:00 AM

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
 
Labels:
Message 1 of 3
371 Views
0
2 REPLIES 2
Omid_Motamedise
Super User
Super User

‎09-11-2024 02:43 PM

WHich one is column CC and which one is collumn L?


If my answer helped solve your issue, please consider marking it as the accepted solution.
Message 2 of 3
343 Views
KuntalSingh
Helper V
Helper V
In response to Omid_Motamedise

‎09-11-2024 06:45 PM

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

Message 3 of 3
323 Views
0

Helpful resources

Announcements
November Power BI Update Carousel

Power BI Monthly Update - November 2025

Check out the November 2025 Power BI update to learn about new features.

Fabric Data Days Carousel

Fabric Data Days

Advance your Data & AI career with 50 days of live learning, contests, hands-on challenges, study groups & certifications and more!

FabCon Atlanta 2026 carousel

FabCon Atlanta 2026

Join us at FabCon Atlanta, March 16-20, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.

View All