+ Reply to Thread
Results 1 to 6 of 6

how to make reconciliations more efficient

  1. #1
    Forum Contributor
    Join Date
    06-21-2009
    Location
    Toronto, Ontario
    MS-Off Ver
    Excel 2007
    Posts
    200

    how to make reconciliations more efficient

    This may sound minor enough but it took me an hour to reconcile 700 lines so I’m wondering if there’s an easier way.

    Refer to attached file. Col C shows the discrepancies. You’ll notice that when there’s a discrepancy you simply move the data on the right side down a row or two, and that resolves it. So for example, row 7 matches, row 8 does not; highlight F8:G8, click insert, and now the data matches.

    The problem is, when you do this, the formula in col D which is checking for discrepancies, also gets messed up. Is there a way to avoid that?
    Attached Files Attached Files

  2. #2
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    Re: how to make reconciliations more efficient

    If you're attempting to marry the two columns together, I'd recommend using a vlookup or a search function.

    Are you just trying to align the columns cosmetically or do you need data from F&G lined up next to A&B?

  3. #3
    Forum Contributor
    Join Date
    06-21-2009
    Location
    Toronto, Ontario
    MS-Off Ver
    Excel 2007
    Posts
    200

    Re: how to make reconciliations more efficient

    marry the columns together, heh, i like that.

    i dont see the difference between the 2 questions but the second option seems closer. basically there's some data in A&B that doesnt exist in F&G, and i need to see what that data is so i can determine similarities in them.

  4. #4
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    Re: how to make reconciliations more efficient

    I assume there's a lot more data in Column A than you've pasted?

    I'm having trouble accounting for the duplicates.
    Last edited by daffodil11; 07-19-2013 at 03:46 PM. Reason: duplicate values

  5. #5
    Forum Expert Ace_XL's Avatar
    Join Date
    06-04-2012
    Location
    UAE
    MS-Off Ver
    2016
    Posts
    6,074

    Re: how to make reconciliations more efficient

    use this in D2 instead of your current formula

    =IF(INDIRECT("B"&ROW())<>INDIRECT("G"&ROW()),"No","")

    copy down

    Inserting (or deleting) cells (not rows!) in F:G will not alter the cell references. Does this help?
    Life's a spreadsheet, Excel!
    Say thanks, Click *

  6. #6
    Forum Contributor
    Join Date
    06-21-2009
    Location
    Toronto, Ontario
    MS-Off Ver
    Excel 2007
    Posts
    200

    Re: how to make reconciliations more efficient

    that's awesome, thanks!

    would you mind explaining the formula? i tried figuring it out myself, dont really understand it.

+ 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. Bank Reconciliations
    By Swinging Beef in forum Hello..Introduce yourself
    Replies: 2
    Last Post: 08-10-2012, 03:25 AM
  2. How to make Excel VBA more efficient?
    By mhni in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-17-2009, 04:31 PM
  3. Make code more efficient
    By allanr in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-29-2008, 08:43 PM
  4. How can I make this code more efficient?
    By Sethaholic in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-01-2006, 12:20 PM
  5. [SOLVED] need to make code more efficient (if possible)
    By Lilivati in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 07-07-2006, 02:55 PM

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