# How to transpose a single column into individual rows based on a similar data?

1. ## How to transpose a single column into individual rows based on a similar data?

Suppose that we have a column including a million rows like this:

12
56
35
17
130
XYZ 1
XYZ 2
3
93
55
XYZ 3
12
XYZ 4
156
83
15
12
XYZ 5

How to transpose this single column into individual rows based on a XYZ i? Like this:

XYZ 1 12 56 35 17 130
XYZ 2
XYZ 3 3 93 55
XYZ 4 12
XYZ 5 156 83 15 12

Here is a solution but I couldn't edit the code to solve my own problem:
"How to transpose variable data from column A to multiple rows"

Any help would be greatly appreciated.

2. ## Re: How to transpose a single column into individual rows based on a similar data?

Hi sushiant,

Here's one way:

``Please Login or Register  to view this content.``
Note if do actually have a million rows (from Excel 2007 each sheet "only" has a maximum 1,046,576 rows) it will take some time to run.

Regards,

Robert

3. ## Re: How to transpose a single column into individual rows based on a similar data?

Thanks, you're a genius. well done, it works great.

4. ## Re: How to transpose a single column into individual rows based on a similar data?

You're welcome

5. ## Re: How to transpose a single column into individual rows based on a similar data?

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

6. ## Re: How to transpose a single column into individual rows based on a similar data?

Originally Posted by sushiant
Suppose that we have a column including a million rows like this:
For such bulky data
``Please Login or Register  to view this content.``

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