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: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

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
298 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. It helps others in the community find answers faster—and keeps the community growing stronger!
You can also check out my YouTube channel for tutorials, tips, and real-world solutions in Power Query with the following link
https://youtube.com/@omidbi?si=96Bo-ZsSwOx0Z36h
Message 2 of 3
270 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
250 Views
0

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

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

View All