Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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.

share|improve this question
up vote 1 down vote accepted

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.

share|improve this answer
1  
=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 FillSeries menu you can fill in a series of numbers.

series

Then select the dollar values and swipe down to select the range of cells and use FillDown (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.

share|improve this answer
    
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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .