Has anybody done this? - Restructuring a row based list to column based
 

Hi folks, I am running into an issue with restructuring a database and
feel their must be an easier way than by hand and it seems someone
must have run into this in the past.

I have a spreadsheet that is structured as follows:


Last Name First Name Date of Birth Sending
Location Receiving Location
Equipment
Frankfort John DOB1 School1 Receiving1 Equipment1
Frankfort John DOB1 School1 Receiving1 Equipment2
Frankfort John DOB1 School1 Receiving1 Equipment3
Smith Ron DOB2 School2 Receiving2 EquipmentA
Smith Ron DOB2 School2 Receiving2 EquipmentC


What I need to do is restructure this sheet into a single row for
each
individual based on last name AND first name with Equipment listed in
ascending columns instead of rows. Like:


Last Name First Name Date of Birth Sending
Location Receiving Location
Equipment Equipment Equipment
Frankfort John DOB1 School1 Receiving1
Equipment1 Equipment2
Equipment3
Smith Ron DOB2 School2 Receiving2 EquipmentA
EquipmentB


I have an indeterminant number of Last and first names (my table is
300 individuals and growing) and each individual has an indeterminant
number of equipment pieces (some have 1, some have 20+). My table
currently only has a column range of A to E, so horizontal growth for
as many equipment columns is fine.


Does anybody have a script that will do this? It seems like it should
be simple I am just not VB Savey. Many thanks in advance,


Ryan

Has anybody done this? - Restructuring a row based list to column based
 

Okay,

I have discovered that I can use the Paste-Special Transpose feature
to change F1, F2, F3 ... into F1, G1, H1 ... however how can I loop
this function for each independent last name/first name combo? This
part is well out of my league. Any recommendations?

Has anybody done this? - Restructuring a row based list to col
 

Is this simple? I'm only checking first and last name. Not birthdate,
sending location, receiving locattion. You may have to add more items to the
IF statement if too many rows get combined.


Sub combinerows()

LastRow = Cells(Rows.Count, "A").End(xlUp).Row

RowCount = 2
For LoopCount = 2 To (LastRow - 1)

If (Cells(RowCount, "A") = Cells(RowCount + 1, "A")) And _
(Cells(RowCount, "B") = Cells(RowCount + 1, "B")) Then


LastCol = Cells(RowCount, Columns.Count).End(xlToLeft).Column
Cells(RowCount, LastCol + 1) = Cells(RowCount + 1, "F")
Cells(RowCount + 1, "A").EntireRow.Delete

Else
RowCount = RowCount + 1
End If

Next LoopCount


End Sub


"JokerFrowns" wrote:

Okay,

I have discovered that I can use the Paste-Special Transpose feature
to change F1, F2, F3 ... into F1, G1, H1 ... however how can I loop
this function for each independent last name/first name combo? This
part is well out of my league. Any recommendations?

Has anybody done this? - Restructuring a row based list to col
 

Joel, this is perfect, however is it possible to somehow merge this
loop with the rest of what I need to do to modify the formating? ...
basicly I have:

A 1
A 2
A 3
B X
B Y
C p
C q
C r
C s

and in the end I need it to look like:

A 1 2 3
B X Y
C p q r s

of course this is an oversimplification, to me it is column F that
needs to be changed from 1F, 2F, 3F structure to 1F, 1G, 1H structure,
based on each "unique" last-first name combo in column A.

Anyone? Thanks.

Ryan

Has anybody done this? - Restructuring a row based list to col
 

Just to briefly add, there is no limit to the number of independent
rows which any individual may have, at the moment that is entirely
based on the amount of equipment pieces they have.

Has anybody done this? - Restructuring a row based list to col
 

Nevermind, I didn't realize that this does it entirely! this is
perfect thank you so much!

Cheers,

Ryan

Has anybody done this? - Restructuring a row based list to col
 

Your requirements are not clear. it seems you want to merge aditional
columns after column F. One problem I see is the titles for each column may
not be correct when you get done.

You may want to combine the equipment in one cell rather than put the data
in new cells. "equipment1, equipment2"

"JokerFrowns" wrote:

Joel, this is perfect, however is it possible to somehow merge this
loop with the rest of what I need to do to modify the formating? ...
basicly I have:

A 1
A 2
A 3
B X
B Y
C p
C q
C r
C s

and in the end I need it to look like:

A 1 2 3
B X Y
C p q r s

of course this is an oversimplification, to me it is column F that
needs to be changed from 1F, 2F, 3F structure to 1F, 1G, 1H structure,
based on each "unique" last-first name combo in column A.

Anyone? Thanks.

Ryan

Has anybody done this? - Restructuring a row based list to col
 

Sorry if I was at all unclear Joel, but your initial code was exactly
what I needed, I will simply need to add some more headers.