Transpose every 4 rows in one column to 4 column in one row

**ttkt** · 01-11-2013, 04:05 AM

Hello,

Please help to create a Marco in which transpose data from every 4 rows in one column to 4 columns in the one row. Since there are so many data (5000+) so I think Macro will be helpful to sort this out. Please refer to the the attachment for sample data and expected output.

Many thanks in advance.
-Lena

**OllieB** · 01-11-2013, 04:24 AM

Hi Lena,

Not a very pretty solution, but it works. Add a worksheet named "Result" to your workbook and add the code below to a module and run.

Please Login or Register  to view this content.

**OllieB** · 01-11-2013, 04:46 AM

Or a shorter version, using copy & paste with transpose

Please Login or Register  to view this content.

**Andrew-R** · 01-11-2013, 05:16 AM

And an even shorter one, which doesn't use looping, so is likely to be much faster

Please Login or Register  to view this content.

**AB33** · 01-11-2013, 05:27 AM

As an option

Please Login or Register  to view this content.

**OllieB** · 01-11-2013, 06:33 AM

Originally Posted by Andrew-R

And an even shorter one, which doesn't use looping, so is likely to be much faster

I am sure it is, but honestly I cannot make head or tails from it. I still have a lot to learn

**Andrew-R** · 01-11-2013, 06:42 AM

Originally Posted by OllieB

I am sure it is, but honestly I cannot make head or tails from it. I still have a lot to learn

Well if we were trying to solve this problem with formula we could use:

=OFFSET($A$2,(ROW()-2)*4+COLUMN()-3,0)

Because all of the cells would have the exact same formula in we can use a macro to write the formula to all of the cells at once, calculate the result and then replace the formula with the value it returns.

I couldn't have done this when I joined this forum, 2 and a bit years ago, but it's a great place to learn new Excel skills

**OllieB** · 01-11-2013, 07:08 AM

Originally Posted by Andrew-R

Well if we were trying to solve this problem with formula we could use:

Andrew, thanks for the explanation. Very interesting approach. I tend to use basic VBA for every problem, and sometimes forget that Excel offers more than just an environment to write VBA solutions in.

**mike7952** · 01-11-2013, 08:18 AM

Yet another

Please Login or Register  to view this content.

**OllieB** · 01-11-2013, 10:10 AM

Mike,

Is it really necessary to use the With clause?

Please Login or Register  to view this content.

or would the following also work, since we seem to be striving for short code

Please Login or Register  to view this content.

**Marcol** · 01-11-2013, 11:19 AM

Is it necessary to use VBa?

Try this in C2, Drag Across to F2, then Down.

Formula:

Please Login or Register  to view this content.

**mike7952** · 01-11-2013, 11:25 AM

@Marcol

Yeap that works for me.

@OllieB

No not really necessary, just personal preference

**ttkt** · 01-15-2013, 12:14 PM

Yeap It works! Wow it's amazing, you all are super VBA coding and Excel expert! Thank you so much for your all your help Mike, Marcol, OllieB, Andrew, AB33. I'm new vba and I still learn it.
Thanks again
-Lena

**Marcol** · 01-15-2013, 12:42 PM

On behalf of all, happy to have helped ...

**Naja** · 01-15-2013, 12:45 PM

Hello,

Can you please attach the excel workbook with thr working vba?

Thank you

**cog38** · 11-07-2013, 11:43 AM

Hello - I have a very similar question and I was wondering if anyone could assist. I have a range of values in one tab called "Debt" and I want to tranpose the data into rows starting in cell C2 on my "Statatest" tab. However, I would like to paste the data on every fifth row. I cannot figure out how to do this my current code only pastes and transposes to one row. In all, I have five worksheets with data I want to copy and transpose to the Statatest tab. Any thoughts are greatly appreciated?

Here is my code:

Please Login or Register  to view this content.

**AB33** · 11-07-2013, 11:59 AM

Cog38,
Welcome to the forum.
Please read forum's rule.
You need to start your own thread.

**arlu1201** · 11-07-2013, 01:41 PM

Thanks AB33.

Also cog38,

I have added code tags to your post. As per forum rule 3, you need to use them whenever you put any code in your post. Please add them in future. In order to put code tags, either type [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] at the end of it, OR you can highlight your code and click the # icon at the top of your post window.

Transpose every 4 rows in one column to 4 column in one row

LinkBack

Thread Tools

Rate This Thread

Display

Transpose every 4 rows in one column to 4 column in one row

Re: Transpose every 4 rows in one column to 4 column in one row

Re: Transpose every 4 rows in one column to 4 column in one row

Re: Transpose every 4 rows in one column to 4 column in one row

Re: Transpose every 4 rows in one column to 4 column in one row

Re: Transpose every 4 rows in one column to 4 column in one row

Re: Transpose every 4 rows in one column to 4 column in one row

Re: Transpose every 4 rows in one column to 4 column in one row

Re: Transpose every 4 rows in one column to 4 column in one row

Re: Transpose every 4 rows in one column to 4 column in one row

Re: Transpose every 4 rows in one column to 4 column in one row

Re: Transpose every 4 rows in one column to 4 column in one row

Re: Transpose every 4 rows in one column to 4 column in one row

Re: Transpose every 4 rows in one column to 4 column in one row

Re: Transpose every 4 rows in one column to 4 column in one row

Re: Transpose every 4 rows in one column to 4 column in one row

Re: Transpose every 4 rows in one column to 4 column in one row

Re: Transpose every 4 rows in one column to 4 column in one row

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions