+ Reply to Thread
Results 1 to 3 of 3

Data Sorting

  1. #1
    Registered User
    Join Date
    02-26-2013
    Location
    Vatican
    MS-Off Ver
    Excel 2007
    Posts
    36

    Data Sorting

    I'm working on a spreadsheet where I want to analyze data and produce reports. Attaching a sample file which will give you a fair idea what I'm dealing with.
    So the idea is pretty simple find out payor & recipient, most likely $ amount should be the point of reconciliation as payer and the recipient might be living in different countries and if I go by their local currency I'll never going to find the exact match, hence using $ amounts is the best option.
    The sample I provided is pretty neat and clean and have less than 10 transactions. I've 3000 to 5000 tractions ( or say rows) every month meaning 3000 to 5000 rows multiplied by 12 months of data. And I'm killing my eyes to match up that data manually.
    To test my excel skills ( although I'm not PRO like you folks, but do some functions here and there ) I used duplicate cell function in conditional formatting section, and find out exact numbers; for this I used ABS function as you can see my numbers are positive and negative format so I want to get it straight.

    So out of those 3000 to 5000 rows I might have 1200 or 1500 rows that are exactly tied to each other but again finding recipient and payor is a big headache, as you can see our accountant are not that great and they are not consistent on typing standard line description ;so I've to sort all those duplicates then out of those duplicates I've to manually look exact opposite entry like on row 2&3 of the attached file "Sheet1". I further tried to automate by selecting last 1 or 2 digits of line description and tried to sort further, but out of 1200 to 1500 records I may able to find 700 to 800 records, thanks to our accountant for not giving a standard description.

    Further I found out accounting software might have slightly different $ number but the local currency number same e.g. if you see row 7 & 8 you'll find that EUR number same but the $ number off by 5 so I've to consider that as well.

    So, after doing this manually for 1 month I've to repeat this process for 12 months. Once I finished that I'll have to compile a report / table which states in a year how many transactions happens form say party 1 to party 2 like that.
    Attached Files Attached Files

  2. #2
    Registered User
    Join Date
    02-28-2013
    Location
    Cincinnati, OH
    MS-Off Ver
    Excel 2007 & 2010
    Posts
    7

    Re: Data Sorting

    I think I have an idea of what you are needing, but without seeing a full month's worth of data, it is difficult to discern the problem and create a solution for you. Can you please upload an actual file of atleast one month of what you are working with?

    Britt
    Click (*) if you received a helpful response.

  3. #3
    Registered User
    Join Date
    02-26-2013
    Location
    Vatican
    MS-Off Ver
    Excel 2007
    Posts
    36

    Re: Data Sorting

    Thanks for the reply, but can not upload entire file as it's too big to handle this forum. also I've security issues as well. So really appreciate your help on whatever info I already provided.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

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