How do I format data from a grid into a printable list in Excel?

I have an 8x16 grid with cells that contain data at random, reading left to right. I'd like to take that data and and have Excel automatically create a vertical list on-the-fly which can be printed, and omit blank cells. Aside from VB, is there a way to do it with a formula?How do I format data from a grid into a printable list in Excel?
In three steps:





1) To convert the grid to a column:





It doesn't matter where your grid is, but let's say it starts at F2. Put this column in cell A4 and copy it down to A131:


=OFFSET($F$2, TRUNC((ROW() - ROW($A$4))/8), MOD(ROW()-ROW($A$4),8))





2) To identify the cells that have non-zero values:





Put this formula in B4, and input it with Ctrl-Shift-Enter (not just Enter), because it's an array formula:


=MIN(IF($A$4:$A$131%26lt;%26gt;0, ROW($A$4:$A$131),';';))





Put this in B5, and copy it down to B131 (also input this with Ctrl-Shift-Enter):


=IF(OR(B4=148,B4=0),0, MIN(IF(INDIRECT(';$A$'; %26amp; B4+1 %26amp; ';:$A$148';)%26lt;%26gt;0, ROW(INDIRECT(';$A$'; %26amp; B4+1 %26amp; ';:$A$148';)),';';)))





3) To collapse the column to eliminate blanks or 0s:





Put this in C4 and copy down to C131:


=IF(B4=0,';';,OFFSET($A$4, B4-ROW($A$4),0))





e-mail if you have any questions





.How do I format data from a grid into a printable list in Excel?
that was pretty cool. i can use #3 for other stuff too!

Report Abuse

aside from VB? hmmm


will any of your random Data be 0?





so if your grid starts in A1 and ends H16 (thats the 8 columns and 16 rows, i just started at the top)...then you can use this formula to transpose the grid into 1 column.


copy%26amp;paste this into A20 (or anywhere on the sheet)





=INDIRECT(ADDRESS( IF(MOD(ROW(A1),128)/8=0, 16,CEILING(MOD(ROW(A1), 128)/8,1)),IF(MOD(ROW(A1), 8)=0,8,MOD(ROW(A1),8))))





then copy%26amp;paste down to A148





the problem with this is, it will replace an empty cell with a zero. i was thinking you could just autofilter out the 0's....thats why i started the formula underneath the 8x16 table in A20. if you put the formula in J1, then you might hide part of the table. it doesnt matter where you start the formula. just setup your page breaks properly.





if your random data does include significant zeroes, then it would make the formula twice as long, but you could add an if() statement that looks for ';'; or isblank() in the matrix, and return a ';';





someone should come by with a better answer, becuase all i did was a basic matrix to column formula.