Variance analysis across a large dynamic dataset

[Mann750]

Hi ,

I need to find variances between each of the individual contracts in a large dataset. In the past I have used sumproduct and it has worked fine but with the level of information that I am looking at now it is running really slowly and I would need to seperate sumproduct formulations for each of the different variables. For example:

Contract 01/001 - I would like to see if there are any changes to the delivery dates, the product quantities, product type etc between the current downloaded information and the information processed a month before.

The end product should be some kind of summary stating which variables have been changed for which month/year of delivery for each contract.

I have thought about creating a macro that seperates the dataset into seperate sheets dependent on contract number and then comparing the information but because of the number of contracts (100+) I don't know if it would be feasible to create so many sheets in one workbook.

Any help would be appreciated.

Many thanks!

Screen shots of what the current dataset is like and what the possible proposed outcome could be to follow.
<!-- / message -->

 

[Excelestial]

These are the kinds of reports I construct regularly as part of my job. Please feel free to inbox me if you don't receive a solution...there are very many highly-skilled board members here. I will construct you a sample you can test upon your request. Your question is much more involved than board illustrations can quickly provide for.

 

[Mann750]

http://www.box.net/shared/uul4eqyhar0ftpma9u1b

Here is a link to the file with the possible proposed outcomes.

The "Customer Delivery Schedule" is the downloaded sheet from another source.

The "Finished" sheet is a result of the macro kindly created by hiker95 to help me with my initial problem. I have then created two worksheets; "200711" is a slightly modified version of the "Finished" sheet to help create a scenario of variance between the "240811" sheet, which is an exact replica of the "Finished" worksheet.

I have the included a "SUMMARY" sheet which gives an example of a possible outcome of the variance. It highlights the contracts with changes, gives the delivery dates of the changes and then just states "*" as a note of a change to the respective variable.

If I could have some help constructing a similar report or a better one that actually summarises all the variances in one sheet instead of having to go back to the other sheets to compare, then that would be great )

 

[Fazza]

hi, if your data is set up in tables, perhaps you can modify this to suit? http://www.mrexcel.com/forum/showthread.php?t=238791 regards

 

[Mann750]

Thanks Fazza I shall try out the methods in the thread and will reply back soon!!!

 

[hiker95]

Mann750,


You excluded two lines of comments from the macro.

Option Explicit
Sub ReorgDataV2()
' hiker95, 08/19/2011
' http://www.mrexcel.com/forum/showthread.php?t=572682

Looks like you have some good information on how to finish your project.

 

[Mann750]

Thank you all for your help! Fazza I have managed to manipulate your SQL code to suit my data and it works fine!!! Such a simple piece of code which works great! Many thanks D

 

[Fazza]

You're welcome