+ Reply to Thread
Results 1 to 3 of 3

Reconciling tables against multiple debits and credits

  1. #1
    Registered User
    Join Date
    08-24-2017
    Location
    singapore
    MS-Off Ver
    2010
    Posts
    2

    Reconciling tables against multiple debits and credits

    Hello everyone, hope someone can help me with the following:

    I want to match below table 1 and 2 and display like the expected result table. Can it be done using excel formula or macro? or it can only to be done manually?


    Table 1
    DATE DESC AMOUNT
    1-Jan-17 AAAA 100,000.00

    Table 2
    DATE DESC AMOUNT
    1/2/2017 AAAA 50,000.00
    1/5/2017 KKKK 90.00
    1/6/2017 AAAA 50,000.00

    Expected Result
    DATE DESC AMOUNT DATE DESC AMOUNT
    1-Jan-17 AAAA 100,000.00 1/2/2017 AAAA 50,000.00
    1/6/2017 AAAA 50,000.00
    Attached Files Attached Files
    Last edited by sealed87; 08-24-2017 at 12:29 PM.

  2. #2
    Registered User
    Join Date
    08-24-2017
    Location
    singapore
    MS-Off Ver
    2010
    Posts
    2

    Re: Reconciling tables against multiple debits and credits

    Hi all, can this be done?

  3. #3
    Forum Expert shukla.ankur281190's Avatar
    Join Date
    05-17-2014
    Location
    Lucknow, India
    MS-Off Ver
    2016
    Posts
    3,834

    Re: Reconciling tables against multiple debits and credits

    Hope you are aware with Power Query option if you are not known then as per your excel version show on excel forum i.e 2010. You can download it here. https://www.microsoft.com/en-in/down....aspx?id=39379 or here is video help https://www.youtube.com/watch?v=gwW2CDdvUUs

    How would it work on your tables ??

    Be on any cell of table 1 and go to the power query tab and click on from table it will convert your table 1 data into power query. You don't need to do any thing, once you get a power query window just need to click on right corner on top close and load.

    Do the same thing for second tab.

    Now you will have two queries which will on right hand side excel window. We will have to merge these queries for extracting data according our need. For merging queries you will again go to power query tab and click on new query and go down select combine query and click on merge. Now you will get a merge window go to the first drop down and select table1, go to the second drop down and select table two. Now click on Desc in first selected tab and pressing control select DESC in second tab. Select joint kind Full outer (All rows from both) then ok.

    Above step will produce merge query and you will see your both table itself settle data according DECS. You will also get some null which are seems to be blank so you just need click on replace value in query tab and replace with blank. Now close and load into excel.

    Check the attached file Sheet6.

    Note it will automatic update once refresh.
    Attached Files Attached Files
    Last edited by shukla.ankur281190; 08-25-2017 at 05:47 AM.
    If I helped, Don't forget to add reputation (click on the little star ★ at bottom of this post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)

+ 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. Matching Debits and Credits
    By jharvey87 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 01-13-2016, 08:38 AM
  2. getting the total of debits and credits
    By ammartino44 in forum Excel General
    Replies: 5
    Last Post: 07-24-2015, 06:10 PM
  3. [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
  4. Debits and credits
    By Willow350 in forum Excel General
    Replies: 2
    Last Post: 12-07-2014, 11:34 AM
  5. [SOLVED] Lookup Debits & Credits
    By Sniper in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-25-2012, 10:25 AM
  6. Highlighting Debits and Credits
    By vharjani in forum Excel General
    Replies: 15
    Last Post: 02-23-2012, 02:20 PM
  7. [SOLVED] Change debits to credits
    By Yo in forum Excel General
    Replies: 1
    Last Post: 04-10-2006, 08:20 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