+ Reply to Thread
Results 1 to 3 of 3

Help Reconciling Accounts - Formula & Conditional Formatting

  1. #1
    Registered User
    Join Date
    11-09-2015
    Location
    Florida
    MS-Off Ver
    O365
    Posts
    61

    Help Reconciling Accounts - Formula & Conditional Formatting

    Just took over a budget spreadsheet and am looking for a way to quickly reconcile one table against another. I combine the key data on to a spreadsheet and so far have been manually matching the dollar amounts with the PO #'s as confirmation of the right account. I'd like to some how get a formula into a macro that will do this for me, but honestly am looking for a better way.

    In a nutshell: I need to compare to Invoice numbers and the dollar amounts that go with each. I'm thinking of a match that tells me they were matched on both criteria, but also a way to fill the cells of the ones that matched, thus making it easy for me to identify the anomalies. I put some notes in the sample and what I'm thinking it should look like, but am sure I'm making it way more complicated than it needs to be.

    Thanks in advance for any help.
    Attached Files Attached Files

  2. #2
    Registered User
    Join Date
    11-09-2015
    Location
    Florida
    MS-Off Ver
    O365
    Posts
    61

    Re: Help Reconciling Accounts - Formula & Conditional Formatting

    Anybody able to point me in the right direction?

  3. #3
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    MS365 Apps for enterprise
    Posts
    5,887

    Re: Help Reconciling Accounts - Formula & Conditional Formatting

    Depends on tools at your disposal. But using Excel 2013, I'd do something like following.

    Add 3rd table, that list all the unique PO/Invoice.

    Then create relationship to each existing table. Data -> Relationships

    Then use Table3's ID column as Row Label. "Sum" of Amount & Reconcile $$ as value fields.

    See attached.

    Alternately, you can load both to PowerQuery (Get & Transform) and then perform join operation.
    Attached Files Attached Files
    ?Progress isn't made by early risers. It's made by lazy men trying to find easier ways to do something.?
    ― Robert A. Heinlein

+ 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] Formula for accounts document
    By MR H 91 in forum Excel Formulas & Functions
    Replies: 37
    Last Post: 10-24-2017, 10:02 AM
  2. Replies: 3
    Last Post: 06-19-2015, 07:16 AM
  3. Replies: 6
    Last Post: 04-16-2015, 01:45 PM
  4. Formatting Cells with Date or Text Values in a Conditional Formatting Formula
    By Phil Hageman in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 07-15-2014, 09:36 AM
  5. Formatting to Identify Accounts NOT Added to a Table
    By bmsrmd in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-07-2014, 04:24 PM
  6. Please help, intricate subtotal for main accounts with many sub accounts!!
    By mitch_bossard in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-24-2013, 05:17 PM
  7. VBA with PivotTable Count of Accounts - Want to show Sum of Accounts
    By snake10 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-19-2008, 07:27 AM

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