Transposing data from columns 1

[DDP]

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?

 

[IRstuff]

A VBA macro could do that.

TTFN

FAQ731-376

 

[MintJulep]

IF(), MATCH(), OFFSET() in some combination. Possibly need INDEX() in there as well.

I think it could be done with a pivot table too.

 

[IDS]

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

http://newtonexcelbach.wordpress.com/

 

[brengine]

See attached Excel file containing VBA Macro...
1) Paste your Data onto Sheet1
2) Run Macro 'TransformRowColumnData'
3) Transformed Data written on New Sheet

Enjoy!

 

[DDP]

You guys rock!

 

[joerd]

Use a Pivot table. No VBA required.

Cheers,
Joerd

Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.

 

[wannabeSE]

Have you tried Excels transpose function?

 

[electricpete]

lol. There are many ways to skin a cat.

=====================================
Eng-tips forums: The best place on the web for engineering discussions.

 

[cowski]

Have you tried Excels transpose function?

That was my initial reaction, but if you look carefully at the opening post, you will see that it is not a straightforward transpose.

 

[Drew08]

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.

 

[Drew08]

Oops,I mean: SUMIFS(Value Range, Row Range, Row, Column Range, Column)

 

[joerd]

Yes, works if you have Excel 2007. Otherwise, it becomes very cumbersome.

Cheers,
Joerd

Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.

 

[Drew08]

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.

 

[zdas04]

"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

 

[joerd]

Since we're off on a tangent, here are two more solutions to the problem, one using DGET, and one using a pivot table.

Cheers,
Joerd

Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.

 

[DDP]

Just to give an update, I found the following formula is working for me

=INDEX($N$18:$N$104,MATCH($W7,IF($M$18:$M$104=Z$3,$L$18:$L$104),0))

 

[IDS]

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

http://newtonexcelbach.wordpress.com/

 

[DDP]

W7 and Z3 are the respective row and column labels for the new array that you fill in such as

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

 

[zdas04]

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