+ Reply to Thread
Results 1 to 5 of 5

How to match Debits and Credits on one column, and then hiding or filtering out

  1. #1
    Registered User
    Join Date
    01-07-2021
    Location
    California
    MS-Off Ver
    365
    Posts
    3

    Question How to match Debits and Credits on one column, and then hiding or filtering out

    Hi all and thanks for reading this post! :-)

    I have been searching online for Excel answers and can't seem to find a solution.

    I have about 3000 entries of data, of which, has a few debits and credits to zero them out (i.e., they were reclassed to another account).

    I am trying match the debit and credit entries, and possibly hide them or filter them out of viewing. End goal is to focus on the remaining items, without getting lost seeing all the duplicates.

    Any advice would greatly be appreciated!

    Eugene
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    08-16-2015
    Location
    Antwerpen, Belgium
    MS-Off Ver
    2007-2016
    Posts
    2,380

    Re: How to match Debits and Credits on one column, and then hiding or filtering out

    I think you give not the correct sample, no credits no debits in this file.

    Kind regards
    Leo

  3. #3
    Registered User
    Join Date
    01-07-2021
    Location
    California
    MS-Off Ver
    365
    Posts
    3

    Re: How to match Debits and Credits on one column, and then hiding or filtering out

    Hi Leo,

    Sorry for not being clearer with my file. This is coming from my accounting program, it's noting a lot of additional data that I ended up removing so that we focus on this one column.

    10/26/2018 $(22,752.68)
    10/26/2018 $22,752.68
    10/26/2018 $23,786.56
    10/26/2018 $(3,975.30)
    10/26/2018 $(23,786.56)

    Column B already has all the numbers noted. Within the column though, there's a lot of entries like the below. Overall, I am trying find an easy way to match all the numbers (i.e., -$22,752.68 and $22,752.68, $23,786.56 and -$23,786.56), especially instances when there's 4 or more of the same matching numbers, and then possibly hide or filter them out of view.

    My goal is to ignore these matching numbers, as they are zeroed out and then focus on the remaining items. Starting to read up on vlookup's and if this is a path that will work.

    Approaching 3000+ entries, I'm hoping to find a way to streamline the process.

    Best,

    Eugene

  4. #4
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,810

    Re: How to match Debits and Credits on one column, and then hiding or filtering out

    See if the attached will work for you. I used Power Query which is called Get and Transform in your version of Excel and found on the Data Tab. I split the range into Debits and Credits (changing the credits to all positive numbers.) I then merged the two tables with a left outer join. All data from Debits and those matching values in the credits. I eliminated all duplicate debits.
    Attached Files Attached Files
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  5. #5
    Registered User
    Join Date
    01-07-2021
    Location
    California
    MS-Off Ver
    365
    Posts
    3

    Red face Re: How to match Debits and Credits on one column, and then hiding or filtering out

    Alan - thank you sooooo much for introducing me to Power Query and for showing me how it works with the sample file!

    I just spent some time going over the basic on this and this seems like it'll do the trick.

    I really appreciate your input on this challenge I was facing.

    Thank you, thank you!

    Eugene

+ 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] Matching debits and credits from one column?
    By nimv1 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-07-2020, 08:08 PM
  2. Macro to match Debits and Credits, verifying four columns of each debit and credit match
    By forcedto register in forum Excel Programming / VBA / Macros
    Replies: 15
    Last Post: 09-22-2016, 05:28 PM
  3. Want to Match Debits and Credits for la ist of Customers
    By Suri159951 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-02-2016, 10:30 AM
  4. way to match 2 or amounts to 1 amount, debits and credits!
    By nimv1 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-30-2016, 08:57 PM
  5. [SOLVED] Vba code to match multiple debits and credits by account number
    By TheRock_82 in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 07-12-2015, 05:17 PM
  6. [SOLVED] debits and credits in one column
    By childothe1980s in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-26-2013, 03:47 PM
  7. Can't remove debits/credits that match
    By grecon in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 06-08-2010, 04:28 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