I need the help of someone clever

[malvolio]

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

Yes, didn't think you could use a pivot table with data from an external source such as another workbook. Could be wrong though, I'm a bit rusty on them these days!

You could do it with a combination of Vlookups and conditional formatting using an intermediate workbook to capture the values (not the original formulae) along the lines of find the matched entries, sum the content in both and check if the two outputs are the same. It would be a little clunky - you'd wind up with several nested IF clauses probably - but it could be made to work.

Just as an aside, though, with my process head on, why two parallel workbooks? Should there not be one source of the truth?

[LisaContractorUmbrella]

Yes, didn't think you could use a pivot table with data from an external source such as another workbook. Could be wrong though, I'm a bit rusty on them these days!

You could do it with a combination of Vlookups and conditional formatting using an intermediate workbook to capture the values (not the original formulae) along the lines of find the matched entries, sum the content in both and check if the two outputs are the same. It would be a little clunky - you'd wind up with several nested IF clauses probably - but it could be made to work.

Just as an aside, though, with my process head on, why two parallel workbooks? Should there not be one source of the truth?

It would be a bit clunky I think - am looking at the moment to sum figures within a date range on both sheets and then compare the results that way.

Comparing two sets of figures from two different sources - data should be the same but isn't and I need to identify why

[malvolio]

It would be a bit clunky I think - am looking at the moment to sum figures within a date range on both sheets and then compare the results that way.

Comparing two sets of figures from two different sources - data should be the same but isn't and I need to identify why

OK, so you're looking at using SUMIFS in an Array formula. I can send you a sample spready with a very basic example, that totals numbers between two values in a table if that would help.

[LisaContractorUmbrella]

OK, so you're looking at using SUMIFS in an Array formula. I can send you a sample spready with a very basic example, that totals numbers between two values in a table if that would help.

I've used this for the date part which will return all figures submitted in the same month =SUM(IF(MONTH($E$2:$E$1000)=MONTH(E2),$H$2:$H$26)) but I think I need to nest it with something else to get a total for each reference for each month e.g.

Reference date Amount Total (required formula)

12345 20-5-14 £50.00
12345 28-5-14 £50.00 £100.00 (total for May)
12345 02-06-14 £25.00
12345 08-06-14 £25.00 £50.00 (total for June)

56789 20-5-14 £100.00 £100.00 (total May)
56789 07-06-14 £100.00 £100.00 (total June)
56789 07-07-14 £100.00 £100.00 (total July)

Isn't this fun

[LisaContractorUmbrella]

Turns out it was easier that than - converted date to month number and then used SUMIFS() on reference and month number Nearly there

[woohoo]

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

Not sure how you want the differences displayed, so cant help further.

[LisaContractorUmbrella]

Not sure how you want the differences displayed, so cant help further.

Not to worry - sorted it in the end - thanks for your help everyone

[darrylmg]

A late suggestion to the party, but...
Use a macro to add a new column to all sheets, with the column values being populated with the workbook name and sheet name.
Then record a macro to copy all rows from all sheets into one big momma sheet, then merge those two "master" sheets from both workbooks.
You'll have all data in one big sheet with an extra column that tells you where the row came from. Sort the rows and then you can see, with a bit of eyeballing, where you have missing rows.