Transposing data with Index function
Lucky guess, I guess. Go ahead and click 'Yes' if the solution helped.
Thanks,
Ryan---
--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.
"Libby" wrote:
Well, ryguy7272, that macro is one big bag of SWEET!!!
You will live in my heart forever :)
Libby
"ryguy7272" wrote:
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---
--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.
"Libby" wrote:
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
|