# Can I use Microsoft Excel to convert a “ranged” table to a full breakdown table?

Suppose I have a spreadsheet with the following rows and columns:

``````QTY MIN | QTY MAX  | 1 Color | 2 Color | 3 Color | 4 Color
1       | 10       | \$1.00   | \$1.50   | \$1.75   | \$1.90
11      | 20       | \$0.90   | \$1.40   | \$1.65   | \$1.80
21      | 100      | \$0.80   | \$1.30   | \$1.45   | \$1.70
101     | 1000     | \$0.70   | \$1.20   | \$1.35   | \$1.60
``````

Is there an easy way to convert this data into a table having a single column for quantity, representing every quantity between the lowest min and the highest max (1-1000)? For example, this is the desired table:

``````QTY | 1 Color | 2 Color | 3 Color | 4 Color
1   | \$1.00   | \$1.50   | \$1.75   | \$1.90
2   | \$1.00   | \$1.50   | \$1.75   | \$1.90
3   | \$1.00   | \$1.50   | \$1.75   | \$1.90
...
``````

Not being a frequent Excel user, I am unaware of any specific Excel-based functions to do this, and imagine something exists that would help the process. If not, I'll revert to writing a standalone script to parse a CSV and do this, but I wanted to ask SU first.

-

I'd use VLOOKUP.

You'll need to keep a copy of the source table on Sheet 1.

Sheet 2 will contain your new table. Column A will contain 1-Infinity. Cell B2 will contain `=VLOOKUP(\$A2,Sheet1!A2:G5,2)` Cell C2 will contain `=VLOOKUP(\$A2,Sheet1!A2:G5,3)` And so on.

You can use the fill down tool to copy the columns B-G.

-
`=VLOOKUP(\$A2,Sheet1!\$A:\$F,COLUMN()+1)` in all four columns may be slightly easier. –  pnuts Apr 24 '13 at 20:38

There's nothing inherent in Excel to automagically transform that data but there are a lot of features that would help you do that.

For example to insert multiple rows for the first set from 1-10. Select the row with 11 and choose `Insert` from the pop-up menu. You can press F4 to repeat this action.

To fill down the series of numbers select the `1` then drag down to select the next 9 cells. Then from the `Fill``Series` menu you can fill in a series of numbers.

Then select the dollar values and swipe down to select the range of cells and use `Fill``Down` (or Ctrl+D)

You could repeat this process for the other rows. I would probably go from one worksheet to another rather than inserting rows but the option is there.

If this a one-time process it's probably quicker to use Excel rather than scripting a solution.

-
Thanks for your help, I really appreciate the pointers. I think this is the way to go. Shouldn't take long. –  Charlie S Apr 24 '13 at 19:51