Transposing data with Index function

[Libby]

Hi,

I've been using this function to transpose records from a single column (A)
to several columns:
=INDEX($A:$A,(ROWS($1:1)-1)*6+COLUMNS($A:B)-1)

All the records were 5 lines with one blank separating each record. The new
file I've received has records that range from 4 lines to 6 lines with a
blank separating each one. How do I work with records of differing sizes?
Do I need a macro now?

Libby

 

[ryguy7272]

You need one heck of a function, or code. I don't know the specifics of your
project, but you can try this:


Sub CombineRows()

LastRow = Range("A" & Rows.Count).End(xlUp).Row
'set rowcount to row where you want 1st entry
RowCount = 1
NewRow = RowCount
Start = False
Do While RowCount <= LastRow
If Start = False Then
If Range("A" & RowCount) <> "" Then
Start = True
StartRow = RowCount
End If

Else
If Range("A" & (RowCount + 1)) = "" Then
ColCount = 1
For MoveRow = StartRow To RowCount
Cells(NewRow, ColCount) = Cells(MoveRow, "A")
ColCount = ColCount + 1
Next MoveRow
NewRow = NewRow + 1
Start = False
End If
End If
RowCount = RowCount + 1
Loop

Rows(NewRow & ":" & LastRow).Delete

End Sub

NOTICE!! MAKE A BACKUP AND RUN THE CODE ON YOUR BACKUP, IN CASE IT DOESN'T
DO WHAT YOU WANT IT TO DO!!

HTH,
Ryan---

 

[Libby]

Well, ryguy7272, that macro is one big bag of SWEET!!!

You will live in my heart forever

Libby

 

[ryguy7272]

Lucky guess, I guess. Go ahead and click 'Yes' if the solution helped.

Thanks,
Ryan---