+ Reply to Thread
Results 1 to 6 of 6

Power Query to Match (Bank - Positive) vs (OR - Negative) amounts within each Branch

  1. #1
    Forum Contributor
    Join Date
    06-01-2014
    Location
    Riyadh, Saudi Arabia
    MS-Off Ver
    Microsoft 365
    Posts
    224

    Question Power Query to Match (Bank - Positive) vs (OR - Negative) amounts within each Branch

    Hello Everyone,

    I have a situation where I am trying to use Power Query to match a list of data (see attached file).

    We have Columns - ID, Match, Net, Branch & Dates which are important in this process:

    Bank has Positive Numbers in Net Column
    OR has Negative Numbers in Net Column

    I would like to make a one to one match of Bank Numbers against OR Numbers and populate in Column B (Match) with value - 1 for all those amounts matching.

    If the numbers match exactly with each other that's fantastic, but in general it is not the case,
    and I am prepared to allow a tolerance limit for the difference from minus 2 to plus 2 (i.e. "-2 <= DIFF <= 2").
    So if the compared amounts throw out a difference within this range it can be assumed they are matched.

    Some Rules:
    - the matching should be between Bank vs OR (absolute values) e.g. 100 against -100 assumed matched
    - numbers matching within each branch not against another branch number
    - for the moment we can leave out date criteria
    Attached Files Attached Files

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Power Query to Match (Bank - Positive) vs (OR - Negative) amounts within each Branch

    You have failed to provide a manual mock-up of the results you expect to see for that sample.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  3. #3
    Forum Contributor
    Join Date
    06-01-2014
    Location
    Riyadh, Saudi Arabia
    MS-Off Ver
    Microsoft 365
    Posts
    224

    Re: Power Query to Match (Bank - Positive) vs (OR - Negative) amounts within each Branch

    The matched results would look like this and we are left with four rows highlighted in RED not matched:
    Attachment 655059

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Power Query to Match (Bank - Positive) vs (OR - Negative) amounts within each Branch

    Please attach a workbook with the mock-up in it. I cannot use a picture.

  5. #5
    Forum Contributor
    Join Date
    06-01-2014
    Location
    Riyadh, Saudi Arabia
    MS-Off Ver
    Microsoft 365
    Posts
    224

    Re: Power Query to Match (Bank - Positive) vs (OR - Negative) amounts within each Branch

    the expected result as excel file
    Attached Files Attached Files

  6. #6
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,423

    Re: Power Query to Match (Bank - Positive) vs (OR - Negative) amounts within each Branch

    This formula based proposal doesn't employ Power Query and does employs helper columns, which may be moved and/or hidden for aesthetic purposes.
    The first helper column is populated using: =IFERROR(AGGREGATE(15,6,ROW(C3:C$25)/(A3:A$25<>A2)/(E3:E$25=E2)/(C3:C$25>=-C2-2)/(C3:C$25<=-C2+2),COUNTIFS(C$2:C2,">="&C2-2,C$2:C2,"<="&C2+2,E$2:E2,E2)),"")
    The second helper column is populated using: =IFERROR(AGGREGATE(15,6,ROW(C$1:C1)/(A$1:A1<>A2)/(E$1:E1=E2)/(C$1:C1>=-C2-2)/(C$1:C1<=-C2+2),COUNTIFS(C$2:C2,">="&C2-2,C$2:C2,"<="&C2+2,E$2:E2,E2)),"")
    Column B is then populated using: =IF(SUM(G2:H2),1,"")
    Let us know if you have any questions.
    Attached Files Attached Files
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Bank reconciliation/matching negative with positive amount using VBA
    By jodayme in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 09-13-2019, 09:14 AM
  2. [SOLVED] Growth Formula with negative and positive amounts
    By archimaitreya in forum Excel Formulas & Functions
    Replies: 16
    Last Post: 04-09-2019, 11:44 AM
  3. [SOLVED] Sum all negative/positive amounts if maturity date is less/more than twelve months
    By Hans-Maulwurf in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-06-2018, 12:54 PM
  4. [SOLVED] matching positive/negative amounts and highlighted them
    By Jocote46 in forum Excel Programming / VBA / Macros
    Replies: 15
    Last Post: 04-08-2015, 11:15 AM
  5. [SOLVED] Filter and copy negative and positive amounts with rows to 2 other sheets
    By abjac in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 10-20-2014, 10:34 AM
  6. Replies: 6
    Last Post: 02-03-2014, 05:57 PM
  7. [SOLVED] allocating between positive and negative amounts
    By THosier in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-07-2005, 10:06 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1