http://chandoo.org/forums/topic/96-cell-grid-transformation-with-label Ask Excel and Charting Questions, Muse about Visualizations, Learn and Share - Discussion Forums - Chandoo.org en-US Wed, 22 May 2013 00:49:05 +0000 http://bbpress.org/?v=1.0.1 q http://chandoo.org/forums/search.php http://chandoo.org/forums/topic/96-cell-grid-transformation-with-label#post-24767 Wed, 02 May 2012 21:19:00 +0000 SirJB7 24767@http://chandoo.org/forums/ <p>Hi, Austinma!<br /> Go back, work, then come back if any comment. Good luck.<br /> Regards! </p> http://chandoo.org/forums/topic/96-cell-grid-transformation-with-label#post-24765 Wed, 02 May 2012 21:13:43 +0000 Austinma 24765@http://chandoo.org/forums/ <p>Hi SirJB7,</p> <p>Thanks - it was a bit touch and go for a while - but we came good in the end.</p> <p>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!!!</p> <p>Cheers</p> <p>Austinma </p> http://chandoo.org/forums/topic/96-cell-grid-transformation-with-label#post-24759 Wed, 02 May 2012 20:08:24 +0000 SirJB7 24759@http://chandoo.org/forums/ <p>Hi, Austinma!<br /> It seems that I missed the last part of the movie, but... glad you solved it.<br /> Just advise if need further modifications.<br /> Regards! </p> http://chandoo.org/forums/topic/96-cell-grid-transformation-with-label#post-24532 Mon, 30 Apr 2012 23:11:47 +0000 Montrey 24532@http://chandoo.org/forums/ <p>Austinma,</p> <p>You can also use Ctrl + F3 to get to the name manager. I use it all the time. </p> http://chandoo.org/forums/topic/96-cell-grid-transformation-with-label#post-24529 Mon, 30 Apr 2012 21:36:44 +0000 oldchippy 24529@http://chandoo.org/forums/ <p>Well that's nice to know the grey matter is still working!</p> <p>Glad you were able to get it to work in the end.</p> <p>I can go to bed now......goodnight </p> http://chandoo.org/forums/topic/96-cell-grid-transformation-with-label#post-24528 Mon, 30 Apr 2012 21:31:21 +0000 Austinma 24528@http://chandoo.org/forums/ <p>Bingo!!!!!!</p> <p>Clearly nothing wrong with your memory!!</p> <p>Thanks for persevering.</p> <p>Best wishes, </p> <p>Austinma (Mark) </p> http://chandoo.org/forums/topic/96-cell-grid-transformation-with-label#post-24527 Mon, 30 Apr 2012 21:21:04 +0000 oldchippy 24527@http://chandoo.org/forums/ <p>Hi,</p> <p>From memory I think in 2003 you will find the Name Manager under Insert &gt; Name &gt; Define</p> <p>EDIT: These formulas can all work in 2003 </p> http://chandoo.org/forums/topic/96-cell-grid-transformation-with-label#post-24526 Mon, 30 Apr 2012 21:18:50 +0000 Austinma 24526@http://chandoo.org/forums/ <p>Hi, </p> <p>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.</p> <p>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?!!</p> <p>cheers, </p> <p>Austinma </p> http://chandoo.org/forums/topic/96-cell-grid-transformation-with-label#post-24524 Mon, 30 Apr 2012 21:04:42 +0000 oldchippy 24524@http://chandoo.org/forums/ <p>Hi.</p> <p>I'm using xl2007, its the fourth tab at the top of the screen?? </p> http://chandoo.org/forums/topic/96-cell-grid-transformation-with-label#post-24521 Mon, 30 Apr 2012 20:49:18 +0000 Austinma 24521@http://chandoo.org/forums/ <p>Hi oldchippy, </p> <p>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. </p> <p>Do you know how to proceed with this in Excel 2007.</p> <p>Thanks, </p> <p>Austinma. </p> http://chandoo.org/forums/topic/96-cell-grid-transformation-with-label#post-24520 Mon, 30 Apr 2012 20:18:10 +0000 oldchippy 24520@http://chandoo.org/forums/ <p>Hi Austinma,</p> <p>Sorry to confuse you.</p> <p>Let me explain a little more</p> <p>If you insert 4 additional columns at the end of the table to “L”.</p> <p>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.</p> <p>See Auto-fill here (Mouse Trick 5)</p> <p><a href="http://chandoo.org/wp/2009/06/12/excel-mouse-tricks/" rel="nofollow">http://chandoo.org/wp/2009/06/12/excel-mouse-tricks/</a></p> <p>Select the range A9:H12 and press the delete key to empty those cells.</p> <p>Then if you go to the Formulas tab and select Name Manager, edit the MatrixData to</p> <p>=Hoja1!$A$1:$L$8</p> <p>Now edit the formula in cell N1 to</p> <p>=INDEX(MatrixData,(ROW()-1)/12+1,MOD(ROW(),12)+IF(MOD(ROW(),12)=0,12,0)) and autofill down</p> <p>Hope this helps? </p> http://chandoo.org/forums/topic/96-cell-grid-transformation-with-label#post-24513 Mon, 30 Apr 2012 17:29:12 +0000 Austinma 24513@http://chandoo.org/forums/ <p>@oldchippy</p> <p>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.</p> <p>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. </p> <p>I fear I'm doing something daft......</p> <p>Thanks in advance, </p> <p>Austinma</p> <p>PS I'm using Excel 2007 - is that why I cannot see a 'Formulas' pull down menu? </p> http://chandoo.org/forums/topic/96-cell-grid-transformation-with-label#post-24444 Sun, 29 Apr 2012 21:31:23 +0000 Austinma 24444@http://chandoo.org/forums/ <p>@oldchippy</p> <p>Thanks for the post - I'll give it a go and report back.</p> <p>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.</p> <p>Thanks again, </p> <p>Regards, </p> <p>Mark </p> http://chandoo.org/forums/topic/96-cell-grid-transformation-with-label#post-24442 Sun, 29 Apr 2012 21:22:10 +0000 oldchippy 24442@http://chandoo.org/forums/ <p>Hi Austinma,<br /> 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 &gt; Name manager, edit the MatrixData to</p> <p>=Hoja1!$A$1:$L$8</p> <p>Now edit the formula in the column to</p> <p>=INDEX(MatrixData,(ROW()-1)/12+1,MOD(ROW(),12)+IF(MOD(ROW(),12)=0,12,0)) and autofill down </p> http://chandoo.org/forums/topic/96-cell-grid-transformation-with-label#post-24441 Sun, 29 Apr 2012 21:21:25 +0000 Austinma 24441@http://chandoo.org/forums/ <p>Hi SirJB7, </p> <p>Perfect - off to work tomorrow to spread the word!</p> <p>Cheers</p> <p>Austinma </p> http://chandoo.org/forums/topic/96-cell-grid-transformation-with-label#post-24440 Sun, 29 Apr 2012 21:16:31 +0000 SirJB7 24440@http://chandoo.org/forums/ <p>Hi, Austinma!</p> <p>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.</p> <p>Glad you solved it. Thanks for your feedback and for your kind words too. Welcome back whenever needed or wanted.</p> <p>Regards! </p> http://chandoo.org/forums/topic/96-cell-grid-transformation-with-label#post-24438 Sun, 29 Apr 2012 20:40:32 +0000 Austinma 24438@http://chandoo.org/forums/ <p>Hi SirJB7, </p> <p>Once again, thanks for your super quick response!</p> <p>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??</p> <p>Sorry if is this classed as pushing my luck too much ;0)</p> <p>Seriously though - thanks for all your help, </p> <p>Regards, </p> <p>Mark </p> http://chandoo.org/forums/topic/96-cell-grid-transformation-with-label#post-24432 Sun, 29 Apr 2012 20:10:29 +0000 SirJB7 24432@http://chandoo.org/forums/ <p>Hi, Austinma!</p> <p>Give a look at this file:<br /> <a href="http://dl.dropbox.com/u/60558749/96%20Cell%20Grid%20Transformation%20with%20label%20%28for%20Austinma%20at%20chandoo.org%29.xlsx" rel="nofollow">http://dl.dropbox.com/u/60558749/96%20Cell%20Grid%20Transformation%20with%20label%20%28for%20Austinma%20at%20chandoo.org%29.xlsx</a></p> <p>But don't push your luck too much next time :)</p> <p>Regards! </p> http://chandoo.org/forums/topic/96-cell-grid-transformation-with-label#post-24424 Sun, 29 Apr 2012 19:31:36 +0000 Austinma 24424@http://chandoo.org/forums/ <p>Hi, </p> <p>My previous two posts were answered successfully within minutes :o) so I thought I'd push my luck and throw another question out there.</p> <p>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. </p> <p>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.</p> <p>I hope that this is clear - please ask if further information is required.</p> <p>Best of luck - Go Ninjas!</p> <p>cheers</p> <p>Mark </p>