+ Reply to Thread
Results 1 to 6 of 6

IF/TRANSPOSE formula not transposing the column into the row

  1. #1
    Registered User
    Join Date
    06-05-2019
    Location
    Washington
    MS-Off Ver
    16.23
    Posts
    8

    IF/TRANSPOSE formula not transposing the column into the row

    I am trying to use the IF function to transpose the data in a column of one sheet into a row of another sheet (See attached example) The formula I'm using on the 'Qualifications' sheet in cells B2:H2 is the one I am having trouble with. For some reason, it's only grabbing the data in cell A2 of the 'Shifts' sheet. What am I doing wrong?
    Attached Files Attached Files
    Last edited by ocallaj; 12-04-2019 at 06:45 PM. Reason: Solved

  2. #2
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: IF/TRANSPOSE formula not transposing the column into the row

    Try this formula in B2 and fill across.
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    According to Help files TRANSPOSE is to be committed by selecting the entire output range and then commit with Ctrl + Shift + Enter instead of just Enter. I disagree with that whole range method of committing array formulas for a host of reasons. Wrapping INDEX around your formula (as above) eliminates the need for Ctrl + Shift + Enter.
    Dave

  3. #3
    Forum Contributor
    Join Date
    10-30-2019
    Location
    Bistrita, Romania
    MS-Off Ver
    2019; 365
    Posts
    231

    Re: IF/TRANSPOSE formula not transposing the column into the row

    Pete_UK's solution for transposing that he posted here: https://www.excelforum.com/excel-gen...ml#post5229992
    seems appropriate for your issue too:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Put that in B2 and drag to the right. No IF and no array formula needed.

    Credits to Pete_UK.
    To show your appreciation
    Click ★ Add reputation!

  4. #4
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 365 (Windows)
    Posts
    5,912

    Re: IF/TRANSPOSE formula not transposing the column into the row

    Use the array-entered (enter using Ctrl-Shift-Enter) formula in cells B2:G2 (Select all cells first, then paste the formula, and press Ctrl-Shift-Enter)

    =TRANSPOSE(Shifts!$A$2:$A$7)

    Or enter this as a regular formula in cell B2:

    =INDEX(Shifts!$A$2:$A$7,COLUMN(A1))

    and copy to C2:G2.

    Both these assume you don't want "Shifts" copied over from A1...
    Bernie Deitrick
    Excel MVP 2000-2010

  5. #5
    Registered User
    Join Date
    06-05-2019
    Location
    Washington
    MS-Off Ver
    16.23
    Posts
    8

    Re: IF/TRANSPOSE formula not transposing the column into the row

    Thank you all! Those solutions all worked for me. My issue is solved.

  6. #6
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: IF/TRANSPOSE formula not transposing the column into the row

    You are welcome. Glad to help. Thank you for the feedback, added rep and marking your thread Solved.

+ 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] formula to trim data and transpose in column
    By JEAN1972 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 01-25-2018, 03:55 PM
  2. Transpose not ... well, transposing
    By hathi25 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 11-01-2016, 04:34 PM
  3. transpose column to a range calculate and transpose to new table loop
    By moshro1 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-02-2015, 10:52 AM
  4. [SOLVED] Transpose in one Column with Formula
    By jhabijay_10 in forum Excel General
    Replies: 6
    Last Post: 11-22-2014, 05:08 AM
  5. Replies: 2
    Last Post: 02-17-2013, 04:06 AM
  6. [SOLVED] Need a formula to transpose data from a Column to a Row
    By ads111ads in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 11-12-2012, 07:12 AM
  7. [SOLVED] Transposing without using Transpose formula
    By douglaswarden in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 09-03-2012, 07:18 AM

Tags for this Thread

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