# Transpose and shift cell to the left

1. ## 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.

2. ## Re: Transpose and shift cell to the left

Try the below code....

``Please Login or Register  to view this content.``

3. ## 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:
`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

4. ## 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.``

5. ## Re: Transpose and shift cell to the left

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

6. ## 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. ## 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. ## Re: Transpose and shift cell to the left

You must execute the formula with ctrl+alt+enter

9. ## 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. ## 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. ## Re: Transpose and shift cell to the left

Thanks Deb, i'll give it a try.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### 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