I have 5000+ records in Column A. Each record contains a number of rows of data. The number of rows containing data varies from record to record. How do I transpose the data in these records so that each record occupies only one row? See included image. (using Excel included in Microsoft 365)
IFERROR & INDEX command, and few helper value solves the issue.
- Shown method works with any version of Excel.
- It’s not an smart method but solves the issue.
- To improvise this exercise, readers may suggest
an efficient Formula/Macro.
How it works:
1. Insert series of numbers (
I93:M93) based on maximum elements per record set, here maximum is
2. Enter another series of numbers (
H94:H96) based on record set, here are
3. Formula in cell
4, Fill it across.
Adjust cell references in the formula as needed.
Since OP is handling huge data set (around 5000 records) therefore VBA Macro can a smart way to handle the issue.
Sub TransposeWithBlanks() Dim Data_Array Dim OutPut_Array() Dim LR As Long, Counter As Long, LR2 As Long Dim i As Long Application.ScreenUpdating = False With Sheets("Sheet1") LR = .Cells(Rows.Count, "A").End(xlUp).Row + 1 Data_Array = .Range("A1:A" & LR).Value2 End With On Error Resume Next For i = LBound(Data_Array, 1) To UBound(Data_Array, 1) If Trim(Data_Array(i, 1)) <> vbNullString Then Counter = Counter + 1 ReDim Preserve OutPut_Array(1 To 1, 1 To Counter) OutPut_Array(1, Counter) = Data_Array(i, 1) Else With Sheets("Sheet1") LR2 = .Cells(Rows.Count, "C").End(xlUp).Row .Range("C" & LR2 + 1).Resize(1, Counter).Value2 = OutPut_Array End With Counter = 0 End If Next i End Sub
- Save the workbook as Macro Enabled.
- In Above code Sheet name and data range are editable.
If you want to Paste the Transposed data in another Sheet, then you need to edit this portion of the code.
With Sheets("Sheet1") LR2 = .Cells(Rows.Count, "C").End(xlUp).Row .Range("C" & LR2 + 1).Resize(1, Counter).Value2 = OutPut_Array End With
that is very kind of you to look at this challenge. While your formula works, it expects to be told the cell range for each individual record ($I$84:$I$86 for Record 2…then $I$88:$I$92 for Record 3… and so on. With 5000 records, one would need to input the ranges of each of the 5000 individual records – which is not really practical.
I am not an excel guru, so please excuse my following ‘layman’s’ thoughts.
The formula (or more likely macro?) that I think would work is as follows (using your table above):
1) Instruct the macro to search downwards in column I for the word (string?) “Record”.
2) Each time the macro finds the word “Record” instruct it to copy/paste the data in the subsequent cells immediately below, horizontally into the adjacent cells (to its right) – and stop the copy/paste when it reaches the next cell down with another word “record”.
As I said, I don’t know how to ‘program’ formulas or macros in excel – so my suggestion may be totally not possible.
Anyhow, regardless, you have been very kind to look at this. I hope someone has come across this problem before, and has a workable answer.
Yours kindly, Sean