Home » Signed Permutation Generator

Signed Permutation Generator

This problem is contributed by Sunny Baggu Given 2 input numbers, create all possible permutations considering numbers as both positive and negative. Permutation means order will matter, hence (a, b) and (b, a) both are different. Hence for 1 and 2 => (1, 2), (2, 1), (-1, 2), (2, -1), (1, -2), (-2, 1), (-1, -2) and (-2, -1).

📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 675
Challenge Difficulty: ⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn

Solving the challenge of Signed Permutation Generator with Power Query

_x000D_
Power Query solution 1 for Signed Permutation Generator, proposed by Zoran Milokanović: 
let
  Source = Table.ToRows(Excel.CurrentWorkbook(){[Name = "Input"]}[Content]){0}, 
  _ = Table.FromRows(
    List.TransformMany(
      Source, 
      each List.TransformMany({1, - 1}, each {1, - 1}, (i, _) => {i, _}), 
      (i, _) => {i * _{0}, List.Difference(Source, {i}){0} * _{1}}
    )
  )
in
  _
_x000D_ _x000D_
Power Query solution 2 for Signed Permutation Generator, proposed by Ramiro Ayala Chávez: 
let
  S   = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  a   = S[num1] & S[num2], 
  b   = List.Transform(a, each _ * - 1), 
  c   = List.Combine(List.Zip({a, b})), 
  d   = List.TransformMany(c, (x) => c, (x, y) => {x, y}), 
  e   = List.Select(d, each Number.Abs(_{0}) <> Number.Abs(_{1})), 
  Sol = Table.FromRows(e, Table.ColumnNames(S))
in
  Sol
_x000D_ _x000D_
Power Query solution 3 for Signed Permutation Generator, proposed by Seokho MOON: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Input = Table.ToRows(Source){0}, 
  Fun = (a as list) => List.TransformMany({a{0}, - a{0}}, (x) => {a{1}, - a{1}}, (x, y) => {x, y}), 
  Res = Table.FromRows(Fun(Input) & Fun(List.Reverse(Input)))
in
  Res
_x000D_

Solving the challenge of Signed Permutation Generator with Excel

_x000D_
Excel solution 1 for Signed Permutation Generator, proposed by Bo Rydobon 🇹🇭: 
=LET(s,
    SEQUENCE(
        8
    )-1,
    INDEX(TOCOL(
        B3:C3*{1;-1},
        ,
        1
    ),
    HSTACK(s/2+1,
    MOD(
        s,
        2
    )+1+2*(s<4))))
_x000D_ _x000D_
Excel solution 2 for Signed Permutation Generator, proposed by Rick Rothstein: 
=LET(c,HSTACK(B3:C3,-B3:C3),t,TOCOL(c&" "&TOCOL(c)),DROP(REDUCE("",FILTER(t,ABS(TEXTBEFORE(t," "))<>ABS(TEXTAFTER(t," "))),LAMBDA(a,x,VSTACK(a,0+TEXTSPLIT(x," ")))),1))
_x000D_ _x000D_
Excel solution 3 for Signed Permutation Generator, proposed by John V.: 
=LET(i,ROW(1:8)-1,INDEX(B3:C3,ABS({1,2}-3*(i>3)))*-1^HSTACK(INT(i/2),i))

or

✅
=LET(i,-1^{0,0;0,1;1,0;1,1},VSTACK(i*B3:C3,i*HSTACK(C3,B3)))
_x000D_ _x000D_
Excel solution 4 for Signed Permutation Generator, proposed by Kris Jaganah: 
=LET(a,VSTACK(B3,-B3),b,VSTACK(C3,-C3),c,TOCOL(VSTACK(a&","&TOROW(b),b&","&TOROW(a))),--HSTACK(TEXTSPLIT(c,","),TEXTAFTER(c,",")))
_x000D_ _x000D_
Excel solution 5 for Signed Permutation Generator, proposed by Timothée BLIOT: 
=LET(
    A,
    {1;-1;1;-1},
    B,
    {1;1;-1;-1},
    H,
    HSTACK,
    VSTACK(
        H(
            B*B3,
            A*C3
        ),
        H(
            B*C3,
            A*B3
        )
    )
)
_x000D_ _x000D_
Excel solution 6 for Signed Permutation Generator, proposed by Duy Tùng: 
=LET(
    a,
    TOCOL(
        {"";"-"}&B3:C3,
        ,
        1
    ),
    b,
    TEXTSPLIT(
        ARRAYTOTEXT(
            a&"/"&TOROW(
                a
            )
        ),
        "/",
        ", "
    ),
    --FILTER(
        b,
        MMULT(
            ABS(
                b
            ),
            {1;-1}
        )
    )
)
_x000D_ _x000D_
Excel solution 7 for Signed Permutation Generator, proposed by Sunny Baggu: 
=LET(
 _r, HSTACK(B3:C3, -B3:C3),
 _c, TOCOL(_r),
 _a, TOCOL(_c & " " & _r),
 _x, TEXTBEFORE(_a, " "),
 _y, TEXTAFTER(_a, " "),
 _v, FILTER(HSTACK(_x, _y), ABS(--_x) <> ABS(--_y)),
 TEXT(SORTBY(_v, ABS(TAKE(_v, , 1)), -1), "0.00")
)
_x000D_ _x000D_
Excel solution 8 for Signed Permutation Generator, proposed by LEONARD OCHEA 🇷🇴: 
=LET(i,B3:C3,h,HSTACK(i,-i),v,TOCOL(h),TEXTSPLIT(CONCAT(TOCOL(IF((h=v)+(h=-v),z,h&"|"&v&";"),3)),"|",";",1))
_x000D_ _x000D_
Excel solution 9 for Signed Permutation Generator, proposed by Anshu Bantra: 
=LET(
    
     nums_,
     TOCOL(
         VSTACK(
             B3:C3,
              -1 * B3:C3
         )
     ),
    
     arr_,
     TOCOL(
         MAKEARRAY(
             COUNT(
                 nums_
             ),
              COUNT(
                 nums_
             ),
              LAMBDA(
                  x,
                   y,
                   TEXTJOIN(
                       ",",
                        ,
                        x,
                        y
                   )
              )
         )
     ),
    
     seq_1_,
     INDEX(
         nums_,
          TEXTBEFORE(
              arr_,
               ","
          )
     ),
    
     seq_2_,
     INDEX(
         nums_,
          TEXTAFTER(
              arr_,
               ","
          )
     ),
    
     FILTER(
         HSTACK(
             seq_1_,
              seq_2_
         ),
          ABS(
              seq_1_
          ) <> ABS(
              seq_2_
          )
     )
    
)
_x000D_ _x000D_
Excel solution 10 for Signed Permutation Generator, proposed by Md. Zohurul Islam: 
=LET(x,B3,y,C3,u,{1;1;-1;-1;1;1;-1;-1},v,{1;-1;1;-1;1;-1;1;-1},a,HSTACK(x*u,y*v),b,TAKE(a,ROWS(a)/2),c,SORT(SORTBY(DROP(a,ROWS(a)/2),SEQUENCE(,COLUMNS(a)),-1),1,-1),d,VSTACK(b,c),d)
_x000D_ _x000D_
Excel solution 11 for Signed Permutation Generator, proposed by Pieter de B.: 
=LET(L,LAMBDA(x,y,TOCOL(x*{-1;1}&" "&TOROW(y*{-1,1})&"•")),-TEXTSPLIT(CONCAT(VSTACK(L(B3,C3),L(C3,B3)))," ","•",1))
_x000D_ _x000D_
Excel solution 12 for Signed Permutation Generator, proposed by Imam Hambali: 
=LET(
    a,
     TOCOL(
         VSTACK(
             B3:C3,
             B3:C3*-1
         ),
         ,
         1
     ),
     b,
     a&";"&TOROW(
         a
     ),
     c,
     TEXTSPLIT(
         TEXTJOIN(
             ":",
             1,
             b
         ),
         ";",
         ":"
     ),
     FILTER(
         c,
         ABS(
             TAKE(
                 c,
                 ,
                 1
             )
         )<>ABS(
             TAKE(
                 c,
                 ,
                 -1
             )
         )
     )
)
_x000D_ _x000D_
Excel solution 13 for Signed Permutation Generator, proposed by Gerson Pineda: 
=LET(i,{1,1;1,-1;-1,1;-1,-1}*B3:C3,VSTACK(i,CHOOSECOLS(SORT(i,2,-1),2,1)))
_x000D_ _x000D_
Excel solution 14 for Signed Permutation Generator, proposed by Ziad A.: 
=LET(
    _,
    {1,
    1;1,
    -1;-1,
    1;-1,
    -1},
    VSTACK(
        B3:C3*_,
        HSTACK(
            C3,
            B3
        )*_
    )
)
_x000D_ _x000D_
Excel solution 15 for Signed Permutation Generator, proposed by Ziad A.: 
=INDEX(
    LET(
        _,
        {1,
        1;1,
        -1;-1,
        1;-1,
        -1},
        {B3:C3*_;{C3,
        B3}*_}
    )
)
_x000D_ _x000D_
Excel solution 16 for Signed Permutation Generator, proposed by Jorge Alvarez: 
=LET(
    _i1;
    SECUENCIA(
        COLUMNAS(
            B3:C3
        )^2
    );
    
     _v1;
    INDICE(
        B3;
        SECUENCIA(
            _i1;
            ;
            1;
            0
        )
    );
    
     _r1;
    SI(
        _i1<=2;
        _v1;
        -_v1
    );
    
     _v2;
    INDICE(
        C3;
        SECUENCIA(
            _i1;
            ;
            1;
            0
        )
    );
    
     _r2;
    SI(
        _i1<=2;
        _v2;
        -_v2
    );
    
     _re1;
    APILARV(
        _r1;
        _r2
    );
    
     _r3;
    SI(
        RESIDUO(
            _i1;
            2
        )=1;
        _v2;
        -_v2
    );
    
     _r4;
    SI(
        RESIDUO(
            _i1;
            2
        )=1;
        _v1;
        -_v1
    );
    
     _re2;
    APILARV(
        _r3;
        _r4
    );
    
     APILARH(
         _re1;
         _re2
     )
)
_x000D_

Solving the challenge of Signed Permutation Generator with Python

_x000D_
Python solution 1 for Signed Permutation Generator, proposed by Konrad Gryczan, PhD: 
import pandas as pd
import itertools
path = "675 Permutations with signs.xlsx"
input = pd.read_excel(path, usecols="B:C", nrows=2, skiprows=1)
test = pd.read_excel(path, usecols="B:C", skiprows=4, nrows=9, names=["Var1", "Var2"]).sort_values(by=["Var1", "Var2"]).reset_index(drop=True)
a, b = input.iloc[0, 0], input.iloc[0, 1]
combinations = list(itertools.product([a, -a], [b, -b])) + list(itertools.product([b, -b], [a, -a]))
results = pd.DataFrame(combinations, columns=['Var1', 'Var2']).sort_values(by=['Var1', 'Var2']).reset_index(drop=True)
print(results.equals(test)) # True
_x000D_ _x000D_
Python solution 2 for Signed Permutation Generator, proposed by Anshu Bantra: 
from itertools import permutations
lst = to_list(REF("B3:C3"))
lst = lst + [-num for num in lst]
[(a,b) for a, b in permutations(lst, 2) if abs(a) != abs(b) ]
_x000D_

Solving the challenge of Signed Permutation Generator with Python in Excel

_x000D_
Python in Excel solution 1 for Signed Permutation Generator, proposed by Alejandro Campos: 
def gen_perm(n1, n2):
 return [(x*n1, y*n2) for x in (1, -1) for y in (1, -1)] + 
 [(x*n2, y*n1) for x in (1, -1) for y in (1, -1)]
gen_perm(xl("B3"), xl("C3"))
_x000D_

Solving the challenge of Signed Permutation Generator with R

_x000D_
R solution 1 for Signed Permutation Generator, proposed by Konrad Gryczan, PhD: 
library(tidyverse)
library(readxl)
path = "Excel/675 Permutations with signs.xlsx"
input = read_excel(path, range = "B2:C3")
test  = read_excel(path, range = "B6:C13", col_names = c("Var1", "Var2")) %>% arrange(Var1, Var2)
a = input$num1
b = input$num2 
as = c(a, -a)
bs = c(b, -b)
df = rbind(expand.grid(as, bs), expand.grid(bs, as)) %>% as.data.frame() %>%
 arrange(Var1, Var2)
all.equal(df, test, check.attributes = FALSE)
#> [1] TRUE
_x000D_ &&&

Leave a Reply