Page 3 of 4 FirstFirst 1 2 3 4 LastLast
Results 21 to 30 of 33

Thread: Clearing Account formula ?

  1. #21
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    2,061
    Articles
    0
    Excel Version
    365


    Register for a FREE account, and/
    or Log in to avoid these ads!

    Quote Originally Posted by Coach Andrew View Post
    Can you help explain your objective?
    Dead simple: for each row I look in the same table for an exact match with:
    1. the negative version of AMOUNT (HOME) column ('negative version' actually means the reversed sign, so +ve it was -ve and vice versa)
    2. the VENDOR column
    3. the BATCHNO column

    I then put the index numbers (from the Index column) of the rows found into the Possibles column, then it's over to you to use them to examine the those rows to see if they should be paired.
    Any row with nothing in the Possibles column means no match has been found at all (which means there are no matches on those 3 columns, so no need to look for any).
    Last edited by p45cal; 2021-11-02 at 04:21 PM.

  2. #22
    Acolyte Coach Andrew's Avatar
    Join Date
    Jun 2021
    Location
    Ottawa
    Posts
    20
    Articles
    0
    Excel Version
    Excel 2016
    Ok, sounds good. Can you send me the revised file with these latest changes? I can try it out on current Sept data.

  3. #23
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    2,061
    Articles
    0
    Excel Version
    365
    Quote Originally Posted by Coach Andrew View Post
    Ok, sounds good. Can you send me the revised file with these latest changes? I can try it out on current Sept data.
    The file attached to msg#19 is that file which does as described in msg#21. (It just matches one more thing than the file in msg#9.)
    As I said earlier, for more exact/likely matches:
    I'll have a play and see what I can do later, but only if I can have a more extensive sample data to play with.

  4. #24
    Acolyte Coach Andrew's Avatar
    Join Date
    Jun 2021
    Location
    Ottawa
    Posts
    20
    Articles
    0
    Excel Version
    Excel 2016

    Added more data to file

    Added more data to file, refer to Sep data file.

  5. #25
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    2,061
    Articles
    0
    Excel Version
    365
    In the attached, I've left it with a single cell selected (AC2514) which is showing 4 possible matches on just the 3 column matches as described in msg#21.
    I've autofiltered the result table not to show blanks in Possibles column.
    I've highlighted those possibly matching rows.
    There are similar rows with multiple possible matches elsewhere.
    To move forward, I need you to look at the rows and tell me if there's any info in any of the rows (you'll have to unhide columns) that can be used to eliminate possible matches.
    That done, I can try to translate what you suggest into code so it's done automatically.

    I can't attach the file here (too big) so here's a link to it: https://app.box.com/s/95ag50tg5za2ewa6x1o50w4tkch4b66n

  6. #26
    Acolyte Coach Andrew's Avatar
    Join Date
    Jun 2021
    Location
    Ottawa
    Posts
    20
    Articles
    0
    Excel Version
    Excel 2016
    Ok Thanks, I will have a look into the revised file and let you know.

  7. #27
    Acolyte Coach Andrew's Avatar
    Join Date
    Jun 2021
    Location
    Ottawa
    Posts
    20
    Articles
    0
    Excel Version
    Excel 2016
    Hi, was hoping to use PQ for my Oct data. I'm fairly new to PQ so I'm not quite sure how I connect my new data to this file. Any suggestions?

  8. #28
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    2,061
    Articles
    0
    Excel Version
    365
    Quick and dirty solution: cut and paste the data into the table in the file I linked to in msg#25.

    Another solution: In the attached, cell A1 contains a path to the file (workbook). Edit this to point to your new file, then refresh the query at cell C1.
    Cell A1 is actually a named range: FileName. There's a little macro that's called if you double-click on that cell which will let you pick the file (saves mis-typing the path and filename). You still need to refresh the query after doing this.

    It's a bit slow at the moment, I haven't sussed out how to speed it up (it should be a lot faster).

    Separately, aren't you likely to want multiple months' files brought in since I'd expect some transactions to be reversed in a different month from their creation(?). What can be done is for Power Query to pick up all files in a folder (or a subset of them) so that all you'd need to do is to make sure the files you want processing are all in the same folder.
    Attached Files Attached Files

  9. #29
    Acolyte Coach Andrew's Avatar
    Join Date
    Jun 2021
    Location
    Ottawa
    Posts
    20
    Articles
    0
    Excel Version
    Excel 2016
    Thanks, I will have a try and let you know if I have any issues.

  10. #30
    Acolyte Coach Andrew's Avatar
    Join Date
    Jun 2021
    Location
    Ottawa
    Posts
    20
    Articles
    0
    Excel Version
    Excel 2016
    An error occurred in the ‘fnPossibles’ query. Expression.Error: We cannot apply field access to the type Null.
    Details:
    Value=
    Key=Index


    Got this error message?

Page 3 of 4 FirstFirst 1 2 3 4 LastLast

Posting Permissions

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