3

I recently encountered a problem while establishing data structure in Excel.

The format that I need to handle is having the forms below.

1   3   5   7   9    
2   4   6   8   10

The data is taking up two rows moving up and down.

I would like to rearrange those data in a one row as below.

1   2   3   4   5   6   7   8   9   10

What function do I need to use in Excel?

I tried to just copy and paste or '=' command only. However the amount of data that i need to handle is too big to do it manually. Please help..

Thanks in advance.

JNevill
  • 46,980
  • 4
  • 38
  • 63
Soonk
  • 332
  • 2
  • 14

2 Answers2

2

Put this in a cell and drag it right.

=INDEX($A$1:$E$2, MOD(COLUMN(A:A)-1, 2)+1, (COLUMN(A:A)-1)/2+1)

enter image description here

  • 1
    It looks like this works on the fact that the column number in an `INDEX()` function rounds down to the nearest whole number. So every second iteration is a half, eg. the formula in `B5` will return `1.5` for the column number, which will return data from `Column A` (AKA Column 1). I didn't know this was a feature of `INDEX()`Thanks for teaching me something new! – girlvsdata Oct 22 '18 at 21:48
  • 1
    Some would just automatically wrap the column calc in INT(...) and that may actually improve readability but it isn't strictly necessary. –  Oct 22 '18 at 21:52
1

I would use vba, as this would require an iteration. This works only if both the two "zig-zag" rows are in the first and second rows of the sheet row (A) and row (B) respectively.

Sub combine()
Dim wb As Workbook
Dim ws As Worksheet
Dim i As Long
Dim j As Long
Dim k As Long
Dim LastCol As Long

Set wb = ActiveWorkbook
Set ws = wb.ActiveSheet
j = 4
k = 1
 LastCol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column
 With ws


  For i = 1 To LastCol
     .Cells(j, k).Value = .Cells(1, i).Value
     k = k + 1
     .Cells(j, k).Value = .Cells(2, i).Value
     k = k + 1
  Next i

 End With
 End Sub
yossup
  • 118
  • 1
  • 9