How can I transpose each 'group'?

141240009-1 2006
141240009-1 2008
141240009-1 2009

141361014-9 2009

142021037-6 2006

142022037-9 2007
142022037-9 2008
142022037-9 2009

142090044-6 2008
142090044-6 2009

142091016-4 2008

142091017-5 2008

142092012-3 2007
142092012-3 2008
142092012-3 2009

142202013-3 2007
142202013-3 2008

142273014-4 2007
142273014-4 2008

142382016-3 2008

142401016-1 2004
142401016-1 2005
142401016-1 2007
142401016-1 2008

142411026-1 2007
142411026-1 2008
142411026-1 2009

(The column has 11,810 cells in 4,800 'groups')

Can you clarify what a group is to you and what you want it to look like
after it is transposed? The problem I am having with you example is it looks
like two columns of information of varying length, so I am not sure how it
is to look once transposed. Can you show us the output you want using the
first 4 groups?

Rick Rothstein (MVP - Excel)




"gcotterl" wrote in message
...

How can I transpose each 'group'?

141240009-1 2006
141240009-1 2008
141240009-1 2009

141361014-9 2009

142021037-6 2006

142022037-9 2007
142022037-9 2008
142022037-9 2009

142090044-6 2008
142090044-6 2009

142091016-4 2008

142091017-5 2008

142092012-3 2007
142092012-3 2008
142092012-3 2009

142202013-3 2007
142202013-3 2008

142273014-4 2007
142273014-4 2008

142382016-3 2008

142401016-1 2004
142401016-1 2005
142401016-1 2007
142401016-1 2008

142411026-1 2007
142411026-1 2008
142411026-1 2009

(The column has 11,810 cells in 4,800 'groups')

On Feb 6, 6:11*pm, "Rick Rothstein"
wrote:
Can you clarify what a group is to you and what you want it to look like
after it is transposed? The problem I am having with you example is it looks
like two columns of information of varying length, so I am not sure how it
is to look once transposed. Can you show us the output you want using the
first 4 groups?

Rick Rothstein (MVP - Excel)
===============================================

Each has cell in 16 characters.
A 'group' has the same 11 leftmost characters.


Here's what I'm looking for:

141240009-1 2006 141240009-1 2008 141240009-1 2009
141361014-9 2009
142021037-6 2006
142022037-9 2007 142022037-9 2008 142022037-9 2009
142401016-1 2004 142401016-1 2005 142401016-1 2007 142401016-1 2008

On Feb 6, 9:30*pm, gcotterl wrote:
On Feb 6, 6:11*pm, "Rick wrote:
Can you clarify what a group is to you and what you want it to look like
after it is transposed? The problem I am having with you example is it looks
like two columns of information of varying length, so I am not sure how it
is to look once transposed. Can you show us the output you want using the
first 4 groups?

Rick Rothstein (MVP - Excel)

===============================================

Each has cell in 16 characters.
A 'group' has the same 11 leftmost characters.

Here's what I'm looking for:

141240009-1 2006 *141240009-1 2008 *141240009-1 2009
141361014-9 2009
142021037-6 2006
142022037-9 2007 *142022037-9 2008 *142022037-9 2009
142401016-1 2004 *142401016-1 2005 *142401016-1 2007 142401016-1 2008

Here is a smiple vba to do the job:

Sub trans()
Dim s As Range, t As Range
Dim ThereIsMoreRegion As Boolean

Set s = [a1] ' beginning address of source data
Set t = [b1] ' beginning address of target data

ThereIsMoreRegion = True

While ThereIsMoreRegion
rc = s.CurrentRegion.Rows.Count ' row count

t.Resize(1, rc) = WorksheetFunction.Transpose(s.CurrentRegion)
Set t = t.Offset(1)
Set s = s.Offset(rc).End(xlDown)
ThereIsMoreRegion = Not IsEmpty(s.Value)
Wend
End Sub


/reza

On Feb 6, 8:32*pm, Nick Egac wrote:
On Feb 6, 9:30*pm, gcotterl wrote:





On Feb 6, 6:11*pm, "Rick wrote:
Can you clarify what a group is to you and what you want it to look like
after it is transposed? The problem I am having with you example is it looks
like two columns of information of varying length, so I am not sure how it
is to look once transposed. Can you show us the output you want using the
first 4 groups?

Rick Rothstein (MVP - Excel)

===============================================

Each has cell in 16 characters.
A 'group' has the same 11 leftmost characters.

Here's what I'm looking for:

141240009-1 2006 *141240009-1 2008 *141240009-1 2009
141361014-9 2009
142021037-6 2006
142022037-9 2007 *142022037-9 2008 *142022037-9 2009
142401016-1 2004 *142401016-1 2005 *142401016-1 2007 142401016-1 2008

Here is a smiple vba to do the job:

Sub trans()
* * Dim s As Range, t As Range
* * Dim ThereIsMoreRegion As Boolean

* * Set s = [a1] *' beginning address of source data
* * Set t = [b1] *' beginning address of target data

* * ThereIsMoreRegion = True

* * While ThereIsMoreRegion
* * * * rc = s.CurrentRegion.Rows.Count *' row count

* * * * t.Resize(1, rc) = WorksheetFunction.Transpose(s.CurrentRegion)
* * * * Set t = t.Offset(1)
* * * * Set s = s.Offset(rc).End(xlDown)
* * * * ThereIsMoreRegion = Not IsEmpty(s.Value)
* * Wend
End Sub

/reza- Hide quoted text -

- Show quoted text -

All transposed cells are in A1 thru QLL1 which is not what I what I
was looking for (see above example).

On Feb 6, 8:32*pm, Nick Egac wrote:
On Feb 6, 9:30*pm, gcotterl wrote:





On Feb 6, 6:11*pm, "Rick wrote:
Can you clarify what a group is to you and what you want it to look like
after it is transposed? The problem I am having with you example is it looks
like two columns of information of varying length, so I am not sure how it
is to look once transposed. Can you show us the output you want using the
first 4 groups?

Rick Rothstein (MVP - Excel)

===============================================

Each has cell in 16 characters.
A 'group' has the same 11 leftmost characters.

Here's what I'm looking for:

141240009-1 2006 *141240009-1 2008 *141240009-1 2009
141361014-9 2009
142021037-6 2006
142022037-9 2007 *142022037-9 2008 *142022037-9 2009
142401016-1 2004 *142401016-1 2005 *142401016-1 2007 142401016-1 2008

Here is a smiple vba to do the job:

Sub trans()
* * Dim s As Range, t As Range
* * Dim ThereIsMoreRegion As Boolean

* * Set s = [a1] *' beginning address of source data
* * Set t = [b1] *' beginning address of target data

* * ThereIsMoreRegion = True

* * While ThereIsMoreRegion
* * * * rc = s.CurrentRegion.Rows.Count *' row count

* * * * t.Resize(1, rc) = WorksheetFunction.Transpose(s.CurrentRegion)
* * * * Set t = t.Offset(1)
* * * * Set s = s.Offset(rc).End(xlDown)
* * * * ThereIsMoreRegion = Not IsEmpty(s.Value)
* * Wend
End Sub

/reza- Hide quoted text -

- Show quoted text -

All transposed cells are in A1 thru QLL1 which is not what I was
looking for (see above example).

Each has cell in 16 characters.
A 'group' has the same 11 leftmost characters.

Here's what I'm looking for:

141240009-1 2006 141240009-1 2008 141240009-1 2009
141361014-9 2009
<<<snip

Give this macro a try....

Sub TransposeGroups()
Dim A As Range, D As Range, StartCell As Range, LastCell As Range
Dim Index As Long, Data() As String
Const DataCol As String = "A"
Const StartRow As Long = 1
Set StartCell = Cells(StartRow, DataCol)
Set LastCell = Cells(Rows.Count, DataCol).End(xlUp)
Set D = Range(StartCell, LastCell).SpecialCells(xlCellTypeConstants)
ReDim Data(1 To D.Count)
For Each A In D.Areas
Index = Index + 1
If A.Count = 1 Then
Data(Index) = A
Else
Data(Index) = Join(WorksheetFunction.Transpose(A), "|")
End If
Next
Application.ScreenUpdating = False
Columns(DataCol).Clear
Range(StartCell, LastCell).Resize(UBound(Data)).Value = _
WorksheetFunction.Transpose(Data)
Range(StartCell, LastCell).TextToColumns StartCell, _
xlDelimited, Tab:=False, Space:=False, _
Other:=True, OtherChar:="|"
Application.ScreenUpdating = True
End Sub


Rick Rothstein (MVP - Excel)

On Feb 6, 10:05*pm, "Rick Rothstein"
wrote:
Each has cell in 16 characters.
A 'group' has the same 11 leftmost characters.

Here's what I'm looking for:

141240009-1 2006 *141240009-1 2008 *141240009-1 2009
141361014-9 2009
* * <<<snip

Give this macro a try....

Sub TransposeGroups()
* Dim A As Range, D As Range, StartCell As Range, LastCell As Range
* Dim Index As Long, Data() As String
* Const DataCol As String = "A"
* Const StartRow As Long = 1
* Set StartCell = Cells(StartRow, DataCol)
* Set LastCell = Cells(Rows.Count, DataCol).End(xlUp)
* Set D = Range(StartCell, LastCell).SpecialCells(xlCellTypeConstants)
* ReDim Data(1 To D.Count)
* For Each A In D.Areas
* * Index = Index + 1
* * If A.Count = 1 Then
* * * Data(Index) = A
* * Else
* * * Data(Index) = Join(WorksheetFunction.Transpose(A), "|")
* * End If
* Next
* Application.ScreenUpdating = False
* Columns(DataCol).Clear
* Range(StartCell, LastCell).Resize(UBound(Data)).Value = _
* * * * * * * * * * * WorksheetFunction.Transpose(Data)
* Range(StartCell, LastCell).TextToColumns StartCell, _
* * * * * * * * * *xlDelimited, Tab:=False, Space:=False, _
* * * * * * * * * *Other:=True, OtherChar:="|"
* Application.ScreenUpdating = True
End Sub

Rick Rothstein (MVP - Excel)

Run-time Error '1004'
Unable to get the Transpose property of the WorksheetFunction class

Run-time Error '1004'
Unable to get the Transpose property of the
WorksheetFunction class

Let me start by saying the code I posted does work because I tested it here
before posting it. One possible problem could be your data is in a different
location than I assumed. At the top of my code are two statements that start
with the VB keyword Const (this stands for constant) that need to be set to
your actual situation... The DataCol needs to be assigned the letter for the
column with your data (I assumed Column A) and the StartRow needs to be set
to the row number of the first piece of data in the DataCol column (I
assumed Row 1). Now, if these settings are not at the heart of your problem,
then I would like you to send me your workbook so I can see first hand what
is going on (otherwise all I can do is guess and wait for you to tell me if
my guess worked or not... that could be a lengthy process that I really do
not want to get involved in... plus the problem could be something I never
end up thinking of). Send it to me at rickDOTnewsATverizonDOTnet (just
replace the upper case letters with the words they spell out).

Rick Rothstein (MVP - Excel)

On Feb 6, 11:55*pm, "Rick Rothstein"
wrote:
Run-time Error '1004'
Unable to get the Transpose property of the
WorksheetFunction class

Let me start by saying the code I posted does work because I tested it here
before posting it. One possible problem could be your data is in a different
location than I assumed. At the top of my code are two statements that start
with the VB keyword Const (this stands for constant) that need to be set to
your actual situation... The DataCol needs to be assigned the letter for the
column with your data (I assumed Column A) and the StartRow needs to be set
to the row number of the first piece of data in the DataCol column (I
assumed Row 1). Now, if these settings are not at the heart of your problem,
then I would like you to send me your workbook so I can see first hand what
is going on (otherwise all I can do is guess and wait for you to tell me if
my guess worked or not... that could be a lengthy process that I really do
not want to get involved in... plus the problem could be something I never
end up thinking of). Send it to me at rickDOTnewsATverizonDOTnet (just
replace the upper case letters with the words they spell out).

Rick Rothstein (MVP - Excel)

I've e-mailed my spreadsheet to you.

To those following this thread...

I looked at the worksheet that gcotterl sent me and discovered the problem.
He seemed to indicate (in his first message and his clarification to me)
that his data was laid out like this...

141240009-1 2006
141240009-1 2008
141240009-1 2009

141361014-9 2009

142021037-6 2006

142022037-9 2007
142022037-9 2008
142022037-9 2009

However, his file does not have blank rows separating the "group"; rather,
it looks like this...

141240009-1 2006
141240009-1 2008
141240009-1 2009
141361014-9 2009
142021037-6 2006
142022037-9 2007
142022037-9 2008
142022037-9 2009

He put the blank rows in to try and show us what a group looked like. Given
that, here is the code I sent back to him...

'******************* START OF CODE ********************
Sub TransposeGroups()
Dim X As Long, Z As Long, StartAt As Long
Dim StartRow As Long, LastRow As Long, CellCount As Long
Dim CellText As String, Data() As String
Const DataCol As Long = 1 'This is Column A
StartRow = 1
StartAt = StartRow
LastRow = Cells(Rows.Count, DataCol).End(xlUp).Row
CellText = Left(Cells(StartRow, DataCol).Value, 11)
Application.ScreenUpdating = False
For X = StartAt To LastRow
If Left(Cells(X + 1, DataCol).Value, 11) < CellText Then
Z = Z + 1
CellCount = X - StartRow + 1
If CellCount = 1 Then
Cells(Z, DataCol + 1).Value = Cells(X, DataCol).Value
Else
Cells(Z, DataCol + 1).Value = Join(WorksheetFunction.Transpose( _
Cells(StartRow, DataCol).Resize(CellCount)), "|")
End If
CellText = Left(Cells(X + 1, DataCol).Value, 11)
StartRow = X + 1
End If
Next
Columns(DataCol).Delete
Columns(DataCol).TextToColumns Cells(StartAt, DataCol), xlDelimited, _
Tab:=False, Space:=False, Other:=True, OtherChar:="|"
Application.ScreenUpdating = True
End Sub
'******************* END OF CODE ********************

Rick Rothstein (MVP - Excel)




"gcotterl" wrote in message
...

On Feb 6, 11:55 pm, "Rick Rothstein"
wrote:
Run-time Error '1004'
Unable to get the Transpose property of the
WorksheetFunction class

Let me start by saying the code I posted does work because I tested it
here
before posting it. One possible problem could be your data is in a
different
location than I assumed. At the top of my code are two statements that
start
with the VB keyword Const (this stands for constant) that need to be set
to
your actual situation... The DataCol needs to be assigned the letter for
the
column with your data (I assumed Column A) and the StartRow needs to be
set
to the row number of the first piece of data in the DataCol column (I
assumed Row 1). Now, if these settings are not at the heart of your
problem,
then I would like you to send me your workbook so I can see first hand
what
is going on (otherwise all I can do is guess and wait for you to tell me
if
my guess worked or not... that could be a lengthy process that I really do
not want to get involved in... plus the problem could be something I never
end up thinking of). Send it to me at rickDOTnewsATverizonDOTnet (just
replace the upper case letters with the words they spell out).

Rick Rothstein (MVP - Excel)

I've e-mailed my spreadsheet to you.