Need to split entries from one long column into 5 columns

**KimC** · 01-31-2015, 09:33 PM

I've been working on something for hours and I can't figure out how to do it. I have one long column (nearly 500 entries). What I want to do, if possible, is to change the data presentation to 5 columns, with every fifth entry starting a new row. What is now column A1-A500 would move the first five entries of that row (original entries A1,A2,A3,A4,A5) to be in cols A1,B1,C1,D1,E1, then take the original A6 and start the next row where there would be original entries, A6,A7,A8,A9,A10 placed into cells A2,B2,C2,D2,E2, and so on. My first though was to "flag" all of col A in Col B with a series of 1,2,3,4,5,1,2,3,4,5, etc. and then use a series of "if" statements. Then all the original col A entries flagged with a "1" in col B would be the first entry in a series of rows starting somewhere to the right. All the flagged "2"s would be the second column of the new table, etc. But I can't make that work like it want it to.

I have set up and attached a shortened dummy table showing what I have, with the flags. My data is in col A and the flags in col B. What I want to have happen begins in col D, just for display purposes (I don't yet have the correct formulas putting the data there). Any ideas? Is there a shorter, simpler way to get this done? Were the flags a wrong approach? Thanks everyone!

**Tony Valko** · 01-31-2015, 09:43 PM

I didn't download your file.

Is this what you want to do:

Data Range

	A	B	C	D	E	F	G
1	1		1	2	3	4	5
2	2		6	7	8	9	10
3	3
4	4
5	5
6	6
7	7
8	8
9	9
10	10
11	------	------	------	------	------	------	------

This formula entered in C1:

=INDEX($A$1:$A$10,(ROWS(A$1:A1)-1)*5+COLUMNS($A1:B1)-1)

Copy across to G1 then down as needed.

**MarvinP** · 01-31-2015, 09:53 PM

Hi Kim,

See if the attached works for you.

In E5 put this formula, then pull it across and down.

=INDIRECT("B"&4 + 5*(ROWS(E$5:E5)-1) + COLUMNS($E1:E1))