*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.
Partner With Us!
"Best Of Breed" Forums Add Stickiness To Your Site
(Download This Button Today!)
Member Feedback
"...I think the site is just incredible....I am learning more here than anywhere else before and am looking forward to being able to help someone .... this is my idea of what the Internet is supposed to do..."
See attached Excel file containing VBA Macro... 1) Paste your Data onto Sheet1 2) Run Macro 'TransformRowColumnData' 3) Transformed Data written on New Sheet
It seems you have it licked with a VBA functions, but here's another cat to skin. If your someone like me that doesn't have the time or knowledge to mess around to much with VBA and like to look for the simple solution first, try this:
Assuming you have only one value for every Row and Column designation, this will work. The trick would be placing the $ in all the right places so you can drag the equation both vertically and horizontally and change the designated Row and Column in only the appropriate direction.
Sorry, for those of you stuck in the dark ages of 2003, try this:
Create a reference column between the Column and Value in the reference table. Insert the formula =Row*100+Column. That creates a unique number for each row and column combination, were the row is contained in the 100's place and the column is contained in the 1's place. Then, it's a simple vlookup function in the new table: =VLOOKUP(row*100+column,range of new column,2,FALSE)
If you need more than 99 columns, then put the row in the 1000's place.
"Dark Ages" my butt. One of the biggest regrets of my life was "upgrading" from Office 2003 to 2007. I have not found a single feature that is better in 2007, and hundreds of features that are worse. For a while I thought that the problems were because I was working in "compatibility mode" and decided to go whole hog and save everything in 2007 versions. When I finally decided that I had made a major mistake I had too many files in the new format to go back so I'm limping along with this crap.
DDP - can you give us a bit more detail about that formula?
I presume your data is in ranges L18:L104, M18:M104, and N18:N104, but what is in cells W7 and Z3?
General comment - I'm all in favour of keeping things simple, but writing a UDF really isn't that hard, and when you have done it the logic is much more obvious than it is for the clever but mysterious worksheet formulas that people come up with.
On XL 2007 - just for a bit of balance, some things that are better in 2007:
Tool tips and help when entering functions are much better. Saves large files much quicker, and to much smaller size. More stable with very big files. It has more rows and columns.
Whether those features make it worth the hassle of the new interface is a matter of taste, but it simply isn't true that nothing is better.
Doug, I didn't say that "nothing is better", I said that I couldn't find anything that is better. I don't save particularly large files (more than a few thousand rows goes straight to Access), so none of the things you mentioned are things that I would have noticed. Excel is a pretty good use of data. My source of data is a database (usually Access these days, SQL/DS and IMS in the past). 2003 moved data back and forth with less effort (e.g., comma formatted data in Excel 2007 shows up in Access 2007 as text, what a pain to have to change any number back to "general" prior to importing).
>> I didn't say that "nothing is better", I said that I couldn't find anything that is better. <<
OK, if you use Excel mainly to access data from a database I can see the "big file" features would be unimportant, and if the process has become more cumbersome I can understand your frustration.
Transposing data from columns