+ Reply to Thread
Results 1 to 11 of 11

Transpose and shift cell to the left

  1. #1
    Registered User
    Join Date
    07-26-2013
    Location
    Nagaland
    MS-Off Ver
    Excel 2007
    Posts
    18

    Transpose and shift cell to the left

    Hi,
    I need help with transpose. My data is spread over 6 Columns. I learned to transpose one row at a time so that the data spread over 6 columns will be rearranged into 6 rows but, doing it one at a time it's time taking and more chances to make errors And every row contains one cell with - spread over different columns. I want the - to be omitted and the right cell to be shifted to the left. I know how to do it one at a time but is there any formula or tricks to do it all together at once (omit - and shift the right cell to the left)? I know it's a mess but, please help. thank you.
    Attached Files Attached Files
    Last edited by PAUL P ACHUMI; 08-01-2013 at 03:19 AM.

  2. #2
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,760

    Re: Transpose and shift cell to the left

    Try the below code....

    Please Login or Register  to view this content.


    If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
    Mark your thread as Solved


    If the suggestion helps you, then Click *below to Add Reputation

  3. #3
    Forum Contributor rajeshturaha's Avatar
    Join Date
    08-22-2012
    Location
    Assam, India
    MS-Off Ver
    Excel 2003, 2007
    Posts
    236

    Re: Transpose and shift cell to the left

    Use Transpose formula.
    Select 6 no. of Rows and 64 no of Columns
    Then put this formula, press Alt Enter (will fill up the formula within the selected range), again press F2 (will execute the edit mode) and press ctrl + alt + enter (will execute the formula and give the result)

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


    It is a array formula which must be executed by ctrl+alt+enter, or the value will be zero
    Rgd
    RT
    If my answer(s) helped you, please add me reputation by click on *

  4. #4
    Forum Expert Debraj Roy's Avatar
    Join Date
    09-27-2012
    Location
    New Delhi,India
    MS-Off Ver
    Excel 2013
    Posts
    1,469

    Re: Transpose and shift cell to the left

    Hi Sixthsense..

    Will you mind, if i borrow your Sixthsense, for a while..

    Just a little modification..

    Please Login or Register  to view this content.
    Regards!
    =DEC2HEX(3563)

    If you like someone's answer, click the star to give them a reputation point for that answer...

  5. #5
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,760

    Re: Transpose and shift cell to the left

    Good Catch!! Nice modifications and thanks for the correction in code

  6. #6
    Registered User
    Join Date
    07-26-2013
    Location
    Nagaland
    MS-Off Ver
    Excel 2007
    Posts
    18

    Re: Transpose and shift cell to the left

    Thank you guys. since i don't know anything about codes i'll try applying rajeshturaha's answer. will get back. Thanks again

  7. #7
    Registered User
    Join Date
    07-26-2013
    Location
    Nagaland
    MS-Off Ver
    Excel 2007
    Posts
    18

    Re: Transpose and shift cell to the left

    hi
    Rajeshturaha, this is a lame question but, i don't get it. Please let me know where i went wrong. first i selected six rows (A1:A6) and then selected 64 colums (A1:A6 to BL1:BL6) after that i entered the formula [=TRANSPOSE('ACTUAL DATA'!A1:F64)] and followed everything you mentioned but nothing shows up can you kindly do it for me in the file attached and post it for me. thank you

  8. #8
    Forum Contributor rajeshturaha's Avatar
    Join Date
    08-22-2012
    Location
    Assam, India
    MS-Off Ver
    Excel 2003, 2007
    Posts
    236

    Re: Transpose and shift cell to the left

    You must execute the formula with ctrl+alt+enter
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    07-26-2013
    Location
    Nagaland
    MS-Off Ver
    Excel 2007
    Posts
    18

    Re: Transpose and shift cell to the left

    Thanks for the feedback but, mate if i'm not wrong what you gave can be achieved by simple transpose. Maybe i didn't make it clear the first time. My bad. I want the transposed data(s) to be arranged in rows like the one mentioned in "AFTER TRANSPOSE" sheet in my attached file. Any clues how to go about it. Your help will be much appreciated.

  10. #10
    Forum Expert Debraj Roy's Avatar
    Join Date
    09-27-2012
    Location
    New Delhi,India
    MS-Off Ver
    Excel 2013
    Posts
    1,469

    Re: Transpose and shift cell to the left

    Try this Sixthsense's Macro..

    Attachment 254717
    In the meantime.. trying to adapt some Offsset Mod formula.. Give us some time..

  11. #11
    Registered User
    Join Date
    07-26-2013
    Location
    Nagaland
    MS-Off Ver
    Excel 2007
    Posts
    18

    Re: Transpose and shift cell to the left

    Thanks Deb, i'll give it a try.

+ 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 row to column n shift cells down
    By SakshiTandon in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 02-14-2014, 10:26 AM
  2. Replies: 0
    Last Post: 03-26-2013, 02:58 PM
  3. Shift left if cell = X or Y
    By finnsbb in forum Excel General
    Replies: 0
    Last Post: 09-20-2011, 07:50 PM
  4. combining delete blanks, left shift, insert row with transpose
    By newty in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 04-30-2010, 12:02 PM
  5. Shift data to left
    By Steph in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 04-12-2005, 11:06 PM

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