Results 1 to 3 of 3

Thread: Data Sortin_ MS Excel 2010

  1. #1
    Junior Member
    Join Date
    Mar 2013
    Posts
    2
    Rep Power
    0

    Question Data Sortin_ MS Excel 2010

    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
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10
    Blake, welcome to ExcelFox

    Can you zip a more larger data set, and attach it here (there is a size limit of course). Seems one needs to consider a larger set before devising a solution to your problem (if at all it is possible)
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  3. #3
    Junior Member
    Join Date
    Mar 2013
    Posts
    2
    Rep Power
    0
    This is the only data i can provide now.

Similar Threads

  1. Test copy Activate Ribbon Tab In Excel 2007 2010 *
    By Excel Fox in forum Test Area
    Replies: 16
    Last Post: 01-22-2019, 05:05 PM
  2. Excel 2003 Classic Menu in Excel 2007-2010
    By Excel Fox in forum Classic Menu
    Replies: 7
    Last Post: 09-10-2014, 10:29 PM
  3. Replies: 1
    Last Post: 02-14-2013, 11:08 AM
  4. Save Excel 2010 File In CSV Format VBA
    By mag in forum Excel Help
    Replies: 7
    Last Post: 01-08-2013, 07:16 PM
  5. Add ribbon programmatically to Excel 2010 using VBA
    By heapifyman in forum Excel Ribbon and Add-Ins
    Replies: 6
    Last Post: 07-18-2011, 09:16 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •