# Permutation chart



## Stumpped (Dec 26, 2022)

I've been racking my brain other than by brute force (manually typing in all the numbers) is there a way to create a chart of numbers with the following criteria?

I have 2 conditions that have 0, 1 or 2 as the needed number (Active and Pending)
For each one of these 2 conditions, I have another set of 2 conditions, (REO and Short) also having 0, 1 or 2 for the numbers.
for example, I am working on some narratives for a project that I have.  the 0, 1, or 2 work fine for the numbers since anything that is 2 and above has certain verbiage, whereas 1 and zero have their own wordings.  



  Here is the chart I am trying to build.  Is there an easy way to calculate to the completion of this list without repeating a sequence of numbers?


----------



## Eric W (Dec 26, 2022)

Is this what you want?

Book1ABCDEF128ActiveREOShortPending SaleREOShort129000000130001001131002002132010010133011011134012012135020020136021021137022022138100100139101101140102102141110110142111111143112112144120120145121121146122122147200200148201201149202202150210210151211211152212212153220220154221221155222222Sheet1Cell FormulasRangeFormulaA129:F155A129=LET(s,SEQUENCE(27,,0),CHOOSE({1,2,3},INT(s/9),INT(MOD(s,9)/3),MOD(s,3)))Dynamic array formulas.

Please let us know what version of Excel you have, if you have a version without LET and SEQUENCE, there are other options.  Also, do you want a formula, or if this is a one-time creation would you be ok with VBA?


----------



## Stumpped (Dec 26, 2022)

Sorry, not quite, but it is close!  Sorry, I can see I forgot to mention that if there are no Active or pending.  then there cannot be any REO or short.  It seems to me that there are roughly more than 120 permutations.


----------



## Eric W (Dec 26, 2022)

OK, there are 9 combinations of Active and Pending (00, 01, 02, 10, 11, 12, 20, 21, 22).  For 00 there would be no REO or short, so 1 combination.  For 01 and 02, there would be no REO or short for Active, but there would be 3^2 = 9 combinations for Pending, so 18 combinations for both 01 and 02.  The same in reverse for 10, 20, so 18 more.  Then for 11, 12, 21, 22 there would be 3^4 combinations for 2 sets of REO and short or 81 for each of the 4 combinations, so 324 more combinations.  This leads to a total of 1 + 18 + 18 + 324 = 361.  Does this sound right?  And do you care if it's a formula or VBA?  Formulas often have issues with excluding combinations, but it's easy with VBA.


----------



## Stumpped (Dec 27, 2022)

Sounds a little on the high side, because if there are no Active or Pending there can't be any REO or short.   
As long as there are either 1 or 2 actives or pending then there can be 0, 1 or 2 REO or short.  
Even if an active or pending is 0 as long as the other has a 1 or 2 then there are the possibilities of 0, 1 or 2 for the active or pending that has a 1 or 2.
And.....even if there are one of either active or pending there can only be a combination of 0 or 1 for the REO or short because there can't be 2 since there are not yet 2 active or Pending.
You seem to be on the right track for being right for the total possible permutations but in some situations, some are not used. 

I was able to brute-force the list, 

I could use a vba script, to create the permutation list, but can't use it when I convert it to my appraisal software as its built-in worksheet only supports basic excel formulas, but does not support VBA. yet.  I have put in a request to add that, but it may be months before they get to it if they even do.


----------



## Stumpped (Dec 27, 2022)

The permutation calculations I need are a bit more complicated than just a simple formula that calculates the total possibilities.  Seems that I may need a few formulas to get the desired results, you are likely correct in saying I need a VBA script to get this done.


----------



## Eric W (Dec 27, 2022)

I'm still not quite clear on what you want.  It appears the biggest difference is that REO or short can't exceed the value of active or pending, which I didn't allow for.  Since you brute forced it, can you present the combinations?  I might be able to reverse engineer the list.  If possible, use XL2BB since that will allow me to manipulate your data without having to retype everything.


----------



## MEUserII (Dec 27, 2022)

Stumpped said:


> I've been racking my brain other than by brute force (manually typing in all the numbers) is there a way to create a chart of numbers with the following criteria?
> 
> I have 2 conditions that have 0, 1 or 2 as the needed number (Active and Pending)
> For each one of these 2 conditions, I have another set of 2 conditions, (REO and Short) also having 0, 1 or 2 for the numbers.
> ...


Let: Active = A, Pending = B, REO = C, and Short = D.
Let: A* = A & {0, 1, 2}, B* = B & {0, 1, 2}, C* = C & {0, 1, 2}, and D* = D & {0, 1, 2}.

If it is understood that A and B cannot switch places with each other, then the possible combinations for A and B are as follows:
A0&B*
A1&B*
A2&B*

Which is a total of nine combinations from: A0B0 to A2B2; or only numerically: 00 to 22.

Applying the same logic to only C and D, with it being understood C and D cannot switch places with each other, then the possible combinations for C and D are as follows.
C0&D*
C1&D*
C2&D*

Which is a total of nine combinations from: C0D0 to C2D2; or only numerically: 00 to 22.

Since each: A and B pair; has a corresponding: C and D pair; then your total pairs are as follows:
(A0&B0)&(C0&D0)
      *       *     *
(A0&B0)&(C2&D2)
      *       *     *
      *       *     *
(A2&B2)&(C0&D0)
      *       *     *
(A2&B2)&(C2&D2)

*Asterisks here represent mathematical dots continuing the sequence with double asterisks representing a jump in sequence in the A and B pair shift.

Which is a total of eighty-one combinations from: A0B0C0D0 to A2B2C2D2; or only numerically: 0000 to 2222.

If the above is correct, please let me know because then I can work on an Excel formula to generate it.

P.S. It seems Eric W has come up with about the similar approach along with an Excel formula, so solid solution from him.


----------



## Stumpped (Dec 27, 2022)

Honestly, I don't know much about VBA or how to even incorporate it into a spreadsheet.  I am an appraiser by trade but since it is slow, I am working on the narratives I can create based on certain data so I never have to type these statements ever again because the spreadsheet is built into my appraisal software.  It does not support VBA.  I've been able to do quite a bit without knowing much about programming by learning how to manipulate formulas to work with the worksheets I am building.  The question I have here was perhaps the most complex part of what I am working on.  Other parts are pretty easy in comparison.  Writing formulas to create a sentence that makes sense based on the supplied data is not as easy as it sounded, but I'm chipping away at it a day at a time.    I brute-forced the numbers and I ended up with a total of 89 possible combinations, I'm still going through them one by one to make sure I have not doubled any of the permutations.  I've already removed 3 doubled options so far.


----------

