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

Question

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 1

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

Answer 2

Here is a worksheet function solution. The formulas are a bit dense, so be warned, but this will give what you want.

Steps:

1. In the first row of your new table, under Name, enter a direct reference to the first Name in your data. In your example, you would enter =A2 where A2 is the first name listed in your data. In the example screenshot I've provided below, this formula goes in A8. All following formulas will follow the layout used in the screenshot. You will of course have to update all range references to match your sheet(s).
2. In the cell below this, enter the following formula:

   =IF(COUNTIF($A$9:A9,A9)=COUNTA(OFFSET($C$1:$E$1,MATCH(A9,$A$2:$A$5,0),0)),INDEX($A$2:$A$5,MATCH(A9,$A$2:$A$5,0)+1),A9)
   

   This basically checks how many rows there should be for the name listed above (in A9), and if the number of rows already in your new table matches this, then it moves on to the next name. If not, another row for the name above will be added.
   Fill this formula down as far as you need to (until it returns a 0 instead of a name).
3. In the first row under Description enter the following formula and fill down.

   =INDEX($B$2:$B$5,MATCH(A9,$A$2:$A$5,0))

4. In the first row under SKU, paste the following formula into the formula bar and press Ctrl+Shift+Enter.

   =INDEX(OFFSET($A$1:$H$1,MATCH(A9,$A$2:$A$5,0),0),SMALL(IF(OFFSET($F$1:$H$1,MATCH(A9,$A$2:$A$5,0),0)<>"",COLUMN($F$1:$H$1)),COUNTIF($A$9:$A9,$A9)))

   This is an array formula; if entered correctly the formula will appear in the formula bar enclosed in curly brackets. Fill this formula down your table (each instance should likewise appear in curly brackets).
5. Similarly, in the first row under Price, paste the following formula into the formula bar and enter it as an array formula (by pressing Ctrl+Shift+Enter).

   =INDEX(OFFSET($A$1:$H$1,MATCH($A9,$A$2:$A$5,0),0),SMALL(IF(OFFSET($C$1:$E$1,MATCH($A9,$A$2:$A$5,0),0)<>"",COLUMN($C$1:$E$1)),COUNTIF($A$9:$A9,$A9)))

   Fill down, and this should complete your table.