About Us

JR Form[_2_]

thanks SA
JR

"StumpedAgain" wrote:

I inquired about what was wrong with my macro but got a different solution
instead.

From Dave Peterson:

Option Explicit
Sub testme()

Dim CurWks As Worksheet
Dim NewWks As Worksheet
Dim FirstRow As Long
Dim oRow As Long
Dim BigRng As Range
Dim SmallArea As Range
Dim NextGroupMustBeFirstCategory As Boolean
Dim RngToCopy As Range
Dim oCol As Long
Dim LinesPerGroup As Long

Set CurWks = Worksheets("Sheet1")
Set NewWks = Worksheets.Add

With CurWks
FirstRow = 1
Set BigRng = .Range(.Cells(FirstRow, "A"), _
.Cells(.Rows.Count, "A").End(xlUp)) _
.Cells.SpecialCells(xlCellTypeConstants)
oRow = 0
LinesPerGroup = 1
For Each SmallArea In BigRng.Areas
If LCase(SmallArea.Cells(1, 1).Value) Like LCase("Date:*") Then
'This is the Date/name/shift group
'Start of a new group.
oRow = oRow + LinesPerGroup
NewWks.Cells(oRow, "A").Resize(1, 5).Value _
= Array("Date", "Name", "Shift", "Category A", "Category
B")

oRow = oRow + 1
'remove "Date: "
NewWks.Cells(oRow, "A").Value _
= Trim(Mid(SmallArea.Cells(1, 1).Value, 6))

'remove "Name: "
NewWks.Cells(oRow, "B").Value _
= Trim(Mid(SmallArea.Cells(2, 1).Value, 6))

'remove "Shift: "
NewWks.Cells(oRow, "C").Value _
= Trim(Mid(SmallArea.Cells(3, 1).Value, 7))

NextGroupMustBeFirstCategory = True
LinesPerGroup = 3
Else
'This is the category A or Category B section.
With SmallArea
If .Cells.Count LinesPerGroup Then
LinesPerGroup = .Cells.Count
End If
Set RngToCopy = .Resize(.Rows.Count - 1, 1).Offset(1, 0)
End With
If NextGroupMustBeFirstCategory Then
oCol = 4 'column D
'get ready for the category B group
NextGroupMustBeFirstCategory = False
Else
oCol = 5 'column E
End If

RngToCopy.Copy
NewWks.Cells(oRow, oCol).PasteSpecial

End If
Next SmallArea

End With

Application.CutCopyMode = False
NewWks.UsedRange.Columns.AutoFit

End Sub

Seems to work well for what I have set up. Hope it helps!

"karim" wrote:

Hi,
I have 30,000 rows of data (exported from a text file) in the following
manner:
Date: xxx
Name:xxx
Shift:xxx

Category A
line 1
line 2
line 3
etc...

Category B
line 1
line 2
line 3
etc..

and I want to convert the data into columns:

Date Name Shift Category A Category B
line 1
line 2
line 3

Any suggestions on how to do that?

Thanks in advance
Karim

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Switch to Linear Mode Switch to Hybrid Mode Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Transposing contents of a cell	Fran[_2_]	Excel Discussion (Misc queries)	6	January 16th 09 09:18 AM
Transposing Multiple Cell References to Multiple Values (NOT total	LinLin	Excel Discussion (Misc queries)	7	November 11th 07 10:57 PM
Transposing Multiple Cell references as Multiple Values	LinLin	Excel Discussion (Misc queries)	1	November 8th 07 01:21 AM
Transposing	Deena	Excel Worksheet Functions	2	October 27th 06 05:13 PM
Deleting Rows based on text in cell & formatting cell based on text in column beside it	Steve	Excel Programming	4	February 26th 04 03:31 PM

Menu

About Us