9

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.

GreysonD
  • 93
  • Is there a possibility that there are several similar names and descriptions? E.g. Two lines have name1 and desc1 but with different prices for Price A, Price B, Price C, etc. – Jerry Aug 17 '13 at 19:40
  • In the original data? No, I'm sure they are unique. But, in the new data there will definitely be repeats. – GreysonD Aug 17 '13 at 19:45
  • Okay, I had something in mind, but turned out it won't be working unless there's more manipulation behind to correct some results. If you're curious, I was trying a Consolidated PivotTable, but the Description field and the SKU fields would have to be pulled separately using index/match/vlookup, which makes it quite long at the end. – Jerry Aug 17 '13 at 20:33

2 Answers2

8

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

enter image description here

nixda
  • 27,268
  • 1
    This is fantastic! Exactly what I need. My data is actually a bit more complex than my example, and I'm new to VBA, so your explanation helps tremendously. Thanks! – GreysonD Aug 17 '13 at 22:14
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.

Screenshot of table

Excellll
  • 12,717