Home » No Challenge Provided

No Challenge Provided

Solving the challenge of No Challenge Provided with Power Query

Power Query solution 1 for No Challenge Provided, proposed by Alejandro Simón 🇵🇦 🇪🇸: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Filter = Table.SelectRows(Source, each ([Words] <> null)), 
  LG = Table.AddColumn(
    Filter, 
    "A", 
    (k) =>
      let
        a = Text.Length(k[Words]), 
        b = List.Generate(
          () => [x = 1, y = 1, z = 0], 
          each [z] <= 1, 
          each [x = [x] + 1, y = [y] + x, z = if y < a then [z] else [z] + 1], 
          each [y]
        ), 
        c = Text.PadEnd(k[Words], List.Last(b), "#"), 
        d = Splitter.SplitTextByLengths({1 .. List.Count(b)})(c), 
        e = List.Transform(
          d, 
          each 
            let
              f = Text.ToList(_), 
              g = List.Repeat({null}, List.Count(f)), 
              h = List.RemoveLastN(List.Combine(List.Zip({f, g})))
            in
              h
        ), 
        i = List.Max(List.Transform(e, each List.Count(_))), 
        j = List.Transform(
          e, 
          each 
            let
              l = i - List.Count(_), 
              m = List.Repeat({null}, l / 2), 
              n = m & _ & m
            in
              n
        )
      in
        Table.FromRows(j)
  )[A], 
  Sol = Table.Combine(LG)
in
  Sol
Power Query solution 2 for No Challenge Provided, proposed by Mihai Radu O: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  s = Table.AddColumn(
    Source, 
    "r", 
    each [
      l = Text.Length([Words]), 
      fRS = (lst) =>
        List.Generate(
          () => [x = 0, y = lst{0}], 
          each [x] < List.Count(lst), 
          each [x = [x] + 1, y = [y] + lst{x}], 
          each [y]
        ), 
      a = {0 .. l - 1}, 
      b = fRS(a), 
      c = List.First(List.Select(b, (x) => (x - l) >= 0)), 
      w = [Words] & Text.Repeat("#", c - l), 
      sw = Text.ToList(w), 
      d = List.PositionOf(b, c), 
      nr = {1 .. d}, 
      p = fRS({0 .. d - 1}), 
      f = List.Transform(
        List.Zip({p, nr}), 
        (x) =>
          [
            f1   = Text.Combine(Text.ToList(Text.Middle(w, x{0}, x{1})), " "), 
            f2   = Text.Length(f1), 
            tRep = Text.Repeat(" ", ((d * 2 - 1) - f2) / 2), 
            f3   = Table.FromRows({Text.ToList(tRep & f1 & tRep)})
          ][f3]
      ), 
      g = Table.Combine(f)
    ][g]
  )
in
  s

Solving the challenge of No Challenge Provided with Excel

Excel solution 1 for No Challenge Provided, proposed by Bo Rydobon 🇹🇭: 
=LET(z,
    A18,
    n,
    DROP(REDUCE(0,
    SEQUENCE(ROUND((LEN(
        z
    )*2)^0.5,
    )),
    LAMBDA(
        a,
        i,
        IFNA(
            VSTACK(
                HSTACK(
                    0,
                    a
                ),
                SEQUENCE(
                    ,
                    i*2-1,
                    MAX(
                        a
                    )+2
                )
            ),
            0
        )
    )),
    1)/2,
    IF(n*(MOD(
        n,
        1
    )=0),
    LEFT(
        MID(
            z,
            n,
            1
        )&"#"
    ),
    ""))
=LET(z,
    A18,
    n,
    ROUND((LEN(
        z
    )*2)^0.5,
    ),
    s,
    SEQUENCE(
        n
    ),
    c,
    SEQUENCE(
        ,
        n*2-1
    ),
    t,
    (ABS(
        c-n
    )
Excel solution 2 for No Challenge Provided, proposed by John V.: 
=LET(w,A9,s,SEQUENCE,n,ROUND((2*LEN(w))^0.5,),c,s(,2*n-1),i,(ABS(c-n)
Excel solution 3 for No Challenge Provided, proposed by Julian Poeltl: 
=LET(W,
    A2,
    L,
    LEN(
        W
    ),
    S,
    XMATCH(
        1,
        L/SCAN(
            0,
            SEQUENCE(
                L
            ),
            LAMBDA(
                A,
                B,
                A+B
            )
        ),
        -1
    ),
    R,
    SEQUENCE(
        S
    ),
    C,
    SEQUENCE(
        ,
        S*2-1
    )-S,
    T,
    IF((ABS(
        C
    )
Excel solution 4 for No Challenge Provided, proposed by Timothée BLIOT: 
=LET(A,LEN(A2),B,SEQUENCE(A),C,MID(A2,B,1),E,XMATCH(A,B*(B+1)/2,1), MAKEARRAY(E,E*2-1,LAMBDA(x,y,IF(AND(y-x<=E-1,x+y>=E+1, ISODD(x+y+E)),XLOOKUP((x-1)*x/2+(y-E+x-1)/2+1,B,C,"#"),""))))

Solving the challenge of No Challenge Provided with Python in Excel

Python in Excel solution 1 for No Challenge Provided, proposed by Seokho MOON: 
Python in Excel
def triangle(txt):
 n = 1
 res = []
 while txt:
 length = len(txt)
 if n > length:
 txt += "#" * (n - length)
 add_text = " ".join(txt[:n])
 res.append(add_text)
 txt = txt[n:]
 n += 1
 max_length = len(res[-1])
 return [["" if c == " " else c for c in e.center(max_length)] for e in res]

Solving the challenge of No Challenge Provided with Excel VBA

Excel VBA solution 1 for No Challenge Provided, proposed by Andris Platais: 
Sub Challange_632()
intRow = ActiveCell.Row
intCol = ActiveCell.Column
 intRows = intRows + 1
 intLen = intLen + i
 If Len(strString) <= intLen Then
 strString = strString & WorksheetFunction.Rept("#", intLen - Len(strString))
 GoTo StringOK
 End If
Next i
StringOK:
j = 1
 c = intRows - r + 1
 For L = 1 To r
 Cells(intRow + r - 1, intCol + c).Value = Mid(strString, j, 1)
 j = j + 1
 c = c + 2
 Next L
Next r
End Sub

&&&

Leave a Reply