Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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
add comment

2 Answers

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
add comment

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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.