+ Reply to Thread
Results 1 to 8 of 8

Reconciliation

  1. #1
    Registered User
    Join Date
    06-22-2020
    Location
    Argentina
    MS-Off Ver
    2013
    Posts
    4

    Reconciliation

    Hi everyone,

    It's part of my job to reconciliate large lists of transactions. I can't attach an example, but I think you guys will get the idea.
    I want to automate it as much as possible.
    I've found many "problems":
    1. Company names can be different
    2. Dates can be different
    3. 1 transaction can be the SUM of multiple transactions
    4. Values may be different by cents
    5. There are duplicates,triplicates,etc.

    My way to do this task at the moment is to use a COUNTIF formula, but it's not accurate. Because I may have duplicates or different decimal values (Ex: 10.25 vs 10.23)


    Sorry for my spelling mistakes, English is not my native language.

    Thanks!!!

  2. #2
    Valued Forum Contributor loginjmor's Avatar
    Join Date
    01-31-2013
    Location
    Cedar Rapids, Iowa
    MS-Off Ver
    Excel 2013
    Posts
    1,073

    Re: Reconciliation

    Hi -

    Without a sample spreadsheet, I can only speculate on the best solution. But in general, you need to find a way to clean up your data. For example, if you have company names that are different, but are all for the same company (Like ABC, ABC Co., ABC and Co., etc.) pick one name and do a search and replace to get them all to be the same name. Likewise find any transactions that are the sum of other transactions and pull those out separately (or delete them and leave the smaller transactions as you can sum them using SUMIF, SUMIFS, COUNTIF, COUNTIFS, etc.

    On the different decimal values, I assume that's because of rounding errors? If it's rounding, getting rid of the intermediate SUM's per above should help. If it's data entry errors, you need to talk to the person entering the data and use some quality control rules to improve data integrity. With the data you have, you will need to decide how important is it that all of the numbers match exactly? You could consider rounding the numbers to the nearest 0.1 or even the nearest whole number. But first, you have to figure out why there are differences and then you will be better able to decide how to deal with them.

    Hope this helps.
    ____________________________________________
    If this has solved your problem, please edit the thread title to add the word [SOLVED] at the beginning. You can do this by
    -Go to the top of the first post
    -Select Thread Tools
    -Select Mark thread as Solved

    If I have been particularly helpful, please "bump" my reputation by pressing the small star in the lower left corner of my post.

  3. #3
    Registered User
    Join Date
    06-22-2020
    Location
    Argentina
    MS-Off Ver
    2013
    Posts
    4

    Re: Reconciliation

    I've attached an example!
    Attached Files Attached Files

  4. #4
    Valued Forum Contributor loginjmor's Avatar
    Join Date
    01-31-2013
    Location
    Cedar Rapids, Iowa
    MS-Off Ver
    Excel 2013
    Posts
    1,073

    Re: Reconciliation

    Hi -

    Let's take this one step at a time. The first thing I did is what I said in my first post: Find and make a list of all of the different company names between your two spreadsheets: Company and Taxes. I created a new spreadsheet called Company Names and Alias. To find all of the names from the two different lists, I used the LOOKUP function to first search in the Company spreadsheet and then go to the Taxes Spreadsheet. You will notice in Column A of the new spreadsheet there are entries for MICHAEL PAGESSSSS and for MICHAEL PAGE. There are also two entries for NATURA COSMETICOS S.A. Even though they look identical, the second one has a blank space after the end of the name, which makes it different. The formula to create the list of unique names is:

    =IFERROR(LOOKUP(2,1/(COUNTIF($A$1:A1,Company!$C$2:$C$25)=0),Company!$C$2:$C$25),LOOKUP(2,1/(COUNTIF($A$1:A1,Taxes!$B$2:$B$25)=0),Taxes!$B$2:$B$25))

    In the column next to that (Column B) I manually type in whatever I want the Standard Name to be for that company. So, for example, MICHAEL PAGESSSSS has a new alias of MICHAEL PAGE. Finally, back on your Company and Taxes spreadsheets, I perform a simple INDEX/MATCH to put the Standard Name (Alias) next to each company name. These are highlighted in orange text.

    I have attached your workbook with the above listed modifications.

    That's the first step. What do you need next?
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    06-22-2020
    Location
    Argentina
    MS-Off Ver
    2013
    Posts
    4

    Re: Reconciliation

    First of all, THANK YOU for your help! I really apreaciate it!

    Would you mind explaining me the BOLD in the formula you created? I don't understand how it works to be honest, and I feel like I will need to understand the fundaments of it to apply it to a 1k/2k transactions reconciliation.

    =IFERROR(LOOKUP(2,1/(COUNTIF($A$1:A1,Company!$C$2:$C$25)=0),Company!$C$2:$C$25),LOOKUP(2,1/(COUNTIF($A$1:A1,Taxes!$B$2:$B$25)=0),Taxes!$B$2:$B$25))


    What I need to do next is the following: (I attached a modified file)
    1. Match "AEROPUERTO" in both sheets (Decimals error)
    2. Match "MICHAEL PAGES" 3 transactions in 1 (The sum of the 3 equals the 1 in the other sheet)
    3. Match those transactions that are Ok (I didn't add any example)

    What would be nice is to match not only by values, but also by COMPANY NAME. I KNOW all of this can be done manually, my objective is to automate it as much as possible.


    MANY MANY THANKS !!!!
    Attached Files Attached Files

  6. #6
    Valued Forum Contributor loginjmor's Avatar
    Join Date
    01-31-2013
    Location
    Cedar Rapids, Iowa
    MS-Off Ver
    Excel 2013
    Posts
    1,073

    Re: Reconciliation

    Hi -

    LOOKUP is one of the original Excel functions before VLOOKUP and HLOOKUP were available. It is a very simple, yet useful function for lists and sorting. At it's basic level, it operates like VLOOKUP. The syntax of the formula is LOOKUP(What you're looking for, What list are you looking for in, Extract the answer from the corresponding location of the first list out of THIS list). A simple example would be:

    LOOKUP(5,A1:A3,B1:B3) Where A1=2, A2=5, and A3=7; B1=X, B2=Y, B3=Z. The lookup function will return "Y" because it finds "5" in the second position of the first list (range A1:A3 ). So it extracts the answer from the second position of of the Second List (range B1:B3), which is Y.

    That's the basics. This application of LOOKUP uses a couple of tricks. First we use COUNTIF and it is looking at the cells immediately above this formula location to see if those names are included in the list of names in Company!$C$2:$C$25. Notice, this particular formula is looking from $A$1 to A1. As you copy the formula down that range expands with the first location always being $A$1 and the second part of the range being the cell just above the current formula cell, so for example $A$1:A4. If any of the names in the Company list are already listed above this formula, then COUNTIF will be greater than 0. If it's a NEW name, that is NOT in the list above this formula, then COUNTIF=0 and our condition is true. So, basically, it creates an array of >0 and 0 that might look like {1,2,2,0,1,1}. So the name in the fourth position is unique because COUNTIF didn't find any repeats.

    Then we convert those numbers to TRUE and FALSE by setting it = to 0. COUNTIF($A$1:A1,Company!$C$2:$C$25)=0 This converts the array {1,2,2,0,1,1} to {FALSE, FALSE,FALSE, TRUE,FALSE, FALSE}. Here is where one of the tricks comes into play: Excel treats TRUE as equal to 1 and FALSE as equal to 0. So, if we take 1/(COUNTIF($A$1:A1,Company!$C$2:$C$25)=0), that converts the array from 1/{FALSE, FALSE,FALSE, TRUE,FALSE, FALSE} to {DIV/0!, DIV/0!, DIV/0!, 1, DIV/0!, DIV/0!}

    This is where the second trick comes into play. LOOKUP is looking for 2 in our list. But our list will never contain 2. It will only contain errors and 1. When LOOKUP is faced with that condition, where what it is looking for is not on the list, it takes the LAST NON-ERROR VALUE, which in this case is the 4th position in the list.

    Finally, as I mentioned earlier if you specify a second list, LOOKUP will return an item from THAT list, the second list, this is in the position found in the FIRST list. In this case, the fourth item, which is the unique name in our list. As practice, erase all of the formulas below the first formula and then copy them down, one row at a time. You will see this LOOKUP formula creates the list IN REVERSE ORDER that they are listed on the Company spreadsheet first, and then the Taxes spreadsheet next.

    I will look at the next pieces you were looking for tomorrow.

    I hope the explanation is clear. If you google "extract unique names from a list in Excel" you will find several web sites (maybe one in Spanish - if that's your native language) that can probably explain better. Most of those use INDEX/MATCH but some of them use this LOOKUP approach as well. I find it very useful. It takes a few times of looking at it and going through the steps slowly to understand it. It is not intuitive at first.

  7. #7
    Valued Forum Contributor loginjmor's Avatar
    Join Date
    01-31-2013
    Location
    Cedar Rapids, Iowa
    MS-Off Ver
    Excel 2013
    Posts
    1,073

    Re: Reconciliation

    Hi -

    I have added a few "tools" to your spreadsheet. These are all on the Company spreadsheet. The first one checks the Taxes spreadsheet for any sums. It sums each of Taxes by Company name. So, you can see MICHAEL PAGE has a sum of $544.05, which matches what is on your Company spreadsheet. You can also see AXXON has an entry of -35,183.36 versus 35381.44. I assume the data was entered as negative instead of positive? You can also see the $0.02 difference between Aeropuertos. The formula for this is a simple SUMIFS. You can add more criteria (like same dates or something like that). I also enclosed it in an IF statement to check is the value on Company is $0. If it's $0, simply return "Zero Value". The formula looks like:

    =IF(F2=0,"Zero Value",SUMIFS(Taxes!$D$2:$D$25,Taxes!$A$2:$A$25,Company!A2))

    The next tool just checks to see if there are any exact matches of values between the two spreadsheets. In your example, there are none.

    The last tool compares the rounded values of both spreadsheets to see if there are approximate matches. You can set the tolerance in Cell L1. This formula uses the ROUND function to round off the values on the Company spreadsheet and compare them to the rounded values on the Taxes spreadsheet (I added a column on the Taxes spreadsheet that rounds those values to the specified Tolerance). The formula looks like:

    =IF(F2=0,"Zero Value",IFERROR("Taxes Row "&(1+MATCH(ROUND(F2,$L$1),Taxes!$E$2:$E$25,0)),"No Match"))

    First, if the Value is $0, it returns "Zero Value". Next it uses the MATCH function to compare the ROUNDed value on Company to the ROUNDed value on Taxes. Currently, I have the tolerance set to 1 decimal. If it finds a match, it tells you which row in the Taxes spreadsheet the match is located, so you can go look at it. If there isn't a match, if returns "No Match". You can change the Tolerance to 0, which will round everything off to the nearest dollar. You can change the tolerance to -1 and round everything to the nearest $10, and so on.

    These tools aren't perfect, but it should help you narrow down a LOT of the problems.

    Hope this helps.
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    06-22-2020
    Location
    Argentina
    MS-Off Ver
    2013
    Posts
    4

    Re: Reconciliation

    Hi again!

    I took a few days to try this things out. I came to a conclusion, this is way TOO advanced for me. I tried to apply it and I couldn't.

    I've got lost in the very beginning of it, with the formula you used to create the "List of Unique Names from Company and from Taxes", the one I asked for your explanation which was great but still too advanced for me.

    I've been looking for easier ways to extract duplicates out of a list as you suggested and I've found it! (DATA - Remove Duplicates) but it doesn't work when you have 2 or more lists, that's why I believe you used the formula.
    When I apply the formula (which I still don't understand) by copy-pasting it, it just gives me a list that never ends, not just the UNIQUE values.

    Check for Sums; Exact Decimal Match; Decimal Tolerance this 3 formulas are easier to understand but if I can't get through the 1st part they they are useless.

    If I manage to apply the Unique Values formula, I'll come back and continue with this journey haha !!

    Thanks again,
    Bye!

+ 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. Reconciliation of Sum
    By Eltun in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 07-06-2018, 06:41 AM
  2. Reconciliation Help
    By giryan in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 01-19-2017, 03:34 PM
  3. Reconciliation
    By musema in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 03-17-2015, 01:54 PM
  4. Reconciliation
    By Shihab in forum Excel General
    Replies: 2
    Last Post: 01-12-2014, 12:21 PM
  5. Reconciliation
    By Eduard in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 07-22-2008, 05:05 AM
  6. Reconciliation
    By cmt_london in forum Excel General
    Replies: 1
    Last Post: 02-19-2008, 09:14 AM
  7. Reconciliation
    By Eduard in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-31-2005, 11:01 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