+ Reply to Thread
Results 1 to 5 of 5

Data in row transpose

  1. #1
    Forum Contributor
    Join Date
    01-23-2016
    Location
    karachi
    MS-Off Ver
    2007
    Posts
    316

    Data in row transpose

    Hello Expert,

    I have text data in row, I want to combine in one cell, I can use Transpose function but there is issue I don't want 0 will add in combine data, please check the screenshot you will better understand what I'm looking.

    Thanks in Advance
    Attached Images Attached Images

  2. #2
    Forum Contributor
    Join Date
    01-23-2016
    Location
    karachi
    MS-Off Ver
    2007
    Posts
    316

    Re: Data in row transpose

    any expert here for help.

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

    Re: Data in row transpose

    No need to bump your thread after only 20 minutes !!

    Pete

  4. #4
    Forum Contributor
    Join Date
    01-23-2016
    Location
    karachi
    MS-Off Ver
    2007
    Posts
    316

    Re: Data in row transpose

    ok, sorry for that.

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

    Re: Data in row transpose

    You can put this formula in B2:

    =IF(OR(A2=0,A2=""),"",A2&" ")&IF(OR(A3=0,A3=""),"",A3&" ")&IF(OR(A4=0,A4=""),"",A4&" ")&IF(OR(A5=0,A5=""),"",A5&" ")&IF(OR(A6=0,A6=""),"",A6&" ")&IF(OR(A7=0,A7=""),"",A7&" ")&IF(OR(A8=0,A8=""),"",A8&" ")&IF(OR(A9=0,A9=""),"",A9&" ")

    It will be easier to follow if I show it like this:

    =IF(OR(A2=0,A2=""),"",A2&" ")
    &IF(OR(A3=0,A3=""),"",A3&" ")
    &IF(OR(A4=0,A4=""),"",A4&" ")
    &IF(OR(A5=0,A5=""),"",A5&" ")
    &IF(OR(A6=0,A6=""),"",A6&" ")
    &IF(OR(A7=0,A7=""),"",A7&" ")
    &IF(OR(A8=0,A8=""),"",A8&" ")
    &IF(OR(A9=0,A9=""),"",A9&" ")

    Just add more terms at the end if you have data below cell A9.

    Hope this helps.

    Pete

+ 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. 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
  2. Replies: 7
    Last Post: 10-03-2014, 05:04 AM
  3. Transpose Data from Columns to Rows after each unique data point
    By lnagell in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 03-01-2014, 08:10 PM
  4. Transpose data to columns from rows keeping unique data together
    By aimeecrystalaid in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 12-09-2013, 12:23 AM
  5. Transpose horizontal data to vertical data with paste link
    By M.Devadhasan in forum Excel General
    Replies: 0
    Last Post: 07-07-2012, 01:01 PM
  6. [SOLVED] Transpose: How can I copy a list and transpose it but leaving 3 cells in between each item
    By cocolete in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 04-11-2012, 10:01 AM
  7. Replies: 2
    Last Post: 02-19-2007, 04:53 PM

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