Transposing Repeat Rows Into Consolidated Columns

[Guest]

I've combed through previous posts and I can't find exactly what I'm looking
for, so here goes:

I've got a worksheet with approx 1000+ employees set up in the following
way, repeated as rows:

A B
1 Type Jones
2 FirstName John
3 MiddleInitial
4 LastName Jones
5 FullName John Jones
6 EnterpriseID John.Jones
7 GMUNumber 333
8 GMUDescription Acme Inc
9 LMUNumber 9IJ
10 LMUDescription Special Task Grp
11 DISCIPLINE_CD 70
12 DISCIPLINE_DESC_TEXT Services/Client
13 Position 7778787
14 PositionName Level E
15 Location Timbuktu
16 CostCenterNum 67676766
17 CostCenterDesc Antedilluvian
18 PersonnelNumber 18976565
19 PeopleKey 126767
20 UserType N/A

All the "employee record clumps" have the same number of rows and there are
two lines between each "clump." I'm trying to transpose the info, so the
items in COLUMN A are column headings (there would be 20) and then have each
of employee's data fall into place under each correct column heading. PASTE
SPECIAL/TRANSPOSE doesn't work, unless I do EACH employee seperatly and I
don't, unfortunately, have two or three weeks to cut/paste the 1000+ emps
this way. Is there any other way to do it?

Thanks in advance for the help!

 

[Guest]

Well, the short answer is you would have to write some VBA code to get what
you want because, after you transpose the first dump you don't want the
column headings to appear again.

 

[Guest]

Dave,

That's sort of what I thought. I haven't used Excel in a while (4 or 5
years) and I'm not very conversant with VBA in Excel except for what I can
glean from a few marcos I've built in the past. Any hints on where to start?

Thanks!

 

[Guest]

Record a macro in which you transpose a range.

That will at least give you the syntax for the transposition of a range.

But what you're looking for--repeating that transposition over a series of
ranges--is out of my league in terms of my VBA knowledge.

 

[Guest]

Yeah, that's the part I can't figure out, either.

Thanks again!

 

[Dave Peterson]

You can try something like this.

Option Explicit
Sub testme()
Dim iRow As Long
Dim FirstRow As Long
Dim LastRow As Long
Dim HowManyRowsPerGroup As Long

With Worksheets("sheet1")
FirstRow = 1
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
HowManyRowsPerGroup = 20

'HowManyRowsPerGroup + 2 because of the two blank rows
For iRow = FirstRow To LastRow Step HowManyRowsPerGroup + 2
.Cells(iRow, "B").Resize(HowManyRowsPerGroup, 1).Copy
.Cells(iRow, "C").PasteSpecial Transpose:=True
Next iRow

'copy headers
.Rows(1).Insert
.Cells(2, "A").Resize(HowManyRowsPerGroup, 1).Copy
.Cells(1, "C").PasteSpecial Transpose:=True

On Error Resume Next
.Range("c:c").Cells.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
On Error GoTo 0

.Range("a:b").Delete

.UsedRange.Columns.AutoFit
End With

End Sub

But try it against a copy of your data--it destroys the original data when it
runs.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[Guest]

Dave,

Worked like a charm and with the little knowledge I have of VBA that I've
been able to pick up from Access (and disecting macros in the past), I think
I might even sort of have a slight inklining of how/why this works and what I
don't understand, I'm looking in a VBA book I've gotten access to on-line
through my employer.

Thanks again--you've given me back my weekend!

 

[Dave Peterson]

If you look at that code, it really doesn't do too much.

It groups in sets of 20 rows and copies and pastes|transpose.

Then a little bit of clean up.

If you have questions about anything, post back. I'm sure you'll get lots of
answers.

Dave,

Worked like a charm and with the little knowledge I have of VBA that I've
been able to pick up from Access (and disecting macros in the past), I think
I might even sort of have a slight inklining of how/why this works and what I
don't understand, I'm looking in a VBA book I've gotten access to on-line
through my employer.

Thanks again--you've given me back my weekend!