I have a spreadsheet that has 2 workbooks both of which contain similar data in similar formats. I need to be able to identify differences between the 2 workbooks based on 2 different criteria. The two workbooks have a similar format: Reference, Name, Amount, Date with anything up to 1000 rows of data. In theory the data on both workbooks should be identical but in reality this won't be the case. There will be instances where a Reference will be on worksheet one but not on two and vice versa and there will also be instances where Amount transactions have taken place over 5 rows in workbook 1 but only 3 in workbook 2.
I have subtotalled according to Reference and then Amount and then applied a Vlookup which has enabled me to identify differences between the totals for each Reference but I cannot figure out a way to deliver the differences between individual references based on dates. What I would like to end up with is:
Worksheet 1: Reference Name Amount Date Worksheet 2 Variance (worksheet 1/worksheet 2)
1234 Bloggs £50.00 20/5/14 £25.00 £25.00
1234 Bloggs £50.00 20/6/14 £0.00 £50.00
1234 Bloggs £0.00 20/7/14 £50.00 (£50.00)
Hope this makes sense. Any helpful suggestions greatly appreciated
I have subtotalled according to Reference and then Amount and then applied a Vlookup which has enabled me to identify differences between the totals for each Reference but I cannot figure out a way to deliver the differences between individual references based on dates. What I would like to end up with is:
Worksheet 1: Reference Name Amount Date Worksheet 2 Variance (worksheet 1/worksheet 2)
1234 Bloggs £50.00 20/5/14 £25.00 £25.00
1234 Bloggs £50.00 20/6/14 £0.00 £50.00
1234 Bloggs £0.00 20/7/14 £50.00 (£50.00)
Hope this makes sense. Any helpful suggestions greatly appreciated
Comment