blake85
03-01-2013, 01:25 AM
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.
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.