Home » Swap Alternate Rows

Swap Alternate Rows

Interchange the position of 1st row with 2nd, 3rd with 4th, 5th with 6th and so on.

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

Solving the challenge of Swap Alternate Rows with Power Query

Power Query solution 1 for Swap Alternate Rows, proposed by Alejandro Simón 🇵🇦 🇪🇸: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Data = Table.FromColumns(
    {
      List.Transform(
        List.Transform(
          List.Transform(List.Split(Source[Data], 2), each Table.FromList(_)), 
          each Table.AddIndexColumn(_, "Index", 1, 1)
        ), 
        each Table.Sort(_, {{"Index", Order.Descending}})
      )
    }
  ), 
  Result = Table.ExpandTableColumn(Data, "Column1", {"Column1"}, {"Expected Answer"})
in
  Result
Power Query solution 2 for Swap Alternate Rows, proposed by Luan Rodrigues: 
let
  Fonte = Data, 
  a = Table.AddIndexColumn(Fonte, "Índice", 1, 1, Int64.Type), 
  b = Table.SelectRows(
    Table.AddColumn(a, "Personalizar", each Number.IsOdd([Índice])), 
    each [Personalizar] = false
  )[Data], 
  c = Table.SelectRows(
    Table.AddColumn(a, "Personalizar", each Number.IsOdd([Índice])), 
    each [Personalizar] = true
  )[Data], 
  Result = List.Select(List.Combine(List.Zip({b, c})), each _ <> null)
in
  Result
Power Query solution 3 for Swap Alternate Rows, proposed by Brian Julius: 
let
  Source = List.Split(FlipRaw[Data], 2), 
  ListFlip = List.Transform(Source, each List.Reverse(_)), 
  ToTable = Table.ExpandListColumn(
    Table.FromList(ListFlip, Splitter.SplitByNothing(), {"Data"}, null, ExtraValues.Error), 
    "Data"
  )
in
  ToTable
Power Query solution 4 for Swap Alternate Rows, proposed by Bhavya Gupta: 
Power Query Submission -
Power Query solution 5 for Swap Alternate Rows, proposed by Jaroslaw Kujawa: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Data"]}[Content], 
  #"Added Index" = Table.AddIndexColumn(Source, "Index", 1), 
  #"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each Number.RoundUp([Index] / 2)), 
  #"Sorted Rows" = Table.Sort(
    #"Added Custom", 
    {{"Custom", Order.Ascending}, {"Index", Order.Descending}}
  )[Data]
in
  #"Sorted Rows"
Power Query solution 6 for Swap Alternate Rows, proposed by Matthias Friedmann: 
let
  Source         = Excel.CurrentWorkbook(){[Name = "ChangedPosition"]}[Content][Data], 
  PositionChange = List.Combine(List.Transform(List.Split(Source, 2), each List.Reverse(_)))
in
  PositionChange
Power Query solution 7 for Swap Alternate Rows, proposed by Venkata Rajesh: 
let
  Source = Data, 
  Output = List.Select(
    List.Combine(
      List.Zip({List.Alternate(Source[Data], 1, 1), List.Alternate(Source[Data], 1, 1, 1)})
    ), 
    each _ <> null
  )
in
  Output
Power Query solution 8 for Swap Alternate Rows, proposed by Jan Willem Van Holst: 
let
  Source = Table.FromRows(
    Json.Document(
      Binary.Decompress(
        Binary.FromText(
          "i45WCslMTy1SitWJVvLJzM8DM1xzUgsyEvNKIKKp+QWJRSlgdgBQMAOq2jkjNbUkMQPMDs/PSQMzvBLTSxMh8r75edmplUqxsQA=", 
          BinaryEncoding.Base64
        ), 
        Compression.Deflate
      )
    ), 
    let
      _t = ((type nullable text) meta [Serialized.Text = true])
    in
      type table [Data = _t]
  ), 
  AddedIndex = Table.AddIndexColumn(Source, "Index", 1, 1, Int64.Type), 
  Count = Table.RowCount(AddedIndex), 
  ShiftList = List.Repeat({1, - 1}, 4) & {0}, 
  TableShift = Table.FromColumns(
    Table.ToColumns(AddedIndex) & {ShiftList}, 
    {"Animal", "Index", "Shift"}
  ), 
  #"Changed Type" = Table.TransformColumnTypes(TableShift, {{"Shift", Int64.Type}}), 
  #"Inserted Addition" = Table.AddColumn(
    #"Changed Type", 
    "Addition", 
    each [Index] + [Shift], 
    Int64.Type
  ), 
  #"Merged Queries" = Table.NestedJoin(
    #"Inserted Addition", 
    {"Addition"}, 
    #"Inserted Addition", 
    {"Index"}, 
    "Inserted Addition", 
    JoinKind.LeftOuter
  ), 
  #"Expanded Inserted Addition" = Table.ExpandTableColumn(
    #"Merged Queries", 
    "Inserted Addition", 
    {"Animal"}, 
    {"Animal.1"}
  ), 
  #"Removed Columns" = Table.RemoveColumns(
    #"Expanded Inserted Addition", 
    {"Index", "Shift", "Addition"}
  )
in
  #"Removed Columns"
Power Query solution 9 for Swap Alternate Rows, proposed by Deron Huskey: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Data"]}[Content], 
  IndexStartFrom1 = Table.AddIndexColumn(Source, "Index", 1, 1, Int64.Type), 
  AddSortIndex = Table.AddColumn(
    IndexStartFrom1, 
    "SortIndex", 
    each [Index] + Number.Mod([Index], 2)
  ), 
  FinalSort = Table.Sort(
    AddSortIndex, 
    {{"SortIndex", Order.Ascending}, {"Index", Order.Descending}}
  ), 
  FinalResult = Table.SelectColumns(FinalSort, {"Data"})
in
  FinalResult

Solving the challenge of Swap Alternate Rows with Excel

Excel solution 1 for Swap Alternate Rows, proposed by Rick Rothstein: 
=LET(a,A2:A10,f,FILTER(TOCOL(HSTACK(OFFSET(a,1,0),a)),MOD(SEQUENCE(2*COUNTA(a),,0),4)<2),FILTER(f,ISTEXT(f)))
Excel solution 2 for Swap Alternate Rows, proposed by Rick Rothstein: 
=LET(
    a,
    A2:A10,
    s,
    SEQUENCE(
        2*COUNTA(
            a
        ),
        ,
        0
    ),
    TOCOL(
        INDEX(
            a,
            2+2*INT(
                s/2
            )-MOD(
                s,
                2
            )
        ),
        3
    )
)
Excel solution 3 for Swap Alternate Rows, proposed by John V.: 
=INDEX(
    A2:B10,
    {2;1;4;3;6;5;8;7;9}
)

Dynamic option:
✅=LET(
    d,
    A2:A10,
    m,
    MOD(
        ROW(
            d
        ),
        2
    ),
    TOCOL(
        HSTACK(
            FILTER(
                d,
                m
            ),
            FILTER(
                d,
                m=0
            )
        ),
        3
    )
)
Excel solution 4 for Swap Alternate Rows, proposed by محمد حلمي: 
=LET(
    a,
    ROW(
        1:9
    ),
    
    SORTBY(
        A2:A10,
        IF(
            ISODD(
                a
            ),
            EVEN(
                a
            ),
            a-1
        )
    )
)
Excel solution 5 for Swap Alternate Rows, proposed by محمد حلمي: 
=LET(
    
    a,
    ROW(
        1:10
    ),
    
    r,
    IFERROR(
        INDEX(
            A2:A10,
            IF(
                ISODD(
                    a
                ),
                EVEN(
                    a
                ),
                a-1
            )
        ),
        
    ),
    
    FILTER(
        r,
        ISTEXT(
            r
        )
    )
)
Excel solution 6 for Swap Alternate Rows, proposed by Kris Jaganah: 
=LET(a,A2:A10,b,MOD(ROW(a),2),TOCOL(HSTACK(FILTER(a,b=1,0),FILTER(a,b=0,0)),2))
Excel solution 7 for Swap Alternate Rows, proposed by Julian Poeltl: 
=LET(
    D,
    A2:A10,
    R,
    ROWS(
        D
    ),
    RR,
    ROUNDDOWN(
        R/2,
        0
    )*2,
    E,
    CHOOSEROWS(
        D,
        TOCOL(
            HSTACK(
                SEQUENCE(
                    RR/2,
                    ,
                    2,
                    2
                ),
                SEQUENCE(
                    RR/2,
                    ,
                    ,
                    2
                )
            )
        )
    ),
    IF(
        RR<>R,
        VSTACK(
            E,
            TAKE(
                D,
                -1
            )
        ),
        E
    )
)
Excel solution 8 for Swap Alternate Rows, proposed by Aditya Kumar Darak 🇮🇳: 
=LET(
 _n, 2,
 _d, A2:A10,
 _rw, ROWS(_d),
 _seq, SORT(SEQUENCE(_rw / _n + 1, _n), , -1, TRUE),
 _r, TOCOL(INDEX(_d, _seq), 3),
 _r
)

Solution 2:
=LET(
 _n, 2,
 _d, A2:A10,
 _wr, WRAPROWS(_d, _n),
 _cc, CHOOSECOLS(_wr, SEQUENCE(1, _n, _n, -1)),
 _r, TOCOL(_cc, 3),
 _r
)
Excel solution 9 for Swap Alternate Rows, proposed by Timothée BLIOT: 
=LET(Data,
    A2:A10,
    
Sequence,
    MOD(
        SEQUENCE(
            ROWS(
                Data
            )
        ),
        2
    ),
    
RowOne,
    (FILTER(
        Data,
        Sequence
    )),
    
RowTwo,
    (FILTER(
        Data,
        NOT(
            Sequence
        )
    )),
    
TOCOL(
    CHOOSECOLS(
        HSTACK(
            RowOne,
            RowTwo
        ),
        2,
        1
    ),
    3
))
Excel solution 10 for Swap Alternate Rows, proposed by Bhavya Gupta: 
=TOCOL(
    CHOOSECOLS(
        WRAPROWS(
            A2:A10,
            2
        ),
        2,
        1
    ),
    3
)
Excel solution 11 for Swap Alternate Rows, proposed by Charles Roldan: 
=SORTBY(A2:A10,
LAMBDA(x,x+2*MOD(x,2))(
SEQUENCE(ROWS(A2:A10))))
Excel solution 12 for Swap Alternate Rows, proposed by Stefan Olsson: 
=MAKEARRAY(
    
     ROWS(
         A2:A10
     ),
     1,
     
     LAMBDA(
         
          rr,
          cc,
          
          IFS(
              
               ISEVEN(
                   rr
               ),
               INDEX(
                   A2:A10,
                    rr-1
               ),
               
               rr=ROWS(
         A2:A10
     ),
               INDEX(
                   A2:A10,
                   rr
               ),
               
               TRUE,
              INDEX(
                  A2:A10,
                  rr+1
              )
               
          )
          
     )
    
)
Excel solution 13 for Swap Alternate Rows, proposed by Abhishek Kumar Jain: 
=LET(a,A2:A10,b,SEQUENCE(ROWS(a)),c,IF(MOD(b,2)=1,b+1,b-1),d,IF(c>MAX(b),c-1,c),INDEX(a,d))
Excel solution 14 for Swap Alternate Rows, proposed by El Badlis Mohd Marzudin: 
=LET(
    
     _data,
    A2:A10,
    
     _rn,
    MOD(
        SEQUENCE(
            ROWS(
                _data
            )
        )-1,
        2
    ),
    
     _sbs,
    HSTACK(
        FILTER(
            _data,
            _rn=1
        ),
        FILTER(
            _data,
            _rn=0
        )
    ),
    
    
     TOCOL(
         _sbs,
         3
     )
     
)
Excel solution 15 for Swap Alternate Rows, proposed by Amardeep Singh: 
=LET(data,A2:A10,c,COUNTA(data),s,SEQUENCE(c),r,IF(ISODD(s),s+1,s-1),f,IF(r>c,c,r),INDEX(data,f))
Excel solution 16 for Swap Alternate Rows, proposed by Viswanathan M B: 
=Let(Rng, A2:A10,
 Sqn, sequence(rows(rng)),
 ChngSqn, switch(TRUE, Sqn=Rows(Rng), Sqn, 
isodd(Sqn),sqn+1, sqn-1),
 Index(Rng, ChngSqn))
Excel solution 17 for Swap Alternate Rows, proposed by Murat Hasanoglu: 
=IF(ISBLANK(INDEX(A:A,IF(MOD(ROW()-1,2),1,-1)+ROW())),A2,INDEX(A:A,IF(MOD(ROW()-1,2),1,-1)+ROW()))
Excel solution 18 for Swap Alternate Rows, proposed by Dennis Priester: 
=@LET( rowindex,1+ROW()-@ROW([Data] ),
 IF(  ISODD(rowindex),@IFERROR(@INDEX([Data],rowindex+1), INDEX([Data],rowindex) ), INDEX( [Data],rowindex-1) ) )

This thing assumes your A1:B10 is in a table, and it doesnt matter where on the sheet the table is placed.
The @ROW([Data] )
Excel solution 19 for Swap Alternate Rows, proposed by Avneet Singh: 
=Index($A$2:$A$10, if(row(A2)=odd(row(A2)), countA(A$2:A2)-1, countA(A$2:A3)))

Solving the challenge of Swap Alternate Rows with SQL

SQL solution 1 for Swap Alternate Rows, proposed by Zoran Milokanović: 
WITH
DATA_PREPARATION
AS
(
 SELECT
 D.DATA
 ,ROW_NUMBER() OVER () AS ORDINAL_NUMBER
 FROM DATA D
)
SELECT
 F.DATA
,DECODE(F.ODD_FLAG, /*EVEN*/ 0, F.PREVIOUS_DATA, /*ODD*/ NVL(F.SUBSEQUENT_DATA, F.DATA)) AS EXPECTED_ANSWER
FROM
(
 SELECT
 DP.ORDINAL_NUMBER
 , DP.DATA
 ,LAG(DP.DATA) OVER (ORDER BY DP.ORDINAL_NUMBER) AS PREVIOUS_DATA
 ,LEAD(DP.DATA) OVER (ORDER BY DP.ORDINAL_NUMBER) AS SUBSEQUENT_DATA
 ,MOD(DP.ORDINAL_NUMBER, 2) AS ODD_FLAG
 FROM DATA_PREPARATION DP
) F
ORDER BY
 F.ORDINAL_NUMBER
;

Leave a Reply