Transpose groups of rows into seperate columns

maluther · Aug 19, 2010

Hello. First time poster here! =)

I've been searching for hours and trying to figure out away to accomplish what I need to. So due to my lack of knowledge, I am turning to all of you wonderful people!

So here is the issue, I have multiple "groups" of data in a single column. Each "group" is separated by a blank row. I need each "group" of data in it's own column.

For example:

Column A
---------
1 | John James
2 | 234-234-2345
3 | john@john.com
4 | www.john.com
5 |
6 | Mike Jones
7 | 123-098-7655
8 |
9 | Mayard James Keenan
10 | 555-555-7777
11 | maynard@rock.com

So I need the data to look like this:

Column A | Column B | Column C
--------- | --------- | ---------
1 | John James | Mike Jones | Mayard James Keenan
2 | 234-234-2345 | 123-098-7655 | 555-555-7777
3 | john@john.com| | maynard@rock.com
4 | www.john.com

The number of rows in each "group" of data is not always the same. So I need to be able to create a new column based on the data between the blank rows. I hope this makes sense.

If there is anything else I need to add, please do let me know. I eagerly await the answer from your brilliant minds! =) Thank you in advance for any help on this! Have a great day!

-Mark-

<link rel="File-List" href="file:///C:%5CUsers%5CMark%5CAppData%5CLocal%5CTemp%5Cmsohtmlclip1%5C01%5Cclip_filelist.xml"><link rel="themeData" href="file:///C:%5CUsers%5CMark%5CAppData%5CLocal%5CTemp%5Cmsohtmlclip1%5C01%5Cclip_themedata.thmx"><link rel="colorSchemeMapping" href="file:///C:%5CUsers%5CMark%5CAppData%5CLocal%5CTemp%5Cmsohtmlclip1%5C01%5Cclip_colorschememapping.xml"><style> <!-- /* Font Definitions */ @font-face {font-family:"Cambria Math"; panose-1:2 4 5 3 5 4 6 3 2 4; mso-font-charset:0; mso-generic-font-family:roman; mso-font-pitch:variable; mso-font-signature:-1610611985 1107304683 0 0 159 0;} @font-face {font-family:Calibri; panose-1:2 15 5 2 2 2 4 3 2 4; mso-font-charset:0; mso-generic-font-family:swiss; mso-font-pitch:variable; mso-font-signature:-1610611985 1073750139 0 0 159 0;} /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal {mso-style-unhide:no; mso-style-qformat:yes; mso-style-parent:""; margin:0in; margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:11.0pt; font-family:"Calibri","sans-serif"; mso-ascii-font-family:Calibri; mso-ascii-theme-font:minor-latin; mso-fareast-font-family:Calibri; mso-fareast-theme-font:minor-latin; mso-hansi-font-family:Calibri; mso-hansi-theme-font:minor-latin; mso-bidi-font-family:"Times New Roman"; mso-bidi-theme-font:minor-bidi;} span.EmailStyle15 {mso-style-type

ersonal; mso-style-noshow:yes; mso-style-unhide:no; mso-ansi-font-size:11.0pt; mso-bidi-font-size:11.0pt; font-family:"Calibri","sans-serif"; mso-ascii-font-family:Calibri; mso-ascii-theme-font:minor-latin; mso-fareast-font-family:Calibri; mso-fareast-theme-font:minor-latin; mso-hansi-font-family:Calibri; mso-hansi-theme-font:minor-latin; mso-bidi-font-family:"Times New Roman"; mso-bidi-theme-font:minor-bidi; color:windowtext;} .MsoChpDefault {mso-style-type:export-only; mso-default-props:yes; mso-ascii-font-family:Calibri; mso-ascii-theme-font:minor-latin; mso-fareast-font-family:Calibri; mso-fareast-theme-font:minor-latin; mso-hansi-font-family:Calibri; mso-hansi-theme-font:minor-latin; mso-bidi-font-family:"Times New Roman"; mso-bidi-theme-font:minor-bidi;} @page WordSection1 {size:8.5in 11.0in; margin:1.0in 1.0in 1.0in 1.0in; mso-header-margin:.5in; mso-footer-margin:.5in; mso-paper-source:0;} div.WordSection1 {page:WordSection1;} --> </style>[FONT=&quot]"Nothing is impossible. Some things are just less likely than others." - Jonathan Winters (American Comedian and Actor, b.1925)[/FONT]

maluther · Aug 19, 2010

Ah... I also forgot something else. If it wouldn't be too hard, I'd like for the solution to be able to create a new worksheet and continue on if it reaches the 256 column limit or whatever it is. I have about 45,000 rows of data like this. =/

[FONT=&quot]
-Mark-[/FONT]

[FONT=&quot] <o

></o

>[/FONT]
[FONT=&quot]"Nothing is impossible. Some things are just less likely than others." - Jonathan Winters (American Comedian and Actor, b.1925)

[/FONT]

GTO · Aug 19, 2010

Are the cells between records actually blank? For instance, in your sample data, if you select A5 or A8 and press F2, then the forward and back keys, can ou confirm there are no spaces/non-printing characters in these 'divider cells'?

hiker95 · Aug 19, 2010

maluther,

What version of Excel are you using?

hiker95 · Aug 19, 2010

maluther,

Sample data before the mcaro:

Excel Workbook

A

1

John James

2

234-234-2345

3

john@john.com

4

www.john.com

5

6

Mike Jones

7

123-098-7655

8

9

Mayard James Keenan

10

555-555-7777

11

maynard@rock.com

12

Sheet1

After the macro in a new worksheet Results:

Excel Workbook

A

B

C

1

John James

Mike Jones

Mayard James Keenan

2

234-234-2345

123-098-7655

555-555-7777

3

john@john.com

maynard@rock.com

4

www.john.com

5

Results

After the second group is processed, I manually force the column count to the maximum, and the next groups will begin three rows below the last used row in the worksheet, like this:

Excel Workbook

A

B

1

John James

Mike Jones

2

234-234-2345

123-098-7655

3

john@john.com

4

www.john.com

5

6

7

Mayard James Keenan

8

555-555-7777

9

maynard@rock.com

10

Results

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Adding the Macro
1. Copy the below macro, by highlighting the macro code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Code:

Option Explicit
Sub ReorgData()
' hiker95, 08/19/2010, ME489624
' Thank you Peter_SSs
Dim w1 As Worksheet, wR As Worksheet
Dim AArea As Range, NR As Long, NC As Long, LR As Long
Application.ScreenUpdating = False
Set w1 = Sheets(1)
If Not Evaluate("ISREF(Results!A1)") Then Worksheets.Add(After:=w1).Name = "Results"
Set wR = Worksheets("Results")
wR.UsedRange.ClearContents
w1.Activate
NC = 0: NR = 1
For Each AArea In w1.Range("A1", w1.Range("A" & Rows.Count).End(xlUp)).SpecialCells(xlCellTypeConstants).Areas
  NC = NC + 1
  If NC >= wR.Columns.Count Then
    Application.Volatile
    NR = wR.Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row + 3
    NC = 1
  End If
  If NR = 1 Then
    wR.Range(wR.Cells(NR, NC), wR.Cells(AArea.Rows.Count, NC)).Value = AArea.Value
  Else
    wR.Range(wR.Cells(NR, NC), wR.Cells(NR + AArea.Rows.Count - 1, NC)).Value = AArea.Value
  End If
Next AArea
wR.UsedRange.Columns.AutoFit
wR.Activate
Application.ScreenUpdating = True
End Sub

Then run the "ReorgData" macro.

maluther · Aug 20, 2010

hiker95

I'm using Excel 2007. Your macro works perfectly! Thank you so very much!

I hope others are able to use this as well! If you need anything regarding Systems Administration or Network Administration for Linux, Windows, Cisco, or any other, please let me know. My contact info is in my signature! Again, Thank you hiker95!!!!!!

hiker95 · Aug 20, 2010

maluther,

Thank you very much.

You are very welcome - come back anytime.

Transpose groups of rows into seperate columns

maluther

New Member

Excel Facts

maluther

New Member

GTO

MrExcel MVP

hiker95

Well-known Member

hiker95

Well-known Member

maluther

New Member

hiker95

Well-known Member

Similar threads

Forum statistics

Share this page

Transpose groups of rows into seperate columns

maluther

New Member

Excel Facts

maluther

New Member

GTO

MrExcel MVP

hiker95

Well-known Member

hiker95

Well-known Member

maluther

New Member

hiker95

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock