Sorting triplets...

[Guest]

Situation: chemical reactants are measured; samples are mixed, each sample is
measured in two different enviroments, samples are measured in triplets, the
result should be an ideal path of the reactant through the changes of the
environment.
Algorithm: triplets of values - the amount of triplets is given, the range
of values is given (confined) - I need to sort (extract from) these triplets
in a way that - all values from the given range are covered by the selected
triplets (which will cover the whole path of the reactant through the changes
of the environment) and each value repeats between two triplets (this covers
the measured differences). an example might look like:

From these:

{3,223;3,223;3,224}
{0,121;0,122;6,782}
{6,782;0,122;0,122}

I need to get these:

{3,223;0,122;6,782},{3,223;8,456;12,354},{33,012;8,456;6,782},{33,012;12,354;0,122}

This wouldn't be a problem if I had the time to code a simple C app...but -
how do I do this in Excel? Thank you for any ideas!

 

[paul.robinson]

Situation: chemical reactants are measured; samples are mixed, each sample is
measured in two different enviroments, samples are measured in triplets, the
result should be an ideal path of the reactant through the changes of the
environment.
Algorithm: triplets of values - the amount of triplets is given, the range
of values is given (confined) - I need to sort (extract from) these triplets
in a way that - all values from the given range are covered by the selected
triplets (which will cover the whole path of the reactant through the changes
of the environment) and each value repeats between two triplets (this covers
the measured differences). an example might look like:

From these:

{3,223;3,223;3,224}
{0,121;0,122;6,782}
{6,782;0,122;0,122}

I need to get these:

{3,223;0,122;6,782},{3,223;8,456;12,354},{33,012;8,456;6,782},{33,012;12,35­4;0,122}

This wouldn't be a problem if I had the time to code a simple C app...but-
how do I do this in Excel? Thank you for any ideas!

Hi
No idea whatsoever how you got from your first set of data to your
second set e.g where does the 8,465 and 12,354 come from in your
second data set - {3,223;8,456;12,354} ??

more explanation please!
regards
Paul

 

[Guest]

Ah well...that is just an illustrative example; let's say you have like 140
values coming in triplets - form these 140 you can create a set of distinct
values (which I have); now, some triplets are unique, some hold repeating
values - you need unique triplets only (which I have as well); then you look
at these triplets and impose two conditions: triplets you select cover all
(or as much as possible) of the distinct values; each value repeats among two
triplets.

 

[paul.robinson]

So
1. You have 140 triplets of numbers
2. From these triplets you extract all the unique values - lets call
this set V
3. From your original set of 140 triplets you discard any that have
repeating values. Let's call the remaining set T
4. Select a subset from T for which each value in V occurs exactly
twice. From your example it also appears you want triplets to have
ONLY one common number?

If this is what you mean then you might
Let S and N be empty sets
Select the first element of T, look through the other elements for one
with one common number that is not in N.
If you find such a common element,
add both elements to a new set S of triplets
Remove the two elements from the set T
Add the common number to the set N
If not
Remove the first element
2. iterate the above until T has only one element or is empty.
3. Check that the set N contains the set V. If not, the problem as
stated can't be achieved.

This could be done in Excel VBA using collections. The easiest format
for data would be three columns of data, where each row is an element
of T. The set V would be a single column, or the macro would generate
V.

I'm a bit busy at the mo to code that up, but if that's what you want
ans someone else wants to give it a go..
regards
Paul