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).