Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations MintJulep on being selected by the Eng-Tips community for having the most helpful posts in the forums last week. Way to Go!
Transposing data from columns 1
- Thread starter DDP
- Start date
Not as easy as I thought it would be.
I wrote a UDF to do it:
Function Tabulate(TabA As Variant) As Variant
Dim Numrows1 As Long, NumRows2 As Long, NumCols As Long, Tab2A() As Variant
Dim i As Long, j As Long
TabA = TabA.Value2
Numrows1 = UBound(TabA)
NumRows2 = TabA(Numrows1, 1)
NumCols = TabA(Numrows1, 2)
ReDim Tab2A(1 To Numrows1, 1 To NumCols)
For i = 1 To Numrows1
Tab2A(TabA(i, 1), TabA(i, 2)) = TabA(i, 3)
Next i
Tabulate = Tab2A
End Function
See file below for example:
Doug Jenkins
Interactive Design Services
I wrote a UDF to do it:
Function Tabulate(TabA As Variant) As Variant
Dim Numrows1 As Long, NumRows2 As Long, NumCols As Long, Tab2A() As Variant
Dim i As Long, j As Long
TabA = TabA.Value2
Numrows1 = UBound(TabA)
NumRows2 = TabA(Numrows1, 1)
NumCols = TabA(Numrows1, 2)
ReDim Tab2A(1 To Numrows1, 1 To NumCols)
For i = 1 To Numrows1
Tab2A(TabA(i, 1), TabA(i, 2)) = TabA(i, 3)
Next i
Tabulate = Tab2A
End Function
See file below for example:
Doug Jenkins
Interactive Design Services
- Thread starter
- #6
-
1
- #8
electricpete
Electrical
lol. There are many ways to skin a cat.
=====================================
Eng-tips forums: The best place on the web for engineering discussions.
=====================================
Eng-tips forums: The best place on the web for engineering discussions.
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:
SUMIF(Value Range, Row Range, Row, Column Range, Column)
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.
SUMIF(Value Range, Row Range, Row, Column Range, Column)
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.
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.
At least I never bought into Vista.
David
At least I never bought into Vista.
David
- Thread starter
- #17
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 Jenkins
Interactive Design Services
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 Jenkins
Interactive Design Services
- Thread starter
- #19
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).
David
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).
David
- Status
Similar threads
- Locked
- Question
- Locked
- Question
- Locked
- Question