2d grid into single column (Excel 97 -SR2)

Topic

New Reply

What I would like to do is to be able to turn a 2 dimensional grid layout on one worksheet into a single column of data on another.

I have attached a copy of what I would like to see, but I want to have a reliable means of converting the layout regardless of the dimensions of the original source table.

I know someone out there will have a solution. – if its trivially easy then I apologize in advance

Reply | Quote

Replies

Try this:
=INDEX(DATALIST,MOD(ROW()-1,COLUMNS(DATALIST))+1,INT((ROW()-1)/COLUMNS(DATALIST))+1)

Where DATALIST is a named range for your dataset. NOTE: if you don’t start in row1 you will have to adjust the formula!

Steve

Reply | Quote

That’s great – but I found I had to reverse the row and column references like this
=INDEX(DATALIST,INT((ROW()-1)/COLUMNS(DATALIST))+1,MOD(ROW()-1,COLUMNS(DATALIST))+1)

Do you agree?

Many thanks for pointing me in the right direction – I’ve not come across INDEX before.

Reply | Quote

Your example sheet goes down col A: A1, A2, A3, A4 then does col B, B1, B2, B3, B4 then C, then COl D. that is what my formula does.

Your formula goes ACROSS the columns:
Row 1: A1, B1, C1, D1, then
Row 2: A2, B2, C2, D2, then
Etc
If that is what you want then your formula is correct.

But if you want the display to be like your example worksheet then I stand by my post.

Steve
Here is the “source data”

	A	B	C	D
1	a100	b100	c100	d100
2	a200	b200	c200	d200
3	a300	b300	c300	d300
4	a400	b400	c400	d400

Col A shows your “single Column”
Col B is : My proposal: =INDEX(DataList,MOD(ROW()-1,COLUMNS(DataList))+1,INT((ROW()-1)/COLUMNS(DataList))+1)
Col C is : Your proposal: =INDEX(DataList,INT((ROW()-1)/COLUMNS(DataList))+1,MOD(ROW()-1,COLUMNS(DataList))+1)

Use whichever result you want.

	A	B	C
1	a100	a100	a100
2	a200	a200	b100
3	a300	a300	c100
4	a400	a400	d100
5	b100	b100	a200
6	b200	b200	b200
7	b300	b300	c200
8	b400	b400	d200
9	c100	c100	a300
10	c200	c200	b300
11	c300	c300	c300
12	c400	c400	d300
13	d100	d100	a400
14	d200	d200	b400
15	d300	d300	c400
16	d400	d400	d400

Reply | Quote

To be honest I hadn’t checked whether it was doing row/column or column/row, that closely. Thanks for pointing it out.
My bigger problem with the original formula was that when I used a 7×4 grid I was getting
a100
a200
a300
a400
#REF!
#REF!
#REF!
b100
b200
b300
b400
#REF!
#REF!
#REF!
c100
c200

etc.

Reply | Quote

Sorry, I got my rows and columns mixed up (I knew I should hve worked with a range that was NOT symmetrical!)

Change the columns() to rows():

=INDEX(datalist,MOD(ROW()-1,ROWS(datalist))+1,INT((ROW()-1)/ROWS(datalist))+1)

Steve

Reply | Quote

Many thanks
I’m using this a lot already.

Reply | Quote