Thread: Arrange cells dynamically

Hi ,

I have a query output stored in a range.
which has years in the first column
i have to arrange the data based on the year , leaving empty cells if a particular year is not present.

SOURCE DATA( Dynamic , recieved from a QUERY)

2001 10 30 50 20 11 34
2006 11 10 53 10 23 45
2009 12 20 67 90 0 3
2010 13 40 11 4 10 11

This needs to be arranged like this

2001 10 30 50 20 11 34
2002
2003
2004
2005
2006 11 10 53 10 23 45
2007
2008
2009 12 20 67 90 0 3
2010 13 40 11 4 10 11

Kindly Look into the workbook attached.
Awaiting Help....



Thanks In Advance,
Shruthi

Try this

With years in A16:A25

in C16

=IF(ISERROR(MATCH($A16,$A$2:$A$11,0)),"",INDEX($A$2:$H$11,MATCH($A16,$A$2:$A$11,0),COLUMN()))

Drag Across to H16 then down to row 25

Hope this helps

If your headers are going to be Var1, Var2.... then

=IF(ISERROR(MATCH($A16,$A$2:$A$11,0)),"",INDEX($A$2:$H$11,MATCH($A16,$A$2:$A$11,0),RIGHT(C$15,1)+2))

or you could use a row with 3, 4, 5, 6, 7, 8 then refer to these cells, say C14:H14

=IF(ISERROR(MATCH($A16,$A$2:$A$11,0)),"",INDEX($A$2:$H$11,MATCH($A16,$A$2:$A$11,0),C$14))

This last formula works in Excel if C14:H14 is blank, I don't know if it will in Xcelsius

Hope this helps

Could you please mark this post [SOLVED]

You started another thread here

http://www.excelforum.com/excel-new-...ow-column.html

Judging by your reply there this thread solved both problems.