Pasting from several rows into a single row every 6th cell

I need to copy every 6 cells down horizontal to a new column

So that D3/E3/F3/G3/H3/I3 goes to J2/K2/L2/M2/N2/O2
And that D4/E4/F4/G4/H4/I4 goes to P2/Q2/R2/S2/T2/U2

And so on for sets of 20 rows

I swear I did it before, do not know VBA but am stumped other than manual paste

Kristina

Posted 2013-10-31T20:47:22.333

Reputation: 1

...every 6 cells not 7 sorry – Kristina – 2013-10-31T20:54:33.177

What's your question? What have you tried already? Where are you getting stuck? – Ƭᴇcʜιᴇ007 – 2013-10-31T20:58:36.320

I have output from one program that has my data in sets of 6 columns and 20 rows per datapoint (person) I need the 20 rows (in 6 cell sets) to fit into one long row. – Kristina – 2013-10-31T21:18:16.050

Only way I know is to split the screen 4 ways and copy paste blocks – Kristina – 2013-10-31T21:18:48.567

Why don't you skip putting a newline in your "program" instead? – tumchaaditya – 2013-10-31T23:22:31.133

Answers

I have output from one program that has my data in sets of 6 columns and 20 rows per datapoint (person) I need the 20 rows (in 6 cell sets) to fit into one long row.

Use Notepad++ and its RegEx feature to remove line breaks to get a single date row.

enter image description here

Save the Excel file as .CSV and open it with Notepad++
Replace the newline character \r\n with your corresponding delimiter ; or ,

^{click to enlarge}

nixda

Posted 2013-10-31T20:47:22.333

Reputation: 23 233

You could put the following formula into J2 and copy across to the right:

=OFFSET(J2,ROUNDDOWN((COLUMN()-4)/6,0),-6*ROUNDDOWN((COLUMN()-4)/6,0))

Levi

Posted 2013-10-31T20:47:22.333

Reputation: 516