Forum Discussion

Copper Contributor

Aug 11, 2020

Difficult scenerio, move columns to a new line

I have to move items that are in a line, across columns, to be moved to their own line. I have 3000 records to do this with. Basically lets say you have a basket (column A1) in that basket is ch...

excel

PReagan

Bronze Contributor

Aug 11, 2020

Hello katphelan,

It sounds like you are looking to transpose your array. Here is how to transpose data:

Highlight A1:E1
Copy (Ctrl + C)
Select an empty cell that has four additional empty cells below it
Paste (Ctrl + P) then press Ctrl then press T

katphelan
Copper Contributor
Aug 11, 2020
PReagan but how do i do this for 3000 lines of data....do i have to put in empty lines first? Its easy for one line with 4 columns but when i have 3000 lines of 4 columns...
- SergeiBaklan
  Diamond Contributor
  Aug 11, 2020
  katphelan
  
  Like this
  
  with
  
  =INDEX(A:D,INT((ROW()-1)/4)+1,MOD(ROW()-1,4)+1)
  4 columns to 1.xlsx18 KB
- SergeiBaklan
  Diamond Contributor
  Aug 11, 2020
  katphelan
  
  The easiest way to combine 4 columns in one is Power Query if you consider such option. Otherwise with INDEX.
- PReagan
  Bronze Contributor
  Aug 11, 2020
  katphelan,
  
  I'm sorry, I must be misunderstanding your question. Does transposing your entire array not yield the result you expect?