Break long column into a specified number of rows

**rcarter86** · 06-03-2015, 05:48 PM

I have 1 column with about 31,000 rows. I need to break this up into rows that contain exactly 96 columns.

On a smaller scale it would look like this:

1 _ 1 2 3
2 _ 4 5 6
3 _ 7 8 9
4 _
5 _
6 _
7 _
8 _
9 _

The closest I've come is by using an offset formula: =OFFSET($A$1,(ROW()-2)*96+INT((COLUMN()-3)),MOD(COLUMN()-3,1))

but after 10 rows it starts to revert back to the first value.

I'm not sure the best way to do this.

**FDibbins** · 06-03-2015, 06:00 PM

Hi, welcome to the forum

Try this...

	A	B	C	D	E
1		3
2	1		1	2	3
3	2		4	5	6
4	3		7	8	9
5	4		0	0	0
6	5		0	0	0
7	6		0	0	0
8	7		0	0	0
9	8
10	9

B1=the number of columns you want
C2=IF(COLUMN(A1)>$B$1,"",OFFSET($A$1,(ROW($A1)-1)*$B$1+COLUMN(A1),0))
copied down and across as needed

**rcarter86** · 06-03-2015, 06:23 PM

Thank you very much. I don't quite understand how the offset formula works but this seems to have given me the data the way i need it formatted. Thank you for your quick response!

**FDibbins** · 06-03-2015, 06:26 PM

You are welcome.

If this answered your question, please take a moment to mark the thread as "solved" - it helps keep things neat and tidy lol, and consider adding the reputation to those that helped (see points 2 & 3 below my 1st post to you)