• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

96 Cell Grid Transformation with label

A

Austinma

Member
Hi,


My previous two posts were answered successfully within minutes :eek:) so I thought I'd push my luck and throw another question out there.


Being a scientist a lot of the data that I have to analyse comes back in the form of multiple Excel 8 x 12 cell grids. There are 8 rows (A - H) and 12 columns (1 -12). Essentially this means that I have 96 data points each with a unique grid reference. For example A01, B01, D01 .........F12, G12 and H12.


Often I have to transform this grid data into a linear column to allow analysis in data analysis packages such as Spotfire. That is a single column of 96 values reading each column in turn . I currently have a vba macro installed that does this for me - but was wondering if there is perhaps a more elegant (less clunky) way of doing this transformation. For an added layer of complexity I would like to run a column alongside the 96 data points that includes the data reference point ie A01, B01, C01 etc - This might be easy enough if the 96 data points were actually in cells A01, etc of the Excel worksheet, but invariably the grid may be located anywhere within the worksheet. It would be nice to have a formula that means I can do both the transformation and the 'label' action in one command.


I hope that this is clear - please ask if further information is required.


Best of luck - Go Ninjas!


cheers


Mark
 
Hi, Austinma!


Give a look at this file:

http://dl.dropbox.com/u/60558749/96%20Cell%20Grid%20Transformation%20with%20label%20%28for%20Austinma%20at%20chandoo.org%29.xlsx


But don't push your luck too much next time :)


Regards!
 
Hi SirJB7,


Once again, thanks for your super quick response!


The file works a treat - would it be possible to have the grid in a slightly different orientation: 12 cells across and 8 rows down??


Sorry if is this classed as pushing my luck too much ;0)


Seriously though - thanks for all your help,


Regards,


Mark
 
Hi, Austinma!


I read upwards A1:H12, so I made the grid of 12 rows by 8 columns. If you now go on pushing your luck so that much and transpose your specs, just replace the each of four 8s in formulas of column J for 12s and that's all. Just advise if any issue with that.


Glad you solved it. Thanks for your feedback and for your kind words too. Welcome back whenever needed or wanted.


Regards!
 
Hi Austinma,

If you insert some additional columns between the table and the column then you can auto fill the table over to column L. Then if you go into Formulas > Name manager, edit the MatrixData to


=Hoja1!$A$1:$L$8


Now edit the formula in the column to


=INDEX(MatrixData,(ROW()-1)/12+1,MOD(ROW(),12)+IF(MOD(ROW(),12)=0,12,0)) and autofill down
 
@oldchippy


Thanks for the post - I'll give it a go and report back.


I've a couple of additional 'nice to haves' with this type of data transformations, but will save those for when I've worked through and familiarised myself with your and SirJB7's solutions.


Thanks again,


Regards,


Mark
 
@oldchippy


Hi - I appear to have fallen at the first hurdle: I've attempted edit the matrix data but cannot see a formulas pull-down menu.


What I did do that got me halfway there was to click on the little black triangle just in the top right hand corner of the toolbars (next to the fx symbol). Doing this allowed me to see and then click on the 'matrixdata' label. If I then paste in the =Hoja1!$A$1:$L$8 formula I see that the area defined is what I want (by the blue lined grid). BUT at this point I am unable to accept this new grid.


I fear I'm doing something daft......


Thanks in advance,


Austinma


PS I'm using Excel 2007 - is that why I cannot see a 'Formulas' pull down menu?
 
Hi Austinma,


Sorry to confuse you.


Let me explain a little more


If you insert 4 additional columns at the end of the table to “L”.


The vertical data should now be in column “N”. Select cell H1 and auto fill the formula across to column “L” and down to row 8.


See Auto-fill here (Mouse Trick 5)


http://chandoo.org/wp/2009/06/12/excel-mouse-tricks/


Select the range A9:H12 and press the delete key to empty those cells.


Then if you go to the Formulas tab and select Name Manager, edit the MatrixData to


=Hoja1!$A$1:$L$8


Now edit the formula in cell N1 to


=INDEX(MatrixData,(ROW()-1)/12+1,MOD(ROW(),12)+IF(MOD(ROW(),12)=0,12,0)) and autofill down


Hope this helps?
 
Hi oldchippy,


Thanks for your post - everything makes perfect sense and works well right up until I have to find the 'Formulas' tab'. I don't have this tab in my old version of Excel (2007). I quick check on the ol' interweb shows me that the tab is there in newer versions.


Do you know how to proceed with this in Excel 2007.


Thanks,


Austinma.
 
Hi,


Oooops - sorry my mistake. My version is actually 2003 - I was confused as the original file posted by SirBJ was saved as a xl2007 file.


Is it still possible to complete this command in 2003 or am I going to have to try moving into the 21st century and up grade my Excel?!!


cheers,


Austinma
 
Hi,


From memory I think in 2003 you will find the Name Manager under Insert > Name > Define


EDIT: These formulas can all work in 2003
 
Well that's nice to know the grey matter is still working!


Glad you were able to get it to work in the end.


I can go to bed now......goodnight
 
Hi, Austinma!

It seems that I missed the last part of the movie, but... glad you solved it.

Just advise if need further modifications.

Regards!
 
Hi SirJB7,


Thanks - it was a bit touch and go for a while - but we came good in the end.


I'm going to take what I've learnt and apply it to some of my data. There's a few things I'm thinking of adding to the pot....but I'm going to give it a go myself - so expect more questions in a couple of days time!!!


Cheers


Austinma
 
You must log in or register to reply here.
Back
Top