+ Reply to Thread
Results 1 to 7 of 7

Transpose values

  1. #1
    Registered User
    Join Date
    07-12-2006
    Posts
    54

    Cool Transpose values

    I've collected 200 sample data points in A:A. Looking for a formula that will automatically transpose the first 10 column values into row 1, row 2, row 3, etc. Example, take the value of A2 and put into C2. Repeat until end of table row 1, L2. Continue on next row 2 starting A12 into C3. Repeat until end of table row 2, L3. See attachment. Thanks
    Attached Files Attached Files

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Transpose values

    Put this in C2:

    =INDEX($A:$A,ROWS($1:1)+1+(COLUMNS($C:C)-1)*11)

    then copy across and down as required.

    Hope this helps.

    Pete

  3. #3
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Transpose values

    Enter in C2 pull it across and then down

    =IFERROR(INDEX($A$2:$A$202,ROWS($A$1:$A1)*11-11+COLUMNS($A:A)),"")
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne

  4. #4
    Registered User
    Join Date
    07-12-2006
    Posts
    54

    Re: Transpose values

    Sorry Pete_Uk, that didn't provide the expected results but close.

    Thanks AlKey! That worked great!

  5. #5
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Transpose values

    You're welcome. Please don't forget to thank those who helped by clicking on Add Reputation * and please mark thread as "Solved" if your issue has been resolved. (Selecting Thread Tools-> Mark thread as Solved).

  6. #6
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Transpose values

    Your description was a bit confused - you said:

    ...transpose the first 10 column values into row 1, row 2, row 3, etc...
    which I took to mean that you wanted the first 10 values in the first column.

    Anyway, glad to see that you got an answer.

    Pete

  7. #7
    Forum Expert
    Join Date
    12-09-2014
    Location
    Trakai, Lithuania
    MS-Off Ver
    2016
    Posts
    1,278

    Re: Transpose values

    Formula: copy to clipboard
    Please Login or Register  to view this content.

+ 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. [SOLVED] Transpose specified number of values with VBA
    By sourabhg98 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 03-07-2015, 12:40 PM
  2. [SOLVED] Transpose a long column into delimiters separated values according to values in input box
    By prkhan56 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 12-27-2014, 01:32 PM
  3. [SOLVED] transpose values into one row
    By artefarct in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 11-01-2014, 03:04 AM
  4. [SOLVED] Transpose values
    By leovilla in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 08-02-2013, 08:11 AM
  5. Transpose values in each row into the same column
    By Lifeseeker in forum Excel Programming / VBA / Macros
    Replies: 23
    Last Post: 02-20-2012, 04:28 PM
  6. Transpose values ???
    By brooklyn in forum Excel General
    Replies: 0
    Last Post: 06-06-2007, 07:43 PM
  7. [SOLVED] I WANT TO TRANSPOSE LINKS, AS WE TRANSPOSE VALUES
    By Umair Aslam in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-22-2005, 09:05 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