I wasn't sure where to post but this seems a more general area to post questions. I'm trying to come up with a list of variances between two files of data. I don't know enough about PowerPivot or Access to know if it would make more sense to try to compare the data using a different process than I use currently. Below is my general process.
I'm comparing tax lot data (account, stock, acquired date, cost, quantity) from two sources. I have about 20 columns and 105,000 lines from one source & 15 columns with 205,000 from the other. I've been using a couple other data sources so I can convert the account numbers to a common account number in both files.
After I have a common account number I create a lot number for each row that has the same account number, stock, and acquired date in each file (I have many rows with lot 1.) Then I create a pivot table for source 1 that has all the lots and the separately for source 2. Then I copy the data from the two pivot tables into one spreadsheet and compare the information using formulas. I get about 90% of the way there. I ended up with about 5,000 lines that have variances, of which about half of actually do match.
It would be easy if there was only one lot for each account but there are often 30 lots, many of which have the same acquired date and cost and the only difference in the whole row of data is the quantity...or more often and quantity & cost. What I have works but takes 2-3 days to manually review because the formulas don't always work correctly and because my files crash constantly because I have so many formulas. This is fine when I was only doing it annually but we're looking to do it at least monthly now.
Any feedback would be appreciated. Thanks!
DeDe
I'm comparing tax lot data (account, stock, acquired date, cost, quantity) from two sources. I have about 20 columns and 105,000 lines from one source & 15 columns with 205,000 from the other. I've been using a couple other data sources so I can convert the account numbers to a common account number in both files.
After I have a common account number I create a lot number for each row that has the same account number, stock, and acquired date in each file (I have many rows with lot 1.) Then I create a pivot table for source 1 that has all the lots and the separately for source 2. Then I copy the data from the two pivot tables into one spreadsheet and compare the information using formulas. I get about 90% of the way there. I ended up with about 5,000 lines that have variances, of which about half of actually do match.
It would be easy if there was only one lot for each account but there are often 30 lots, many of which have the same acquired date and cost and the only difference in the whole row of data is the quantity...or more often and quantity & cost. What I have works but takes 2-3 days to manually review because the formulas don't always work correctly and because my files crash constantly because I have so many formulas. This is fine when I was only doing it annually but we're looking to do it at least monthly now.
Any feedback would be appreciated. Thanks!
DeDe
