Jump to content

This topic is 3692 days old. Please don't post here. Open a new topic instead.

Recommended Posts

I've been trying to write a Custom Function that will give me a list of unique combinations between two occurrences of the same list.

 

The nearest I've come is with this Function, using the following list as an example 1,2,3,4

 

ListCombinations ( listofValues ; separator )

 

Let([
listLength = ValueCount(listOfValues);
lastValue = GetValue(listOfValues; listLength);
lesserList = Case(
listLength > 1; ListCombinations(LeftValues(listOfValues; listLength -1 ); separator);
"")
];
Case(
listLength = 0; "";
listLength = 1; RightValues(listOfValues; 1); /* Ensures ¶ at end */
lastValue & "¶" &
lesserList & /* already terminated by ¶ */
Substitute(lesserList; "¶"; separator & lastValue & "¶")
)
)
 
The output comes out looking like this:

4

3

2

1

1 | 2

2 | 3

1 | 3

1 | 2 | 3

3 | 4

2 | 4

1 | 4

1 | 2 | 4

2 | 3 | 4

1 | 3 | 4

1 | 2 | 3 | 4

 

What I want is for the output to be limited to only the 2 number values, shown below.  I've had a play with the Function, but I think I've been looking at it too long and can't make hide nor hair of what I need to change to produce the below list :idot:  

 

1 | 2

2 | 3

1 | 3

3 | 4

2 | 4

1 | 4

 

Thank You for An Help in Advance  :)

 

 

 

Link to comment
Share on other sites

I went back and had a look at the calculations, and went through a bundle of CF's on Brian Dunnings Site...

 

This is what I am working with now, since it meets the requirement for two variables.  I added in the If Function highlighted below

 

Combine Values ( list1; list2; divider )

 

Let([$ƒCounter[1] = Case($ƒCounter[1]; $ƒCounter[1]; 1);
       $ƒCounter[2] = Case($ƒCounter[2]; $ƒCounter[2]; 1)
]; //end define Let
 
   Substitute( MiddleValues( list1; $ƒCounter[1]; 1 ); ¶; "" ) & divider &
   If ( MiddleValues ( list1; $ƒCounter[1]; 1) ≠ MiddleValues ( list2; $ƒCounter[2]; 1 ) ;
   Substitute( MiddleValues( list2; $ƒCounter[2]; 1 ); ¶; "" ) ; "0" ) &
 
   Case(
 
      $ƒCounter[2] < ValueCount(list2);
         Let( $ƒCounter[2] = $ƒCounter[2] + 1; ¶ & CombineValues(list1; list2; divider) );
 
      $ƒCounter[1] < ValueCount(list1);
         Let([$ƒCounter[1] = $ƒCounter[1] + 1; $ƒCounter[2] = 1]; ¶ & CombineValues(list1; list2; divider) );
 
      Let([$ƒCounter[1] = ""; $ƒCounter[2] = ""]; "") //clear variables when done
 
   ) //end Case
 
) //end Let
 
This gives me an output shown below
 
1 | 0

1 | 2

1 | 3

1 | 4

2 | 1

2 | 0

2 | 3

2 | 4

3 | 1

3 | 2

3 | 0

3 | 4

4 | 1

4 | 2

4 | 3

4 | 0

 

Now, I want to make it recognise that 1 | 2 is the same as 2 | 1. So basically, all the Italicised outputs just don't appear.  I'd also like to remove the 1 | 0 outputs from the list in the event that the GetValue of List is Even, but keep it for when GetValue is Odd.

 

Any Ideas? since I'm afraid to say I'm stumped again...  :idot:

 

Thanking  You in Advance  :)

Link to comment
Share on other sites

Brilliant... Works an absolute treat :)

 

Many Thanks, from both myself, and the hole I was banging into the wall with my head

:laugh:  :)  :shocked:

 

Sorry to say, I spoke a little too soon... When I realised, that given the usage for this CF, I will need those pesky 1 | 0 outputs to be included if the list of Values is Odd... But if it's even, then the 1 | 0 outputs need not be there...

 

Any thoughts Comment?

Link to comment
Share on other sites

I will need those pesky 1 | 0 outputs to be included if the list of Values is Odd... But if it's even, then the 1 | 0 outputs need not be there...

 

It seems to me you're attacking this at the wrong end. You should count the values before calling the function - and if the count comes out odd, add a value to the call.

  • Like 1
Link to comment
Share on other sites

This topic is 3692 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.