×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Transposing data from columns

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?

RE: Transposing data from columns

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

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

RE: Transposing data from columns

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/
 

RE: Transposing data from columns

(OP)
You guys rock!

RE: Transposing data from columns

Have you tried Excels transpose function?

RE: Transposing data from columns

lol.  There are many ways to skin a cat.

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

RE: Transposing data from columns

Quote:

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.

RE: Transposing data from columns

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.   

RE: Transposing data from columns

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

RE: Transposing data from columns

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

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

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.
 

RE: Transposing data from columns

"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

RE: Transposing data from columns

(OP)
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))

RE: Transposing data from columns

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/
 

RE: Transposing data from columns

(OP)
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

RE: Transposing data from columns

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

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/
 

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members! Already a Member? Login


Resources

Low-Volume Rapid Injection Molding With 3D Printed Molds
Learn methods and guidelines for using stereolithography (SLA) 3D printed molds in the injection molding process to lower costs and lead time. Discover how this hybrid manufacturing process enables on-demand mold fabrication to quickly produce small batches of thermoplastic parts. Download Now
Design for Additive Manufacturing (DfAM)
Examine how the principles of DfAM upend many of the long-standing rules around manufacturability - allowing engineers and designers to place a part’s function at the center of their design considerations. Download Now
Taking Control of Engineering Documents
This ebook covers tips for creating and managing workflows, security best practices and protection of intellectual property, Cloud vs. on-premise software solutions, CAD file management, compliance, and more. Download Now

Close Box

Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close