+ Reply to Thread
Results 1 to 9 of 9

Problem with transpose

  1. #1
    Registered User
    Join Date
    04-11-2012
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    46

    Problem with transpose

    Hi All

    My dataset is this

    5210 9673 1.86
    5379 10243 1.9
    5484 10357 1.89
    5496 10392 1.89
    5606 10303 1.84

    and I want to convert this into this:

    5210 9673 1.86 5379 10243 1.9 5484 10357 1.89 5484 10357 1.89 5496 10392 1.89 5606 10303 1.84

    Any way I can do this? I will really appreciate some help. Thanks

  2. #2
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.83 for Mac MS 365
    Posts
    8,480

    Re: Problem with transpose

    you could try this...
    =INDEX($A$2:$B$10,(ROWS($E$2:E2)-1)*3+CEILING(COLUMNS($E$2:E2)/2,1),MOD(COLUMNS($E$2:E2)-1,2)+1)&""
    It may need some tweaking.
    Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
    Sam Capricci

  3. #3
    Registered User
    Join Date
    04-11-2012
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    46

    Re: Problem with transpose

    I will try that. Thanks

  4. #4
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.83 for Mac MS 365
    Posts
    8,480

    Re: Problem with transpose

    I'm still working to tweak it to your data and will let you know if it needs changed.

  5. #5
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.83 for Mac MS 365
    Posts
    8,480

    Re: Problem with transpose

    This is how I'd tweak it to work for your data...
    '=INDEX($A$2:$B$10,(ROWS($E$2:E2)-1)*3+CEILING(COLUMNS($E$2:E2)/3,1),MOD(COLUMNS($E$2:E2)-1,3)+1)&""
    appears to do the job.

  6. #6
    Registered User
    Join Date
    04-11-2012
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    46

    Re: Problem with transpose

    Actually I am not a very good excel user. Should i just copy the formula and paste it in a blank column?

  7. #7
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.83 for Mac MS 365
    Posts
    8,480

    Re: Problem with transpose

    It depends on where your data begins and ends and where you want to populate the transposed data too.
    So, for instance if your original data begins in columns A1, B1 and C1 and you want it to populate over beginning in D1, you just it should really be this...
    =INDEX($A$1:$C$5,(ROWS($D$1:D1)-1)*3+CEILING(COLUMNS($D$1:D1)/3,1),MOD(COLUMNS($D$1:D1)-1,3)+1)&""
    then drag to the right from there.
    it really depends on the "depth of your data" as to whether you change A1:C5 to something longer (like A1:C200 or so). Just remember you have only so much width in excel.

  8. #8
    Registered User
    Join Date
    04-11-2012
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    46

    Re: Problem with transpose

    it is working absolutely brilliantly..thanks a lot man! you saved my day

  9. #9
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.83 for Mac MS 365
    Posts
    8,480

    Re: Problem with transpose

    Glad to hear that! Don't forget to mark the post as solved (under thread tools at the top) and I'm not averse to having my reputation bumped (sorry for the shamlessness of that) as it helps.

+ 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. Problem in Split and Transpose
    By chiragsinghal4 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 06-19-2012, 10:37 AM
  2. Copy and Transpose Problem
    By SimonDorfman in forum Excel General
    Replies: 2
    Last Post: 12-22-2011, 01:43 PM
  3. problem with worksheetfunction.transpose
    By ramserp in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-17-2009, 08:11 AM
  4. Not exactly a transpose problem
    By Wibs in forum Excel General
    Replies: 5
    Last Post: 06-15-2006, 06:49 AM
  5. [SOLVED] Transpose Problem
    By Biman in forum Excel General
    Replies: 8
    Last Post: 01-13-2005, 10: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