Transposing Repeat Rows Into Consolidated Columns

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!
--
Rich F

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.
--
Brevity is the soul of wit.


"Rich F" wrote:

> 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!
> --
> Rich F

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!
--
Rich F


"Dave F" wrote:

> 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.
> --
> Brevity is the soul of wit.
>
>
> "Rich F" wrote:
>
> > 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!
> > --
> > Rich F

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.
--
Brevity is the soul of wit.


"Rich F" wrote:

> 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!
> --
> Rich F
>
>
> "Dave F" wrote:
>
> > 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.
> > --
> > Brevity is the soul of wit.
> >
> >
> > "Rich F" wrote:
> >
> > > 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!
> > > --
> > > Rich F

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

Thanks again!
--
Rich F


"Dave F" wrote:

> 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.
> --
> Brevity is the soul of wit.
>
>
> "Rich F" wrote:
>
> > 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!
> > --
> > Rich F
> >
> >
> > "Dave F" wrote:
> >
> > > 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.
> > > --
> > > Brevity is the soul of wit.
> > >
> > >
> > > "Rich F" wrote:
> > >
> > > > 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!
> > > > --
> > > > Rich F

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

Rich F wrote:
>
> 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!
> --
> Rich F

--

Dave Peterson

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!


--
Rich F


"Dave Peterson" wrote:

> 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
>
> Rich F wrote:
> >
> > 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!
> > --
> > Rich F
>
> --
>
> 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.

Rich F wrote:
>
> 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!
>
> --
> Rich F
>
> "Dave Peterson" wrote:
>
> > 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
> >
> > Rich F wrote:
> > >
> > > 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!
> > > --
> > > Rich F
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson