I need the help of someone clever

[LisaContractorUmbrella]

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

[malvolio]

Perhaps start from here... ? Although it does depend on what Windows edition you're running.

[woohoo]

I'm not sure if this is going to help, I use Beyond Compare all the time to compare code. But you can also use it to compare excel sheets. It's only going to show you the differences between the two sheets, so not sure about different criteria etc.

I've bought the software but I think they may have a trial, which might give you a chance to see if it does what you want.

[vetran]

Pivot is always the answer. (unless of course you want to do lost of formulas)

pivot table. Group by date.

Group Pivot Table Items in Excel - Easy Excel Tutorial

There are of course a myriad of ways to do this but pivot table is probably the most user friendly.

You can do charts as well.

[lilelvis2000]

Do you have Access Installed? If so this is easy peasy in Access.

[LisaContractorUmbrella]

Perhaps start from here... ? Although it does depend on what Windows edition you're running.

Good idea but a no go unfortunately - running 2010 and it doesn't give me access to the add in required

[LisaContractorUmbrella]

I'm not sure if this is going to help, I use Beyond Compare all the time to compare code. But you can also use it to compare excel sheets. It's only going to show you the differences between the two sheets, so not sure about different criteria etc.

I've bought the software but I think they may have a trial, which might give you a chance to see if it does what you want.

Am downloading now so will give it a go and report back

[LisaContractorUmbrella]

The software did work but it wasn't reporting the differences in a very user friendly way (can see other uses though so thanks for tip) so now moving on to the pivot table idea

[LisaContractorUmbrella]

pivot table. Group by date.

Group Pivot Table Items in Excel - Easy Excel Tutorial

There are of course a myriad of ways to do this but pivot table is probably the most user friendly.

You can do charts as well.

Hang on - how will this work in comparing the two worksheets? It's early so there's a good chance I may be being dense but I can't see it

[LisaContractorUmbrella]

Do you have Access Installed? If so this is easy peasy in Access.

Unfortunately not