CODEWITHSUNDEEP
excelvba
Greg Herr
Greg Herr

Reputation: 37

Excel: Transpose stacked data in one column to multiple rows based on a consistent cell value

I am trying to transpose a single column into multiple rows. The row break should be based on the row following a cell containing "Contact buyer". Below is an example of the data:

jeneice07 on 13 May, 2019

5 out of 5 stars

 Report this review

 Contact buyer

Tree of Life Stone Necklace Wire Wrapped Crystal Pendant Stone Womens Jewellery Nature Jewelry Gifts for Her Green Gem Copper Tree Design

The number of rows in each review is variable or this would be a simple problem to solve. I need code that will look for "Contact buyer" then go one additional cell and transpose that then transpose the next review another row down. I can change the cells containing contact buyer to any value that may be required to make this easier than it being a string.

I searched heavily for any solution to this issue. I have over 100000 rows of data so doing this manually isn't an option. I tried modifying the code below but it doesn't work because I cannot get the stepVal to be variable.

Public Sub TransposePaste()
   'Value we want to step by....
    Dim stepVal As Long
    stepVal = "
'Declare start row variable (-1)
 Dim row As Long
 row = 0

'Declare the column the data is coming from
Dim col As Long
col = 1

'Declare and set worksheet
Dim ws As Worksheet
Set ws = Sheet1

'end row of the data
Dim endRow As Long 
endRow = ws.Cells(ws.Rows.Count, col).End(xlUp).row

'cycle to end of data end of data...
For i = 1 To endRow Step stepVal

'increment row each time
row = row + 1

    'cycle through the columns outputting the data
    For j = 1 To stepVal

    Sheet1.Cells(row, j).Value = Sheet1.Cells(i + j - 1, col).Value

    Next j
Next i
End Sub

Input data looks like this. It is 10K rows of user comments

Output example below taken from first two entries of the input data and I added one additional item where the reviewer added a photo which results in the review being seven cells long instead of five:

Output Example

Upvotes: 0

Views: 340

Answers (1)

Greg Herr
Greg Herr

Reputation: 37

Disregard. I am just going to manually scrub the data to get the length to be a consistent 5 rows then use the code above.

Upvotes: 0

Related Questions