+ Reply to Thread
Results 1 to 6 of 6

Transpor Lines/Columns

  1. #1
    Registered User
    Join Date
    04-18-2011
    Location
    Virginia
    MS-Off Ver
    Excel 2010
    Posts
    8

    Unhappy Transpor Lines/Columns

    Hi, look at the spreasheet attached. there is info on column A, and on columns B to F, the values. the idea is to get as result as the columns J and K.
    please let me know if someone knows how to solve it using functions.
    thx.
    Attached Files Attached Files

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Transpor Lines/Columns

    In your sample, there were always 5 horizontal values that needed to be transposed. This will work if there are missing values:

    =IFERROR(INDEX(A:A,AGGREGATE(15,6,ROW($A$2:$A$6)/($B$2:$F$6<>""),ROWS(M$2:M2)),1),"")

    and

    =IF($M2="","",INDEX(B$2:F$6,MATCH($M2,$A$2:$A$6,0),(COUNTIF($M$2:$M2,$M2))))

    If there are ALWAYS 5 and ONLY 5 it can be simplified a bit...

    Please check your profile to ensure that the Excel version you're using is still correct. Change it, as required.
    Attached Files Attached Files
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  3. #3
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Transpor Lines/Columns

    Hi brjohnsmith,

    See if this formula doesn't solve your problem.
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Array to 2 Columns.xlsx
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  4. #4
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Transpor Lines/Columns

    ...another option

    In A7 =INDEX($A$2:$A$6,MOD(ROW()-2,5)+1,1)
    In B7 =INDEX($B$2:$F$6,INT((ROW()-6.5)/5+1),MOD(ROW()-2,5)+1)

    Copy A7:B7 down for 25 rows
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  5. #5
    Registered User
    Join Date
    04-18-2011
    Location
    Virginia
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: Transpor Lines/Columns

    Hi Glenn, thank you, it worked , this was only an example, it could have more columns and lines. I adjusted it. however, I had to pull down the values until to the max combination, is there a way to set the complete values at once? thx.

  6. #6
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    Re: Transpor Lines/Columns

    The following isn't fully automatic; however, it will automatically adjust when rows are added/deleted.
    1. Add column headers in A1:F1
    2. Convert the range A1:F6 into an Excel table
    3. Use the following modification of Glenn's formula for column M:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    4. Use the following modification of Glenn's formula for column N:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Note that if columns are added then the [Column6] references will need to be changed.
    Let us know if you have any questions.
    Attached Files Attached Files
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. macro to autofilter and copy first 15 lines visible lines in columns D:E
    By Juraj123 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-20-2013, 03:40 AM
  2. Vlookup with lines instead of columns?
    By bryan444 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-13-2012, 05:24 AM
  3. macro - columns to lines
    By lenorsk in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-24-2012, 09:17 AM
  4. moving a lot of lines into columns
    By RhinoVibe in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-06-2008, 05:41 AM
  5. Converting columns to lines
    By ForSubscription in forum Excel General
    Replies: 1
    Last Post: 08-30-2007, 10:22 AM
  6. [SOLVED] Mixing For i = x to y and j = z to y for lines and columns
    By [email protected] in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-31-2006, 11:03 AM
  7. Format new Lines into Columns
    By efenili in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 05-16-2006, 01:15 PM
  8. [SOLVED] Add more lines and more columns in Excel
    By Marc Charbonneau in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-20-2005, 12:06 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1