Home » Minimum Sum Unique Pairs

Minimum Sum Unique Pairs

Find the unique pairs for which sum in minimum across the columns. So, answer needs to be listed for N1, N2, N3 and N4 columns separately.

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

Solving the challenge of Minimum Sum Unique Pairs with Power Query

Power Query solution 1 for Minimum Sum Unique Pairs, proposed by Aditya Kumar Darak 🇮🇳: 
let
  Source = Excel.CurrentWorkbook(){[Name = "data"]}[Content], 
  ToCols = Table.ToColumns(Source), 
  Return = List.Transform(
    ToCols, 
    each [M = List.MinN(_, 2), T = List.Transform(M, Text.From), R = Text.Combine(T, ", ")][R]
  )
in
  Return
Power Query solution 2 for Minimum Sum Unique Pairs, proposed by Alejandro Simón 🇵🇦 🇪🇸: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Min = List.Transform(
    Table.ToColumns(Source), 
    each 
      let
        a = List.MinN(_, 2), 
        b = Text.Combine(List.Transform(a, Text.From), ", ")
      in
        b
  ), 
  Sol = Table.FromColumns({Min}, {"Answer"})
in
  Sol
Power Query solution 3 for Minimum Sum Unique Pairs, proposed by Abdallah Ally: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Transform = List.Transform(
    Table.ToColumns(Source), 
    each [
      a = List.Zip({_, {0 .. List.Count(_) - 1}}), 
      b = List.FirstN(List.Sort(a, each _{0}), 2), 
      c = List.Transform(List.Sort(b, each _{1}), each _{0}), 
      d = Text.Combine(List.Transform(c, Text.From), ", ")
    ][d]
  ), 
  Result = Table.FromColumns({Transform}, {"My Answer"})
in
  Result
Power Query solution 4 for Minimum Sum Unique Pairs, proposed by Krzysztof Kominiak: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Result = Table.FromRows(
    List.Transform(
      Table.ToColumns(Source), 
      each {Text.From(List.MinN(_, 2){0}) & "; " & Text.From(List.MinN(_, 2){1})}
    ), 
    {"Answer Expected"}
  )
in
  Result
Power Query solution 5 for Minimum Sum Unique Pairs, proposed by Glyn Willis: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Custom1 = Table.FirstN(
    Table.FromColumns(
      List.Transform(Table.ToColumns(Source), each List.Sort(_)), 
      Table.ColumnNames(Source)
    ), 
    2
  ), 
  Custom2 = List.Transform(
    Table.ToColumns(Custom1), 
    (x) => Text.Combine(List.Transform(x, Text.From), ", ")
  )
in
  Custom2

Solving the challenge of Minimum Sum Unique Pairs with Excel

Excel solution 1 for Minimum Sum Unique Pairs, proposed by Bo Rydobon 🇹🇭: 
=TOCOL(BYCOL(A2:D10,LAMBDA(c,ARRAYTOTEXT(SMALL(c,{1,2})))))
Excel solution 2 for Minimum Sum Unique Pairs, proposed by Rick Rothstein: 
=TOCOL(BYCOL(A2:D10,LAMBDA(c,SMALL(c,1)&", "&SMALL(c,2))))
Excel solution 3 for Minimum Sum Unique Pairs, proposed by John V.: 
=TOCOL(BYCOL(A2:D10,LAMBDA(x,ARRAYTOTEXT(SMALL(x,{1;2})))))
Excel solution 4 for Minimum Sum Unique Pairs, proposed by Kris Jaganah: 
=TOCOL(BYCOL(A2:D10,LAMBDA(x,ARRAYTOTEXT(SMALL(x,{1;2})))))
Excel solution 5 for Minimum Sum Unique Pairs, proposed by Julian Poeltl: 
=TOCOL(BYCOL(A2:D10,LAMBDA(A,TEXTJOIN(", ",,SMALL(A,SEQUENCE(2))))))
Excel solution 6 for Minimum Sum Unique Pairs, proposed by Aditya Kumar Darak 🇮🇳: 
=MAP(
    SEQUENCE(
        COLUMNS(
            A2:D10
        )
    ),
     LAMBDA(
         a,
          ARRAYTOTEXT(
              SMALL(
                  INDEX(
                      A2:D10,
                       ,
                       a
                  ),
                   SEQUENCE(
                       2
                   )
              )
          )
     )
)
Excel solution 7 for Minimum Sum Unique Pairs, proposed by Timothée BLIOT: 
=TOCOL(BYCOL(A2:D10,LAMBDA(x,ARRAYTOTEXT(FILTER(x,SMALL(x,2)>=x)))))
Excel solution 8 for Minimum Sum Unique Pairs, proposed by Hussein SATOUR: 
=TOCOL(
    BYCOL(
        A2:D10,
        LAMBDA(
            x,
            ARRAYTOTEXT(
                TAKE(
                    SORT(
                        x
                    ),
                    2
                )
            )
        )
    )
)
Excel solution 9 for Minimum Sum Unique Pairs, proposed by Duy Tùng: 
=TOCOL(BYCOL(A2:D10,LAMBDA(v,ARRAYTOTEXT(SMALL(v,{1,2})))))
Excel solution 10 for Minimum Sum Unique Pairs, proposed by Sunny Baggu: 
=TOCOL(
    
     BYCOL(
         
          A2:D10,
         
          LAMBDA(
              a,
               ARRAYTOTEXT(
                   SMALL(
                       a,
                        {1; 2}
                   )
               )
          )
          
     )
    
)
Excel solution 11 for Minimum Sum Unique Pairs, proposed by Sunny Baggu: 
=TOCOL(BYCOL(A2:D10, LAMBDA(a, ARRAYTOTEXT(TAKE(SORT(a), 2)))))
Excel solution 12 for Minimum Sum Unique Pairs, proposed by Md. Zohurul Islam: 
=TOCOL(
    BYCOL(
        A2:D10,
        LAMBDA(
            x,
            LET(
                a,
                SORT(
                    x
                ),
                b,
                ARRAYTOTEXT(
                    TAKE(
                        a,
                        2
                    )
                ),
                b
            )
        )
    )
)
Excel solution 13 for Minimum Sum Unique Pairs, proposed by Hamidi Hamid: 
=TRANSPOSE(BYCOL(A2:D10,LAMBDA(a,ARRAYTOTEXT(SMALL(a,SEQUENCE(,2))))))
Excel solution 14 for Minimum Sum Unique Pairs, proposed by Imam Hambali: 
=TOCOL(BYCOL(A2:D10, LAMBDA(x, ARRAYTOTEXT(TAKE(SORT(x,,-1),-2)))))
Excel solution 15 for Minimum Sum Unique Pairs, proposed by Eddy Wijaya: 
=TOCOL(BYCOL(A2:D10,LAMBDA(c,TEXTJOIN(", ",,SMALL(c,{1,2})))))
Excel solution 16 for Minimum Sum Unique Pairs, proposed by Cary Ballard, DML: 
=TOCOL(MAKEARRAY(, COLUMNS(A2:D10), LAMBDA(r,c, ARRAYTOTEXT(SMALL(CHOOSECOLS(A2:D10, c), {1,2})))))
Excel solution 17 for Minimum Sum Unique Pairs, proposed by Ben Warshaw: 
=TRANSPOSE(BYCOL(A2:D10,LAMBDA(c,ARRAYTOTEXT(TAKE(SORT(c),2)))))
Excel solution 18 for Minimum Sum Unique Pairs, proposed by Cuong Pham: 
=TRANSPOSE(BYCOL(A2:D10,LAMBDA(a,TEXTJOIN(", ",,TAKE(SORT(a),2)))))
Excel solution 19 for Minimum Sum Unique Pairs, proposed by Ben Gutscher: 
=TRANSPOSE(BYCOL(A2:D11,LAMBDA(c,TEXTJOIN(", ",1,SMALL(c,{1,2})))))

Solving the challenge of Minimum Sum Unique Pairs with Python in Excel

Python in Excel solution 1 for Minimum Sum Unique Pairs, proposed by Alejandro Campos: 
import itertools
data = xl("A1:D10", headers=True)
min_pairs = {col: min(itertools.combinations(
 data[col], 2), key=sum) for col in data}
df = pd.DataFrame(min_pairs.items(), columns=['Column', 'Min Sum Pair'])
df['Min Sum Pair'] = df['Min Sum Pair'].apply(
 lambda x: f", ")
df['Min Sum Pair']
Python in Excel solution 2 for Minimum Sum Unique Pairs, proposed by Owen Price: 
https://www.linkedin.com/posts/owenhprice_data-analytics-excel-activity-7263210787035389952-merW?utm_source=share&utm_medium=member_desktop

Solving the challenge of Minimum Sum Unique Pairs with R

R solution 1 for Minimum Sum Unique Pairs, proposed by Konrad Gryczan, PhD: 
library(tidyverse)
library(readxl)
path = "Excel/588 Minimum Sum Pair.xlsx"
input = read_excel(path, range = "A1:D10")
test = read_excel(path, range = "F1:F5") %>% select(1) %>% pull() 
process_column = function(column) {
 grid = combn(column, 2) %>% t() %>% data.frame() %>% mutate(sum = X1 + X2) %>% arrange(sum) %>% head(1)
 paste0(grid$X1, ", ", grid$X2)
}
result = map(input, process_column) %>% unlist()
all.equal(result, test, check.attributes = FALSE)
#> [1] TRUE

&&&

Leave a Reply