Transpose Data (Column to Rows)

**Maxpsb47** · 06-17-2021, 02:22 PM

Hi,

Hope that an expert can help me out with this.

I'm trying to transpose the data shown in the attached example; from a column (Ex:A2:A46) into rows (Ex:C2:Y3).

Problem is that the number of rows being transposed is not a fixed number, only differentiating factor is that the it begins at a 6 digit number and ends in the cell before the next 6 digit number
(Ex:A2:A23 is the data that'll be transposed into the first row & A24:A46 the second row).

There a lot of data in the spreadsheet so if someone can help me with a VBA Macro that can automatically transpose the data, that'll help me a lot!!

Thank you,

**nankw83** · 06-17-2021, 03:25 PM

Hi Maxpsb47 & welcome to the forum

Try below code based on your sample file ...

Please Login or Register  to view this content.

**Maxpsb47** · 06-17-2021, 03:38 PM

Oh wow!! I think this should work just fine. I'll run this on my master worksheet and see how it turns out. Thanks, you are a life saver!!

**Maxpsb47** · 06-17-2021, 04:19 PM

Hi nankw83,

Thanks again for the quick response.

Ran into a issue here, please refer attached example for more information.

The macro is not picking up the 6 digit numbers consistently also it looks like data is being duplicated somehow.

The attached picture shows a "Run-time error '1004': that popped up after I ran the macro.

**alansidman** · 06-17-2021, 05:25 PM

Here is an alternative solution using Power Query.

File is attached. Here is the Mcode

Please Login or Register  to view this content.

Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

- Follow this link to learn how to install Power Query in Excel 2010 / 2013.

- Follow this link for an introduction to Power Query functionality.

- Follow this link for a video which demonstrates how to use Power Query code provided.

**jindon** · 06-18-2021, 01:57 AM

Please Login or Register  to view this content.

**nankw83** · 06-18-2021, 03:02 AM

Originally Posted by Maxpsb47

The macro is not picking up the 6 digit numbers consistently also it looks like data is being duplicated somehow.

Try revised code below ...

Please Login or Register  to view this content.

**Maxpsb47** · 06-21-2021, 11:06 AM

Hi jindon,

Thanks for helping out.

The macro works really quick and consistently although I did get an error message (refer to attachment) and it did not transpose all of my data.

All I did was change (whats marked in red)

x = Filter([transpose(if((isnumber(b1:b129903))*(len(b1:b129903)=6),row(1:129903)))], False, 0)

**Maxpsb47** · 06-21-2021, 11:09 AM

Hi nankw83,

I really appreciate the reply.

I did end up with another error message (refer attachment). The macro is definitely working more consistently but it stops midway.

**Maxpsb47** · 06-21-2021, 11:12 AM

Hi alansidman,

Thanks for mentioning about Power Query, I learned something new here. The Mcode you sent ended up transposing everything into 1 single row. I will definitely mess around with Power Query more and see were all it might be applicable at work.

**nankw83** · 06-21-2021, 11:29 AM

Maxpsb47, what's the maximum number of items possible under each number. i.e. How many columns would be enough ?

Edit: Try below code ...

Please Login or Register  to view this content.

**Maxpsb47** · 06-21-2021, 11:38 AM

nankw83, I can't say for sure since all of this data was exported from an pdf file but after using the marco the maximum columns i saw was 34

**nankw83** · 06-21-2021, 11:40 AM

In the code I posted in post #11, I am assuming maximum number of columns is 100 which you can change in the below line

Please Login or Register  to view this content.

So does the code work now ?

**Maxpsb47** · 06-21-2021, 11:51 AM

nankw83,

This code works perfectly, thanks a lot for the help man. I just gotta clean up the data and make it more workable.

**nankw83** · 06-21-2021, 11:53 AM

Glad to help & thanks for the feedback

**alansidman** · 06-21-2021, 07:27 PM

thanks for the rep and feedback. If you are serious about learning PQ then the book, "M is for (Data) Monkey" by Ken Puls and Miguel Escobar.

Transpose Data (Column to Rows)

LinkBack

Thread Tools

Rate This Thread

Display

Transpose Data (Column to Rows)

Re: Transpose Data (Column to Rows)

Re: Transpose Data (Column to Rows)

Re: Transpose Data (Column to Rows)

Re: Transpose Data (Column to Rows)

Re: Transpose Data (Column to Rows)

Re: Transpose Data (Column to Rows)

Re: Transpose Data (Column to Rows)

Re: Transpose Data (Column to Rows)

Re: Transpose Data (Column to Rows)

Re: Transpose Data (Column to Rows)

Re: Transpose Data (Column to Rows)

Re: Transpose Data (Column to Rows)

Re: Transpose Data (Column to Rows)

Re: Transpose Data (Column to Rows)

Re: Transpose Data (Column to Rows)

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Transpose data from 1 column to rows

Transpose Column Data to Rows

Transpose rows to column and insert new rows to fill in data

[SOLVED] Please help Transpose data from column to rows

Transpose Column Data to Rows?

Insert rows, transpose data from column in to rows, in steps

[SOLVED] Transpose Data from a column to several rows

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions