Transposing data from columns
Transposing data from columns
(OP)
I want to change data from this
Row Column Value
1 1 5
1 2 6
2 1 7
2 2 8
to this:
Row Column
1 2
--- ------
1 5 6
2 7 8
I cant seem to figure it out using VLOOKUP, any suggestions?
Row Column Value
1 1 5
1 2 6
2 1 7
2 2 8
to this:
Row Column
1 2
--- ------
1 5 6
2 7 8
I cant seem to figure it out using VLOOKUP, any suggestions?
RE: Transposing data from columns
TTFN
FAQ731-376: Eng-Tips.com Forum Policies
RE: Transposing data from columns
I think it could be done with a pivot table too.
RE: Transposing data from columns
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
http://newtonexcelbach.wordpress.com/
RE: Transposing data from columns
1) Paste your Data onto Sheet1
2) Run Macro 'TransformRowColumnData'
3) Transformed Data written on New Sheet
Enjoy!
RE: Transposing data from columns
RE: Transposing data from columns
Cheers,
Joerd
Please see FAQ731-376: Eng-Tips.com Forum Policies for tips on how to make the best use of Eng-Tips.
RE: Transposing data from columns
RE: Transposing data from columns
=====================================
Eng-tips forums: The best place on the web for engineering discussions.
RE: Transposing data from columns
That was my initial reaction, but if you look carefully at the opening post, you will see that it is not a straightforward transpose.
RE: Transposing data from columns
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.
RE: Transposing data from columns
RE: Transposing data from columns
Cheers,
Joerd
Please see FAQ731-376: Eng-Tips.com Forum Policies for tips on how to make the best use of Eng-Tips.
RE: Transposing data from columns
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.
RE: Transposing data from columns
At least I never bought into Vista.
David
RE: Transposing data from columns
Cheers,
Joerd
Please see FAQ731-376: Eng-Tips.com Forum Policies for tips on how to make the best use of Eng-Tips.
RE: Transposing data from columns
=INDEX($N$18:$N$104,MATCH($W7,IF($M$18:$M$104=Z$3,$L$18:$L$104),0))
RE: Transposing data from columns
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
http://newtonexcelbach.wordpress.com/
RE: Transposing data from columns
Z3-> 1 2
W7-> 1 5 6
2 7 8
You leave the dollar sign off the 7 and off the Z so you can fill the formula up and down or left and right
RE: Transposing data from columns
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
RE: Transposing data from columns
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.
Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/