CODEWITHSUNDEEP
excelexcel-formulaexcel-2010vba
Tanvir Sourov
Tanvir Sourov

Reputation: 417

Excel Duplicate Rows depending on Columns Value

In my excel file, in the first column ProductName, I have my list of products, for each of which there can be different sizes: VerySmall, Small, Large, etc. Each Size has its own price for each product. I am trying to list down vertically each available size for each product and its corresponding price.
Here is the original data structure:

The Excel File that is currently available:


And this is what it will look like as my result:

How the Excel File will be after Conversion:

I have around 8000+ Products like this. I have tried to find the solution of this problem in the past few days, but I have completely failed. Is there any way to pull the sizes and prices and list them vertically?

Upvotes: 1

Views: 106

Answers (1)

Uri Goren
Uri Goren

Reputation: 13690

Assuming that the data is on Sheet1 and that Sheet2 is empty, write this code as a VBA macro (ALT+F11) and run it (F5)

Sub Flatten()
  Dim row as Integer,col as Integer, dst_row as Integer,dst_col as Integer
  Dim col_start as Integer,col_end as Integer
  'Your parameters:
  col_start=3'C column
  col_end=12'L column
  row=2'Data starts from second row
  dst_row=row
  'Copy the headers
  For col=1 To col_start+2
    Sheet2.Cells(1,col)=Sheet1.Cells(1,col)
  Next col
  'Flat the Table
  While Sheet1.Cells(row,col_start)<>""
    For col=col_start To col_end Step 2
      If Sheet1.Cells(row,col)<>"" Then
        For dst_col=1 To col_start
          Sheet2.Cells(dst_row,dst_col)=Sheet1.Cells(row,dst_col)
        Next dst_col
        Sheet2.Cells(dst_row,col_start)=Sheet1.Cells(row,col)
        Sheet2.Cells(dst_row,col_start+1)=Sheet1.Cells(row,col+1)
        dst_row=dst_row+1
      End If
    Next col
    row=row+1
    dst_row=dst_row+1
  Wend
End Sub

Upvotes: 1

Related Questions