Macro to convert rows to columns

[Riaang]

Hi there,

Can anyone help me to write a macro that can convert rows into columns. I have a excel spreadsheet and in column A is the member number. From B to E will be the rest of the detail. On the second line or row the member number will repeat in column A but in column B to E is different information for the member than in row 1.

I want all the information of the member to be in one line. Some member have only 2 lines whereby other members can have more than that.

Any help will be appreciated.

Thanks

 

[Nimrod]

Hello HalfAce...
Yea ... well... I've just done it about a million times myself

 

[jOSEFFB]

I tried using the Macro that Half ace wrote and it works to some degree to naturalize the data. But when I try it on a fresh spreadsheet (unaltered) that is emailed to me it comes up with a error 400. Here is how the fields of the data are. Help is much appreciated.

The goal is to get all four rows (one record) into one row : Plan - Forecast - Actual - Comments.

Report.xls
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
1	Record Name	CASPR Project No., Search Ring Name, Site Type	Project Type	Market	Plan/ Forecast/ Actual Dates	MS000 Handoff to CPM	MS010 Search Ring Issued	MS020 Primary Approved	MS030 Lease Fully Executed	MS036 Zoning Submitted	MS040 Zoning Approved	MS075 BP Submitted	MS080 BP Received	MS093 Site Acq. Complete	MS095 Construction Start	MS110 Site Ready For Equip.	MS120 Equip. Install Complete	MS125 Construction Complete	MS145 RF Opt. & Integrated (private)	MS150 Site Opt. & Integration (on air)	MS155 Turnover Package Comp. (BLIP)	MS160 Final Acceptance
2	A-04-008 - Voorheesville WT	A-04-008 - Voorheesville WT	New	New York	Plan	2/4/2004	2/4/2004	2/17/2004	3/4/2004	2/24/2004	3/25/2004	3/1/2004	3/11/2004	4/26/2004	5/1/2004	5/6/2004	5/7/2004	5/11/2004	8/25/2004		5/20/2004	7/21/2004
3	A-04-008 - Voorheesville WT	A-04-008	Build		Forecast	2/10/2004	2/10/2004	7/12/2004	10/15/2004	11/19/2004	5/14/2005	5/14/2005	5/27/2005	3/20/2005	5/2/2005	5/29/2005	6/6/2005	6/6/2005	8/25/2004		6/20/2005	6/22/2005
4	A-04-008 - Voorheesville WT	Colo			Actual	2/4/2004	2/4/2004	7/19/2004	10/15/2004	11/12/2004
5	A-04-008 - Voorheesville WT	Comments: Existing tank, ground space available for monopole
Report

 

[chiello]

Do you need what shown below?

Cartel1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	RecordName	Data1	Data2	Data3	Data4
2	A	I	YOU	HE	SHE
3	A	WE	YOU	THEY
4	A	RED			GREEN
5
6	Doyouwantwhatbelow??
7
8	1
9	RecordName	Data1	Data2	Data3	Data4	Data1	Data2	Data3	Data4	Data1	Data2	Data3	Data4
10	A	I	YOU	HE	SHE	WE	YOU	THEY		RED			GREEN
11
Foglio1

 

[PA HS Teacher]

Book1
	A	B	C	D
1	RecordName	A-04-008-VoorheesvilleWT	A-04-008-VoorheesvilleWT	A-04-008-VoorheesvilleWT
2	CASPRProjectNo.,SearchRingName,SiteType	A-04-008-VoorheesvilleWT	A-04-008	Colo
3	ProjectType	New	Build
4	Market	NewYork
5	Plan/Forecast/ActualDates	Plan	Forecast	Actual
6	MS000HandofftoCPM	2/4/2004	2/10/2004	2/4/2004
7	MS010SearchRingIssued	2/4/2004	2/10/2004	2/4/2004
Sheet2

Is the above what you would like the data to look like? If not, could you post an example of what you would like your data to look like when you're done?

 

[jOSEFFB]

For each record (those four rows) it should be rearranged to look like this.

The records can be as small as 1000 rows or as many as.... ???

HalfAce's original macro would be a good starting point because it normalizes the data, buit the end result is the following (mock data).

This is one record, so their would be many more of these repeating.

Every four rows of above example would become the following.

Book1
	A	B	C	D	E	F
2	Record Name	Market	MStitle1	Plan	Actual	Forecast
3	Record Name	Market	MStitle2	1/1/2004	1/1/2004	1/1/2004
4	Record Name	Market	MStitle3	1/2/2004	1/2/2004	1/2/2004
5	Record Name	Market	MStitle4	1/3/2004	1/3/2004	1/3/2004
6	Record Name	Market	MStitle5	1/4/2004	1/4/2004	1/4/2004
7	Record Name	Market	MStitle6	1/5/2004	1/5/2004	1/5/2004
8	Record Name	Market	MStitle7	1/6/2004	1/6/2004	1/6/2004
9	Record Name	Market	MStitle8	1/7/2004	1/7/2004	1/7/2004
10	Record Name	Market	MStitle9	1/8/2004	1/8/2004	1/8/2004
Sheet1

 

[jOSEFFB]

OK I've cleaned up my examples.
I've been playing around withit all weekend and I'm lost -- I'm not very good at excel based macros (obviously)



This is orignal spreadsheet sent to me where each record is in this format:.

CingularProjectCycleReport8.xls
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
5	RecordName	ProjectNo.,Name,SiteType	ProjectType	Market	Plan/Forecast/ActualDates	MS000HandofftoCPM	MS010SearchRingIssued	MS020PrimaryApproved	MS030LeaseFullyExecuted	MS036ZoningSubmitted	MS040ZoningApproved	MS075BPSubmitted	MS080BPReceived	MS093SiteAcq.Complete	MS095ConstructionStart	MS110SiteReadyForEquip.	MS120Equip.InstallComplete	MS125ConstructionComplete	MS145RFOpt.&Integrated(private)	MS150SiteOpt.&Integration(onair)	MS155TurnoverPackageComp.(BLIP)	MS160FinalAcceptance
6	Record1	Record1	New	NewYork	Plan	10/9/2003	10/9/2003	10/22/2003	11/7/2003	10/29/2003	11/28/2003	11/4/2003	11/14/2003	12/30/2003
7	Record1	6156	Build		Forecast	10/9/2003	10/9/2003	12/1/2003	7/8/2004	3/2/2004	4/23/2004	7/21/2004	8/31/2004	9/1/2004
8	Record1	None			Actual	10/9/2003	10/9/2003	12/1/2003	7/8/2004	3/2/2004	4/23/2004	7/30/2004	8/31/2004	9/1/2004
9	BerryRidge	Comments:Dual
DummyData

 

[jOSEFFB]

This is what I've been tasked to get it to look like:

#VALUE!

 

[chiello]

Ciao,
I'm interested in helping you, but for this I need further information. Can you post columns from A to D of 2 or 3 records in the original spreadsheet.
I understand that each original record is made by 4 rows: Column A of fourth row has different information ("Berry Ridge" vs Record1) and Column B starts with "Comment".

... Wait for response....

 

[jOSEFFB]

I renamed the projects inthe previous example for clarity. Here is an extract of the first 3 records.

Please note that C (c6:c8) and D (d6:d8) are merged.

C8.xls
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
5	RecordName	ProjectNo.,SearchRingName,SiteType	ProjectType	Market	Plan/Forecast/ActualDates	MS000HandofftoCPM	MS010SearchRingIssued	MS020PrimaryApproved	MS030LeaseFullyExecuted	MS036ZoningSubmitted	MS040ZoningApproved	MS075BPSubmitted	MS080BPReceived	MS093SiteAcq.Complete	MS095ConstructionStart	MS110SiteReadyForEquip.	MS120Equip.InstallComplete	MS125ConstructionComplete	MS145RFOpt.&Integrated(private)	MS150SiteOpt.&Integration(onair)	MS155TurnoverPackageComp.(BLIP)	MS160FinalAcceptance
6	BerryRidge	BerryRidge	New	Georgia	Plan	10/9/2003	10/9/2003	10/22/2003	11/7/2003	10/29/2003	11/28/2003	11/4/2003	11/14/2003	12/30/2003
7	BerryRidge	6156	Build		Forecast	10/9/2003	10/9/2003	12/1/2003	7/8/2004	3/2/2004	4/23/2004	7/21/2004	8/31/2004	9/1/2004
8	BerryRidge	Condo			Actual	10/9/2003	10/9/2003	12/1/2003	7/8/2004	3/2/2004	4/23/2004	7/30/2004	8/31/2004	9/1/2004
9	BerryRidge	Comments:
10
11	Bridgemill	Bridgemill	New	Georgia	Plan	10/3/2003	10/3/2003	10/16/2003	11/1/2003	10/23/2003	11/22/2003	10/29/2003	11/8/2003	12/24/2003	12/29/2003	1/3/2004	1/4/2004	1/8/2004	4/1/2004		1/17/2004	5/28/2004
12	Bridgemill	6157	Build		Forecast	10/3/2003	10/3/2003	10/30/2003	12/12/2003	1/28/2004	2/11/2004	2/12/2004	2/25/2004	1/20/2004	2/26/2004	3/1/2004	3/12/2004	3/12/2004	4/1/2004		9/29/2004	9/30/2004
13	Bridgemill	Condo			Actual	10/3/2003	10/3/2003	10/30/2003	12/12/2003	1/28/2004	2/11/2004	2/12/2004	2/25/2004	1/20/2004	2/26/2004	3/1/2004	3/12/2004	3/12/2004	4/1/2004		9/29/2004	9/30/2004
14	Bridgemill	Comments:
15
16	N.AycockSt	N.AycockSt	New	Georgia	Plan	10/3/2003	10/3/2003	10/16/2003	11/1/2003	10/23/2003	11/22/2003	10/29/2003	11/8/2003	12/24/2003	12/29/2003	1/3/2004	1/4/2004	1/8/2004	5/28/2004		1/17/2004	9/21/2004
17	N.AycockSt	6159	Build		Forecast	10/3/2003	10/3/2003	12/3/2003	2/13/2004	3/3/2004	3/12/2004	3/8/2004	3/15/2004	3/12/2004	4/5/2004	4/13/2004	5/26/2004	4/20/2004	5/28/2004		9/14/2004	9/21/2004
18	N.AycockSt	Condo			Actual	10/3/2003	10/3/2003	12/3/2003	2/13/2004	3/3/2004	3/12/2004	3/8/2004	3/15/2004	3/12/2004	4/5/2004	4/13/2004	5/26/2004	4/20/2004	5/28/2004		9/14/2004	9/21/2004
19	N.AycockSt	Comments:
20
C8

 

[chiello]

I have finished (hours of work) a code which should suit your request (teste on data you provided).

Post for feedback

Hypthesis: Source data in Sheet(1)
Destination in Sheet(2)

Code:

Sub Macro6()
'
' Macro6 Macro
' Macro registrata il 24/01/2005 da FSC
'

'
Dim Sheet1LastRow As Long, Sheet2RowPointer As Long, PointerIncr As Long
Dim AllRange As Range, OneCopyRange As Range
Dim CELLi As Range, RangeToCopy As Range
Dim CopyColFrom As Long, CopyColTo As Long
Dim StrMarking As String

Application.ScreenUpdating = False

'Sheets 2 cleaning
Sheets(2).Cells.Delete

On Error Resume Next
Sheets(1).ShowAllData
On Error GoTo 0

'Last row index acquisition
Sheet1LastRow = Sheets(1).Cells(Cells.Rows.Count, 1).End(xlUp).Row
'Initialization of Pointer to first blank row in Sheet 2
Sheet2RowPointer = 1

'Set Columns Number to copy
CopyColFrom = 5 'First
CopyColTo = Sheets(1).Range("A1").End(xlToRight).Column 'Last

'Data range acquisition
Set AllRange = Sheets(1).Range(Sheets(1).Range("A1"), Sheets(1).Cells(Sheet1LastRow, CopyColTo))

'Acquisition of all different records
AllRange.Columns(1).AdvancedFilter Action:=xlFilterInPlace, Unique:=True
Set OneCopyRange = AllRange.Columns(1).SpecialCells(xlCellTypeVisible)

'Momently clear column A cells relevant to Comments
For PointerIncr = 5 To Sheet1LastRow Step 5
Sheets(1).Cells(PointerIncr, 1).ClearContents
Next PointerIncr

'Setting of PointerIncr to be used after the copy procedure
PointerIncr = CopyColTo - CopyColFrom + 1

'Filter all different records
AllRange.AutoFilter
For Each CELLi In OneCopyRange
Sheets(1).Activate
'Skip A1 and empty cells
If CELLi.Row <> 1 And Trim(CELLi) <> "" Then
AllRange.AutoFilter Field:=1, Criteria1:=CELLi

'Acquisition of Marking String Data
StrMarking = AllRange.Columns(4).SpecialCells(xlCellTypeVisible).Cells(2, 1)

'Range to copy acquisition
Set RangeToCopy = _
Range(AllRange.Columns(CopyColFrom), AllRange.Columns(CopyColTo)).SpecialCells(xlCellTypeVisible)

'Copy data
RangeToCopy.Copy
Sheets(2).Activate
Sheets(2).Cells(Sheet2RowPointer, 3).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=True
Sheets(1).Range("A1").Copy Destination:=Sheets(2).Cells(Sheet2RowPointer, 1)
Sheets(1).Range("A2").Copy
Sheets(2).Range(Cells(Sheet2RowPointer + 1, 1), Cells(Sheet2RowPointer + PointerIncr - 1, 2)).PasteSpecial _
Paste:=xlPasteFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Sheets(2).Range(Cells(Sheet2RowPointer + 1, 1), Cells(Sheet2RowPointer + PointerIncr - 1, 1)) = CELLi
Sheets(1).Range("D1").Copy Destination:=Sheets(2).Cells(Sheet2RowPointer, 2)
Sheets(2).Range(Cells(Sheet2RowPointer + 1, 2), Cells(Sheet2RowPointer + PointerIncr - 1, 2)) = StrMarking
'Increment of the pointer for next copy
Sheet2RowPointer = PointerIncr + Sheet2RowPointer
End If
Next CELLi

'Columns Width Adjustment (Autofit)
With Sheets(2).Columns("A:F")
.ColumnWidth = 100
.AutoFit
.Rows.AutoFit
End With

Sheets(1).Activate
'Clear Filter
Sheets(1).ShowAllData
'Restore column A cells relevant to Comments
For PointerIncr = 5 To Sheet1LastRow Step 5
Sheets(1).Cells(PointerIncr, 1) = Sheets(1).Cells(PointerIncr - 1, 1)
Next PointerIncr
Sheets(2).Activate
Application.ScreenUpdating = True
End Sub