Hello,
I used to think that i was pretty good at using excel to complete data analysis but this particular set of data is giving me a run for my money. Anyhow here is a little background about the data, i work for a mortgage company that sells our loans to mortgage investors, as part of the selling process we send the loan file to the investor they review and get back to us with conditions that need to be meet before they will purchase the loan. those conditions are recorded on 20 line item and when i create a report i get basic info about the loan followed by 20 suspense categories, 20 subcategories, 20 responsible people, 20 dates in which each condition is cleared, and a few other.
to be able to create pivot table based such data i wrote a vba code that copies and pastes items #1, then items #2, followed by items #3 and so on, giving a single column for each parameter, the data is so big that my excel keeps on crushing, so my questions are, do any one have a better way to create pivot table on such data (without having to copy and paste on top of each other 20 times), or... is there maybe an affordable software or tool that i may be able to use for this.
A little more info about this, one of the reports that i do is most frequent suspense category and subCategory by month. Or average number of day between the Sent Date and Resolved Date for each of the possible 20 conditions, and i may need to then show that by responsible role. and users need to be able to drill down on the each metric and see the details that create such metric (like pivot tables allow for).
oh! and one last thing i also build graphs to show trends within the data.
Here is a link to a data sample, and this only about 80% of the total rows that i would need to look each month
https://www.dropbox.com/s/hr0g8icuinll9e9/suspense data with turn times 2.xlsx?dl=0
Thank you very much in advance for any help.
I used to think that i was pretty good at using excel to complete data analysis but this particular set of data is giving me a run for my money. Anyhow here is a little background about the data, i work for a mortgage company that sells our loans to mortgage investors, as part of the selling process we send the loan file to the investor they review and get back to us with conditions that need to be meet before they will purchase the loan. those conditions are recorded on 20 line item and when i create a report i get basic info about the loan followed by 20 suspense categories, 20 subcategories, 20 responsible people, 20 dates in which each condition is cleared, and a few other.
to be able to create pivot table based such data i wrote a vba code that copies and pastes items #1, then items #2, followed by items #3 and so on, giving a single column for each parameter, the data is so big that my excel keeps on crushing, so my questions are, do any one have a better way to create pivot table on such data (without having to copy and paste on top of each other 20 times), or... is there maybe an affordable software or tool that i may be able to use for this.
A little more info about this, one of the reports that i do is most frequent suspense category and subCategory by month. Or average number of day between the Sent Date and Resolved Date for each of the possible 20 conditions, and i may need to then show that by responsible role. and users need to be able to drill down on the each metric and see the details that create such metric (like pivot tables allow for).
oh! and one last thing i also build graphs to show trends within the data.
Here is a link to a data sample, and this only about 80% of the total rows that i would need to look each month
https://www.dropbox.com/s/hr0g8icuinll9e9/suspense data with turn times 2.xlsx?dl=0
Thank you very much in advance for any help.
please!! 
