Reply to: I need the help of someone clever

[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.

[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

[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]

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

[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

[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]

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]

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]

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

Unfortunately not

[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]

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]

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]

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

[lilelvis2000]

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

[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.