+ Reply to Thread
Results 1 to 7 of 7

Transposing without zeros across separate sheets

  1. #1
    Registered User
    Join Date
    01-15-2020
    Location
    Michigan, USA
    MS-Off Ver
    MS Office 365
    Posts
    3

    Transposing without zeros across separate sheets

    I am trying to format an excel document to transpose my data from one sheet to another. The data I am trying to transpose is non-numeric thus I want to transpose without the zeroes for blank cells. To do this I generally would use an IF statement to say if the cell is blank return a blank cell otherwise return the value in the cell, but now that I am trying to go across sheets, the equation is not working. Below is the equation I have been trying to use.

    {=TRANSPOSE(IF(Sheet1!K:K="","",Sheet1!K:K))}

    Any help is welcome!

    Thanks
    Last edited by jostigg; 01-16-2020 at 09:22 AM.

  2. #2
    Forum Contributor
    Join Date
    11-20-2007
    Location
    Felixstowe, England
    MS-Off Ver
    Excel 2013, 2019
    Posts
    181

    Re: Transposing without zeros across separate sheets

    Works fine for me. Are you sure the blank cells are really blank?

  3. #3
    Registered User
    Join Date
    01-15-2020
    Location
    Michigan, USA
    MS-Off Ver
    MS Office 365
    Posts
    3

    Re: Transposing without zeros across separate sheets

    I believe so. I keep highlighting and deleting the contents before typing the equation each time.
    Not sure why it isn't working for me then.

  4. #4
    Forum Contributor
    Join Date
    11-20-2007
    Location
    Felixstowe, England
    MS-Off Ver
    Excel 2013, 2019
    Posts
    181

    Re: Transposing without zeros across separate sheets

    Quote Originally Posted by jostigg View Post
    I believe so. I keep highlighting and deleting the contents before typing the equation each time.
    Not sure why it isn't working for me then.
    Deleting the contents of Sheet1!K:K? I hope you are meaning that you are clearing some of the cells there. Please clarify if I'm wrong.

  5. #5
    Registered User
    Join Date
    01-15-2020
    Location
    Michigan, USA
    MS-Off Ver
    MS Office 365
    Posts
    3

    Re: Transposing without zeros across separate sheets

    I am clearing the contents of the area that I am creating the formula in which for mean was Sheet2!4:4.

  6. #6
    Forum Contributor
    Join Date
    11-20-2007
    Location
    Felixstowe, England
    MS-Off Ver
    Excel 2013, 2019
    Posts
    181

    Re: Transposing without zeros across separate sheets

    I think we have gotten to the point where you have to describe exactly what you are doing, giving us details of what cells you are selecting at what stage, etc etc, and what is happening (instead of just "it's not working").

  7. #7
    Forum Expert
    Join Date
    07-06-2004
    Location
    Northern California
    MS-Off Ver
    2K, 2003, 2010, O365
    Posts
    1,482

    Re: Transposing without zeros across separate sheets

    Quote Originally Posted by jostigg View Post
    . . . creating the formula in which for mean was Sheet2!4:4 . . .
    You want your TRANSPOSE formula in Sheet2!4:4? Note that entire columns have 1,048,576 rows while entire rows have only 16,384 columns. I'd guess you're not even transposing 16,000-odd value. Better in this case to avoid entire column references.

    Anyway, if ALL the items in Sheet1!T1:T200 (or whatever) should be treated as text, force 'em to be text. Select Sheet2!A4:GR4 and enter the array formula =TRANSPOSE(Sheet1!T1:T200&"") .

+ 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. transposing data read in from separate CSV files
    By Nerpilis in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-10-2017, 12:29 PM
  2. [SOLVED] Macro needed to Concatenate data in 2 separate tables of 2 separate sheets
    By 823 in forum Excel Programming / VBA / Macros
    Replies: 22
    Last Post: 02-08-2015, 02:02 PM
  3. [SOLVED] Macro for transposing bunch of 8 cells in a column into separate rows
    By theotherguy316 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-23-2012, 02:43 PM
  4. transposing chart, omitting zeros
    By redfur in forum Excel General
    Replies: 1
    Last Post: 04-27-2011, 04:53 PM
  5. Replies: 4
    Last Post: 11-22-2010, 12:57 PM
  6. Replies: 3
    Last Post: 08-12-2010, 03:45 PM
  7. Remove blank or zeros when transposing data
    By tuizner in forum Excel General
    Replies: 3
    Last Post: 03-14-2010, 05:55 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