0

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

5
  • ...every 6 cells not 7 sorry
    – Kristina
    Oct 31, 2013 at 20:54
  • What's your question? What have you tried already? Where are you getting stuck? Oct 31, 2013 at 20:58
  • 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
    Oct 31, 2013 at 21:18
  • Only way I know is to split the screen 4 ways and copy paste blocks
    – Kristina
    Oct 31, 2013 at 21:18
  • Why don't you skip putting a newline in your "program" instead? Oct 31, 2013 at 23:22

2 Answers 2

2

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

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

    enter image description here
    click to enlarge

0

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))

Not the answer you're looking for? Browse other questions tagged .