Assume four columns - A, B, C, D, filled with values (double prec. format);
assume a row (R50), which shows a min() value for each of the columns; assume
another row (R51), which shows a sum() of the four values in R50; assume
another row (R52) which, via index() and match(), shows the row positions of
the data values that generate the result in R51; generally - what I'm
searching this way is the smallest possible aggregate of four values, one
from each column.. The problem: sometimes the values at coordinates shown in
R52 are not 'valid' (the data values are parameters of objects and only
special objects can be put together) and in that case, I need to retreive the
'next smallest aggregate' following the one in R51. I think of an algo:
(((each A sum each B) sum each C) sum each D) = we have a set of all
possible sums; sort this set ascending; pick first, pick second, ....; but:

1.) how to do this??

2.)for 50 values in each column this would generate a huge set of values;
can you do in Excel like - hold this set in memory and only give my worksheet
the results?

3.)even if I get to picking the e.g. five smallest sums of the set, how do I
find out the row coordinates of the values that generated this sum?

Can someone please help me to solve this beast of a problem?

I am only a student, not a guru, but...

You can avoid the problem with (R52) not giving valid results by fixing it
at the source. Define a sub-set of the values in column A that are valid.
Instead of getting the MIN of column A, get the MIN of the sub-set. The same
for the other columns. Then (R52) should always be valid.
--
Gary''s Student - gsnu200753


"Mac" wrote:

Assume four columns - A, B, C, D, filled with values (double prec. format);
assume a row (R50), which shows a min() value for each of the columns; assume
another row (R51), which shows a sum() of the four values in R50; assume
another row (R52) which, via index() and match(), shows the row positions of
the data values that generate the result in R51; generally - what I'm
searching this way is the smallest possible aggregate of four values, one
from each column.. The problem: sometimes the values at coordinates shown in
R52 are not 'valid' (the data values are parameters of objects and only
special objects can be put together) and in that case, I need to retreive the
'next smallest aggregate' following the one in R51. I think of an algo:
(((each A sum each B) sum each C) sum each D) = we have a set of all
possible sums; sort this set ascending; pick first, pick second, ....; but:

1.) how to do this??

2.)for 50 values in each column this would generate a huge set of values;
can you do in Excel like - hold this set in memory and only give my worksheet
the results?

3.)even if I get to picking the e.g. five smallest sums of the set, how do I
find out the row coordinates of the values that generated this sum?

Can someone please help me to solve this beast of a problem?

If only it were this simple..:-) There's more to this and didn't mention that
information to keep my post readable, however that would clarify the whole
thing: next to each row, there's another one which keeps the names of the
objects whose parameter are in A, B, C and D; and only looking at the result
in R52 I can see - ok, these are the 4 objects and then I have to check
another sheet to see if this quaternion of objects is 'valid'; and if it's
not, then I need the next 'minimal sum' which gives another quaternion of
objects...that's the whole scenario and you can see that I can't do the check
that you suggest....oh well..

"Gary''s Student" wrote:

I am only a student, not a guru, but...

You can avoid the problem with (R52) not giving valid results by fixing it
at the source. Define a sub-set of the values in column A that are valid.
Instead of getting the MIN of column A, get the MIN of the sub-set. The same
for the other columns. Then (R52) should always be valid.
--
Gary''s Student - gsnu200753


"Mac" wrote:

Assume four columns - A, B, C, D, filled with values (double prec. format);
assume a row (R50), which shows a min() value for each of the columns; assume
another row (R51), which shows a sum() of the four values in R50; assume
another row (R52) which, via index() and match(), shows the row positions of
the data values that generate the result in R51; generally - what I'm
searching this way is the smallest possible aggregate of four values, one
from each column.. The problem: sometimes the values at coordinates shown in
R52 are not 'valid' (the data values are parameters of objects and only
special objects can be put together) and in that case, I need to retreive the
'next smallest aggregate' following the one in R51. I think of an algo:
(((each A sum each B) sum each C) sum each D) = we have a set of all
possible sums; sort this set ascending; pick first, pick second, ....; but:

1.) how to do this??

2.)for 50 values in each column this would generate a huge set of values;
can you do in Excel like - hold this set in memory and only give my worksheet
the results?

3.)even if I get to picking the e.g. five smallest sums of the set, how do I
find out the row coordinates of the values that generated this sum?

Can someone please help me to solve this beast of a problem?

I see your point. Maybe we can take advantage of the acceptability criteria.
There are 6,250,000 possible quads. Does the object criteria sheet have
this many entries or is a simplification possible?
--
Gary''s Student - gsnu200753


"Mac" wrote:

If only it were this simple..:-) There's more to this and didn't mention that
information to keep my post readable, however that would clarify the whole
thing: next to each row, there's another one which keeps the names of the
objects whose parameter are in A, B, C and D; and only looking at the result
in R52 I can see - ok, these are the 4 objects and then I have to check
another sheet to see if this quaternion of objects is 'valid'; and if it's
not, then I need the next 'minimal sum' which gives another quaternion of
objects...that's the whole scenario and you can see that I can't do the check
that you suggest....oh well..

"Gary''s Student" wrote:

I am only a student, not a guru, but...

You can avoid the problem with (R52) not giving valid results by fixing it
at the source. Define a sub-set of the values in column A that are valid.
Instead of getting the MIN of column A, get the MIN of the sub-set. The same
for the other columns. Then (R52) should always be valid.
--
Gary''s Student - gsnu200753


"Mac" wrote:

Assume four columns - A, B, C, D, filled with values (double prec. format);
assume a row (R50), which shows a min() value for each of the columns; assume
another row (R51), which shows a sum() of the four values in R50; assume
another row (R52) which, via index() and match(), shows the row positions of
the data values that generate the result in R51; generally - what I'm
searching this way is the smallest possible aggregate of four values, one
from each column.. The problem: sometimes the values at coordinates shown in
R52 are not 'valid' (the data values are parameters of objects and only
special objects can be put together) and in that case, I need to retreive the
'next smallest aggregate' following the one in R51. I think of an algo:
(((each A sum each B) sum each C) sum each D) = we have a set of all
possible sums; sort this set ascending; pick first, pick second, ....; but:

1.) how to do this??

2.)for 50 values in each column this would generate a huge set of values;
can you do in Excel like - hold this set in memory and only give my worksheet
the results?

3.)even if I get to picking the e.g. five smallest sums of the set, how do I
find out the row coordinates of the values that generated this sum?

Can someone please help me to solve this beast of a problem?

Well the point is, that the only criteria available is - look into these
6,250,000 quads starting with the smallest and if you find a quad where each
number points to a different object (that will tell us the object's name in
the neighbouring column) - BINGO! and stop looking any further. So you might
need 1000 numbers or you can go with the first....but the bottom line is -
each number in a quad must point to a different object. Now you led me to
think...could we somehow extract these out from the 6,250,000 bunch?

"Gary''s Student" wrote:

I see your point. Maybe we can take advantage of the acceptability criteria.
There are 6,250,000 possible quads. Does the object criteria sheet have
this many entries or is a simplification possible?
--
Gary''s Student - gsnu200753


"Mac" wrote:

If only it were this simple..:-) There's more to this and didn't mention that
information to keep my post readable, however that would clarify the whole
thing: next to each row, there's another one which keeps the names of the
objects whose parameter are in A, B, C and D; and only looking at the result
in R52 I can see - ok, these are the 4 objects and then I have to check
another sheet to see if this quaternion of objects is 'valid'; and if it's
not, then I need the next 'minimal sum' which gives another quaternion of
objects...that's the whole scenario and you can see that I can't do the check
that you suggest....oh well..

"Gary''s Student" wrote:

I am only a student, not a guru, but...

You can avoid the problem with (R52) not giving valid results by fixing it
at the source. Define a sub-set of the values in column A that are valid.
Instead of getting the MIN of column A, get the MIN of the sub-set. The same
for the other columns. Then (R52) should always be valid.
--
Gary''s Student - gsnu200753


"Mac" wrote:

Assume four columns - A, B, C, D, filled with values (double prec. format);
assume a row (R50), which shows a min() value for each of the columns; assume
another row (R51), which shows a sum() of the four values in R50; assume
another row (R52) which, via index() and match(), shows the row positions of
the data values that generate the result in R51; generally - what I'm
searching this way is the smallest possible aggregate of four values, one
from each column.. The problem: sometimes the values at coordinates shown in
R52 are not 'valid' (the data values are parameters of objects and only
special objects can be put together) and in that case, I need to retreive the
'next smallest aggregate' following the one in R51. I think of an algo:
(((each A sum each B) sum each C) sum each D) = we have a set of all
possible sums; sort this set ascending; pick first, pick second, ....; but:

1.) how to do this??

2.)for 50 values in each column this would generate a huge set of values;
can you do in Excel like - hold this set in memory and only give my worksheet
the results?

3.)even if I get to picking the e.g. five smallest sums of the set, how do I
find out the row coordinates of the values that generated this sum?

Can someone please help me to solve this beast of a problem?

Mac,

If think you need to approach this from the opposite end - use your 'valid quaternions' list to
calculate all possible solutions, and pick the minimum from that list of solutions generated.

How do your list the valid quaternion combinations? and what does that number mean - is it the index
of the list, an offset from a row, ???

Anyway, post reduced example tables, both data and valid quaternions, and we'll go from there.

HTH,
Bernie
MS Excel MVP


"Mac" wrote in message
...
Well the point is, that the only criteria available is - look into these
6,250,000 quads starting with the smallest and if you find a quad where each
number points to a different object (that will tell us the object's name in
the neighbouring column) - BINGO! and stop looking any further. So you might
need 1000 numbers or you can go with the first....but the bottom line is -
each number in a quad must point to a different object. Now you led me to
think...could we somehow extract these out from the 6,250,000 bunch?

"Gary''s Student" wrote:

I see your point. Maybe we can take advantage of the acceptability criteria.
There are 6,250,000 possible quads. Does the object criteria sheet have
this many entries or is a simplification possible?
--
Gary''s Student - gsnu200753


"Mac" wrote:

If only it were this simple..:-) There's more to this and didn't mention that
information to keep my post readable, however that would clarify the whole
thing: next to each row, there's another one which keeps the names of the
objects whose parameter are in A, B, C and D; and only looking at the result
in R52 I can see - ok, these are the 4 objects and then I have to check
another sheet to see if this quaternion of objects is 'valid'; and if it's
not, then I need the next 'minimal sum' which gives another quaternion of
objects...that's the whole scenario and you can see that I can't do the check
that you suggest....oh well..

"Gary''s Student" wrote:

I am only a student, not a guru, but...

You can avoid the problem with (R52) not giving valid results by fixing it
at the source. Define a sub-set of the values in column A that are valid.
Instead of getting the MIN of column A, get the MIN of the sub-set. The same
for the other columns. Then (R52) should always be valid.
--
Gary''s Student - gsnu200753


"Mac" wrote:

Assume four columns - A, B, C, D, filled with values (double prec. format);
assume a row (R50), which shows a min() value for each of the columns; assume
another row (R51), which shows a sum() of the four values in R50; assume
another row (R52) which, via index() and match(), shows the row positions of
the data values that generate the result in R51; generally - what I'm
searching this way is the smallest possible aggregate of four values, one
from each column.. The problem: sometimes the values at coordinates shown in
R52 are not 'valid' (the data values are parameters of objects and only
special objects can be put together) and in that case, I need to retreive the
'next smallest aggregate' following the one in R51. I think of an algo:
(((each A sum each B) sum each C) sum each D) = we have a set of all
possible sums; sort this set ascending; pick first, pick second, ....; but:

1.) how to do this??

2.)for 50 values in each column this would generate a huge set of values;
can you do in Excel like - hold this set in memory and only give my worksheet
the results?

3.)even if I get to picking the e.g. five smallest sums of the set, how do I
find out the row coordinates of the values that generated this sum?

Can someone please help me to solve this beast of a problem?