finding multiple values that make up 1 value

[rickf19]

Hi

I am trying to find a way to find out the cells that make up a difference value on a spreadsheet that has over 100000 rows, tried solver but it is limited to 200 rows anyone any idea ?

Thanks
Rick

 

[Joe4]

There is a reason why Solver's limit is 200. Think about the magnitude of what you are asking.

Given "n" numbers, the formula for determining how many different combinations you can make from those numbers is (2^n)-1
You can see from the formula, that the choices grow exponentially with each additional choice.

So, for n=200, that would be:
=(2^200)-1
=1.60694E+60

So, that is a number with 61 characters to the left of the decimal point (WAY more than trillions - I don't even know that the word for a number than large is!)

And that is with just 200 numbers. Now imagine what it would be for 100000! Way over a Googol, maybe even approaching a Googolplex or more!

I think you would need some sort of super computer to try to perform those number of calculations, and even that may not be enough.

I don't know that you are going to be able to do what you want with that many numbers.

 

[rickf19]

Hi
Thanks for the reply, the difference value I need to detail is from two very large data sets which hold the same info in different ways but should be the same total. Unfortunately they very rarely are so I need to try and find why there are differences and correct them in our financials spreadsheets so that things are accurate I know what the difference value is but there are only about a dozen splits of the data where both tables have the same split even those can have at least hundreds of rows to check

Regards
Rick

 

[Joe4]

I would try to match up and remove as much as you can from the data sets.
Or see if there is any way to break it up into smaller batches.

If it were me, it is probably something I would use Microsoft Access for (to create the Matched and Unmatched records).

 

[rickf19]

Thanks again I have someone who tinkers with access on the team so I will ask them if they can help
By the way I came across this whilst searching for an answer and wondered if anyone has used it or would be interested in it Open Solver

An open source Solver-compatible optimization engine for Microsoft Excel on Windows and Mac using the Coin-OR CBC linear and integer programming optimizer. You can also use other solvers like Gurobi (if you have it installed), solvers on the NEOS cloud, and various non-linear solvers are supported too.

Features​

• Solver compatible for linear and integer models
• Powerful model display feature for model checking on your spreadsheet
• Uses the powerful COIN-OR CBC optimization engine
• GPL licensed

Regards
Rick

 

[Joe4]

I have never used/seen that program before, so I cannot really comment on it. But you may want to look at the limitations. I have serious doubts on whether it would be able to handle 100000 variables either. It is more the "nature of the beast". That is an astronomically huge number of possibilities!

I think whatever method you use, you are probably going to need to narrow down the scope somehow.

Good luck! Let us know if you find a working solution.

 

[Sufiyan97]

Yes please I would also like to know if there is a solution, I am using Accounts Receivable problem macro, which handles 60 to 80 values but if I add 100 or more then excel starts freezing and then I need to stop it from task manager.

 

[rickf19]

Hi guys
As you suspected there is no easy way to do this I ended up trying numerous methods but still ended up with thousands of rows of unmatched, was able to rec at a higher level by matching trans types but that list was huge so balance was hard to analyse, data. Eventually had to accept the inevitable and break the data into very small sets and sort by date then by ref no on one sheet and by date on the other and manually check as much off each date as possible, unfortunately this is going to be a regular thing but should only have to do the big match on new items added in the month