Return Above Cell for Numbers

If you encounter a number anywhere in a row, output the cell above that number along with the number.

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

Solving the challenge of Return Above Cell for Numbers with Power Query

Power Query solution 1 for Return Above Cell for Numbers, proposed by Bo Rydobon 🇹🇭:

let
  r = Table.ToRows(Excel.CurrentWorkbook(){[Name = "Table1"]}[Content]), 
  Ans = Table.FromColumns(
    List.Zip(
      List.Combine(
        List.Transform(
          List.RemoveLastN(List.Positions(r), 1), 
          (i) =>
            let
              x = List.Select(List.Zip({r{i}, r{i + 1}}), each _{1} is number)
            in
              List.Zip(x)
        )
      )
    )
  )
in
  Ans

Power Query solution 2 for Return Above Cell for Numbers, proposed by Zoran Milokanović:

let
  Source = Table.ToRows(Excel.CurrentWorkbook(){[Name = "Input"]}[Content]), 
  S = Table.FromColumns(
    List.Zip(
      List.Combine(
        List.RemoveItems(
          List.Transform(
            List.Positions(Source), 
            each 
              let
                R = Source{_}, 
                P = Source{_ - 1}
              in
                List.Zip(
                  List.Accumulate(
                    List.Positions(R), 
                    {}, 
                    (s, c) => s & (if Value.Is(R{c}, Int64.Type) then {{P{c}, R{c}}} else {})
                  )
                )
          ), 
          {{}}
        )
      )
    )
  )
in
  S

Power Query solution 3 for Return Above Cell for Numbers, proposed by Alejandro Simón 🇵🇦 🇪🇸:

let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Sol = Table.Combine(
    List.Transform(
      Table.Split(Source, 2), 
      each 
        let
          a = Table.ToColumns(_), 
          b = List.Select(a, (x) => x{1} is number), 
          c = Table.FromColumns(b)
        in
          c
    )
  )
in
  Sol

Power Query solution 4 for Return Above Cell for Numbers, proposed by Luan Rodrigues:

let
  Fonte = Tabela1, 
  res = Table.Combine(
    List.Transform(
      Table.Split(Fonte, 2), 
      (x) => Table.FromColumns(List.Select(Table.ToColumns(x), each _{1} is number))
    )
  )
in
  res

Power Query solution 5 for Return Above Cell for Numbers, proposed by Hussein SATOUR:

let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Custom1 = Table.Split(Source, 2), 
  #"Converted to Table" = Table.FromList(
    Custom1, 
    Splitter.SplitByNothing(), 
    null, 
    null, 
    ExtraValues.Error
  ), 
  #"Added Custom" = Table.AddColumn(
    #"Converted to Table", 
    "Custom", 
    each Table.Transpose(
      Table.SelectRows(
        Table.RemoveRowsWithErrors(
          Table.TransformColumnTypes(Table.Transpose([Column1]), {{"Column2", Int64.Type}}), 
          {"Column2"}
        ), 
        each [Column2] <> null
      )
    )
  ), 
  #"Removed Columns" = Table.RemoveColumns(#"Added Custom", {"Column1"}), 
  #"Expanded Custom" = Table.ExpandTableColumn(
    #"Removed  
  Columns", 
    "Custom", 
    {"Column1", "Column2", "Column3", "Column4"}, 
    {"Column1", "Column2", "Column3", "Column4"}
  ), 
  #"Removed Blank Rows" = Table.SelectRows(
    #"Expanded Custom", 
    each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null}))
  )
in
  #"Removed Blank Rows"

Power Query solution 6 for Return Above Cell for Numbers, proposed by Eric Laforce:

let
  Source = Excel.CurrentWorkbook(){[Name = "tData115"]}[Content], 
  N = Table.ColumnCount(Source), 
  FilterNum = List.Accumulate(
    List.Split(Table.ToRows(Source), 2), 
    {}, 
    (s, c) => s & {List.Select(List.Zip(c), each Value.Is(_{1}, type number))}
  ), 
  Transpose = List.Transform(
    List.Select(FilterNum, each (List.Count(_) > 0)), 
    each {
      List.Accumulate(_, {}, (s, c) => s & {c{0}}), 
      List.Accumulate(_, {}, (s, c) => s & {c{1}})
    }
  ), 
  ToTable = Table.FromRows(
    List.Transform(List.Combine(Transpose), each _ & List.Repeat({null}, N - List.Count(_)))
  )
in
  ToTable

Power Query solution 7 for Return Above Cell for Numbers, proposed by Sandeep Marwal:

let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  #"Changed Type" = Table.TransformColumnTypes(
    Source, 
    {{"Column1", type any}, {"Column2", type any}, {"Column3", type any}, {"Column4", type any}}
  ), 
  Custom1 = Table.Split(#"Changed Type", 2), 
  Custom3 = List.Transform(
    Custom1, 
    each Table.Transpose(
      Table.SelectRows(
        Table.RemoveRowsWithErrors(
          Table.TransformColumnTypes(Table.Transpose(_), {{"Column2", Int64.Type}}), 
          {"Column2"}
        ), 
        each [Column2] <> null
      )
    )
  ), 
  Custom2 = Table.Combine(Custom3)
in
  Custom2

Power Query solution 8 for Return Above Cell for Numbers, proposed by Szabolcs Phraner:

let
  Source = Excel.CurrentWorkbook(){[Name = "Table"]}[Content], 
  // Removes nulls and values from a row that equals an Upper case letter of the alphabet 
  RemoveJunkValues = each List.Select(_, each not List.Contains({"A" .. "Z", null}, _)), 
  // if the field count of row is less then the amount of columns needed, adds additional null values to prevent error in the last step 
  SupplementRows = each 
    let
      FieldCount  = List.Count(_), 
      ColumnCount = Table.ColumnCount(Source), 
      Insert      = List.Repeat({null}, ColumnCount - FieldCount)
    in
      if List.IsEmpty(_) then _ else List.InsertRange(_, FieldCount, Insert), 
  //Transform Source Table to list of rows and apply row transformations 
  TransformedRows = List.Accumulate(
    {RemoveJunkValues, SupplementRows}, 
    Table.ToRows(Source), 
    (row, transformation) => List.Transform(row, transformation)
  ), 
  RemoveEmptyRows = List.Select(TransformedRows, each not List.IsEmpty(_)), 
  // Converst nested list of rows back to table 
  TableFromRows = Table.FromRows(RemoveEmptyRows)
in
  TableFromRows

Solving the challenge of Return Above Cell for Numbers with Excel

Excel solution 1 for Return Above Cell for Numbers, proposed by Bo Rydobon 🇹🇭:

=LET(z,A2:D13,REDUCE(A1:D1,SEQUENCE(ROWS(z)),LAMBDA(a,r,LET(f,IFERROR(FILTER(CHOOSEROWS(z,r-1,r),INDEX(z,r,)<""),0),IF(@f>0,IFNA(VSTACK(a,f),""),a)))))

Excel solution 2 for Return Above Cell for Numbers, proposed by Bo Rydobon 🇹🇭:

=LET(z,A2:D13,REDUCE(A1:D1,SEQUENCE(ROWS(z)),LAMBDA(a,r,LET(x,DROP(REDUCE(0,SEQUENCE(COLUMNS(z)),
LAMBDA(b,c,IF(INDEX(z,r,c)<"",HSTACK(b,INDEX(z,r-{1;0},c)),b))),,1),
IF(ISERR(@x),a,IFNA(VSTACK(a,x),""))))))

Excel solution 3 for Return Above Cell for Numbers, proposed by محمد حلمي:

=LET(a,A2:D13,i,TEXTSPLIT(CONCAT(IFNA(
HSTACK(IF(N(+a)+N(+A3:D14),a,""),"/"),"/")&" ")," ","/",1,,""),
VSTACK(A1:D1,IFERROR(--i,i)))

Excel solution 4 for Return Above Cell for Numbers, proposed by محمد حلمي:

=LET(a,A2:D13,i,TEXTSPLIT(CONCAT(BYROW(IF(N(+a)+N(+A3:D14),a,""),LAMBDA(a,CONCAT(a&" ")))&"-")," ","-",1,,""),
VSTACK(A1:D1,IFERROR(--i,i)))

Excel solution 5 for Return Above Cell for Numbers, proposed by محمد حلمي:

=TEXTSPLIT( CONCAT(BYROW( IF(LEN(A2:D13)>1,A2:D13,""),LAMBDA(a,CONCAT(FILTER(a,a<>"","")&" ")))&"-")," ","-",1,,"")

Excel solution 6 for Return Above Cell for Numbers, proposed by Kris Jaganah:

=LET(a,A1:D1,b,A2:D13,c,VSTACK(a,TEXTSPLIT(TEXTJOIN("+",1,BYROW(IF(LEN(b)>1,b,""),LAMBDA(x,TEXTJOIN("-",1,x)))),"-","+",,,"")),IFERROR(--c,c))

Excel solution 7 for Return Above Cell for Numbers, proposed by Oscar Mendez Roca Farell:

=LET(_u,UNIQUE(IF(ISNUMBER(A3:D13),A2:D12&"|"&A3:D13,"|"),,1), IFNA(REDUCE(A1:D1, SEQUENCE(ROWS(_u)), LAMBDA(i, x, VSTACK(i, TRANSPOSE(TEXTSPLIT(CONCAT(INDEX(_u,x,)&"-"), "|", "-", 1))))), ""))

Excel solution 8 for Return Above Cell for Numbers, proposed by Sunny Baggu:

=LET(
 _a, ISNUMBER(A2:D13),
 _b, VSTACK(DROP(_a, 1), TAKE(_a, -1)),
 _c, IF(_a + _b, A2:D13, 1 / x),
 _d, BYROW(ISERR(_c), LAMBDA(a, NOT(AND(a)))),
 _e, FILTER(_c, _d),
 IFNA(
 DROP(
 REDUCE(
 "",
 SEQUENCE(ROWS(_e)),
 LAMBDA(a, v, VSTACK(a, TOROW(INDEX(_e, v, ), 3)))
 ),
 1
 ),
 ""
 )
)

Excel solution 9 for Return Above Cell for Numbers, proposed by Diarmuid Early:

=LET(dt,A2:D13,
 hd,A1:D1,
 rws,SEQUENCE(ROWS(dt)),
 numRws,MAP(rws,LAMBDA(n,SUM(INDEX(dt,n,0))>0)),
 IFNA(REDUCE(hd,FILTER(rws,numRws),
 LAMBDA(a,v,VSTACK(a,
 FILTER(CHOOSEROWS(dt,v-1,v),ISNUMBER(CHOOSEROWS(dt,v)))
 ))),""))

Solving the challenge of Return Above Cell for Numbers with Python in Excel

Python in Excel solution 1 for Return Above Cell for Numbers, proposed by Bo Rydobon 🇹🇭:

df = xl("A2:D13")
pd.DataFrame(y for r in df.index if (y:=[v for c in range(len(df.columns)) if str((v:=df.values[r,c])).isnumeric() or str(df.values[min(r+1,len(df.index)-1),c]).isnumeric()])).fillna('')

Python in Excel solution 2 for Return Above Cell for Numbers, proposed by Bo Rydobon 🇹🇭:

df = xl("A1:D13", headers=True)
ar = [y for r in df.index if (y:=[list(x) for x in np.transpose([df.values[r-1:r+1,c] for c in range(4) if str(df.values[r,c]).isnumeric()])])]
y=[]
for a in ar:
 y.append(a[0])
 y.append(a[1])
 
pd.DataFrame(y).fillna('')
Still, trying to find  list comprehension to change ar to y  but failed

&&&

Solving the challenge of Return Above Cell for Numbers with Power Query

Solving the challenge of Return Above Cell for Numbers with Excel

Solving the challenge of Return Above Cell for Numbers with Python in Excel

Leave a Reply Cancel reply