How do I split one row into multiple rows with Excel?

I have a product database in Excel with several hundred entries, each of which has from 1 to 3 "tiers" of pricing: Standard, Deluxe, and Premium. Each tier has its own SKU (A, B, or C added on to the end of the base SKU) and price. My data is like this:

Name, Description, Price A, Price B, Price C, SKU A, SKU B, SKU C
name1,      desc1,   14.95,   19.95,        , sku1A, sku1B, 
name2,      desc2,    4.95,    9.95,   12.95, sku2A, sku2B, sku2C
name3,      desc3,   49.95,        ,        , sku3A,      ,

How would I go about getting the data to look like this:

Name, Description,   SKU, Price
name1,      desc1, sku1A, 14.95
name1,      desc1, sku1B, 19.95
name2,      desc2, sku2A,  4.95
name2,      desc2, sku2B,  9.95
name2,      desc2, sku2C, 12.95
name3,      desc3, sku3A, 49.95

If it helps, I'm going to be importing these products into a Magento installation.

Thank you in advanced.

Answer

Those tasks are usually faster with VBA. In fact, it took me ~10 minutes to set it up.
I'm assuming your data is in column A to column H.

Go to Excel » Developer » Visual Basic » On the left pane open sheet1 (or) the sheet where your data resides » Insert the code at the right window » Run the code

VBA code

1 |Sub NewLayout()
2 |    For i = 2 To Cells.Find("*", [A1], , , xlByRows, xlPrevious).Row
3 |        For j = 0 To 2
4 |        If Cells(i, 3 + j) <> vbNullString Then
5 |            intCount = intCount + 1
6 |            Cells(i, 1).Copy Destination:=Cells(intCount, 10)
7 |            Cells(i, 2).Copy Destination:=Cells(intCount, 11)
8 |            Cells(i, 3 + j).Copy Destination:=Cells(intCount, 12)
9 |            Cells(i, 6 + j).Copy Destination:=Cells(intCount, 13)
10|        End If
11|        Next j
12|    Next i
13|End Sub

Explanation

It was my intention to keep the code as short as possible to explain it better. Basically we use two loops. The outer loop (i) is for the rows and the inner loop (j) for the price columns.

We heavily use cells(rowNumber,columnNumber) to read/write cells.

• 
  

  Line 2| Start a loop from row 2 to your last row. We iterate through every used row

  
  


• 
  

  Line 3| Start a second loop from 0 to 2 (that are actually 3 loops, one for every Price column)

  
  


• 
  

  Line 4| We use this inner loop to check for values in our current row and column Price A, then Price B and in the last loop Price C. If we find a value in a Price column, we go on and copy cells. If no Price is inserted, we do nothing and go on to the next Price column

  
  


• 
  

  Line 5| Count up a counter to know how many rows we already copied,
  so we know after what row we can copy our current row

  
  


• 
  

  Line 6| Copy the name column

  
  


• 
  

  Line 7| Copy the description column

  
  


• 
  

  Line 8| Copy the Price A or B or C column depending on what inner loop we currently are

  
  


• 
  

  Line 9| Copy the SKU A or B or C column depending on what inner loop we currently are

  
  

Result screenshot