Quick way to convert Two Columns (Qty & Price) into One Price Column

[Handro]

Hi,

I'm a longtime lurker, but haven't been able to find a post that fits this (seemingly simple) purpose.

I have multiple data sets containing two columns (Quantity & Price) which I want to revert back to raw Price data in order to make a statistical distribution curve.

Example of Data:

[TABLE="width: 222"]
<tbody>[TR]
[TD][TABLE="width: 222"]
<tbody>[TR]
[TD]Quantity[/TD]
[TD]Price[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]$75.00[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]$76.95[/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD]$77.00[/TD]
[/TR]
[TR]
[TD]18[/TD]
[TD]$79.99[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]$82.00[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]$83.00[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]$85.00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Apart from manually copying & pasting the values multiple times into new rows, is there another, quicker solution (potentially in VBA) to revert the data? I have some experience making Macros in VBA, but it's mostly from Googling and deciphering code to suit my needs.

I am using MS Excel 2007. Thank you for your help!

 

[Andrew Poulsom]

Welcome to MrExcel.

What do you mean by "revert back to raw Price data"?

 

[Handro]

Hi Andrew,

As in, reorganizing the price data into individual cells. I'm working with sale prices for identical event tickets. So, in my example, taking the row of 2 tickets priced at $75 and reorganizing it in a new sheet as 2 rows of 75. Then taking the row of 14 tickets at $76.95 and converting it to 14 rows of $76.95 etc.

[TABLE="width: 64"]
<colgroup><col width="64" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"]Price[/TD]
[/TR]
[TR]
[TD="align: right"]75[/TD]
[/TR]
[TR]
[TD="align: right"]75[/TD]
[/TR]
[TR]
[TD="align: right"]76.95[/TD]
[/TR]
[TR]
[TD="align: right"]76.95[/TD]
[/TR]
[TR]
[TD="align: right"]76.95[/TD]
[/TR]
[TR]
[TD]etc.[/TD]
[/TR]
</tbody>[/TABLE]

Since I'm eventually going to need the mean and standard deviation of the data, I need the data in this form.

I'm assuming the way to do this would be in VBA since the new number of rows in the new sheet is dependent on the quantity column.

I hope I'm explaining this well enough...

[TABLE="width: 64"]
<colgroup><col width="64" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"][/TD]
[/TR]
</tbody>[/TABLE]

 

[shg]

How about just formulas?

[Table="width:, class:grid"][tr][td] [/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][/tr]
[tr][td]

1​

[/td][td]

Quantity​

[/td][td]

Price​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][/tr]

[tr][td]

2​

[/td][td]

2​

[/td][td]

$75.00​

[/td][td]

Mean​

[/td][td]

$79.58​

[/td][td]D2: =SUMPRODUCT(B2:B8, A2:A8) / SUM(A2:A8)[/td][/tr]

[tr][td]

3​

[/td][td]

14​

[/td][td]

$76.95​

[/td][td]

SD​

[/td][td]

$2.70​

[/td][td]D3: =SQRT(SUMPRODUCT((B2:B8 - D2)^2 * A2:A8) / SUM(A2:A8))[/td][/tr]

[tr][td]

4​

[/td][td]

16​

[/td][td]

$77.00​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

5​

[/td][td]

18​

[/td][td]

$79.99​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

6​

[/td][td]

10​

[/td][td]

$82.00​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

7​

[/td][td]

10​

[/td][td]

$83.00​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

8​

[/td][td]

4​

[/td][td]

$85.00​

[/td][td][/td][td][/td][td][/td][/tr]
[/table]

 

[Andrew Poulsom]

Here's a macro you can try:

Sub Test()
    Dim Rng As Range
    Dim Cell As Range
    With Worksheets("Sheet1")
        Set Rng = .Range("A2:A" & .Range("A" & .Rows.Count).End(xlUp).Row)
    End With
    With Worksheets("Sheet2")
        .Cells.Delete
        .Range("A1").Value = "Prices"
        For Each Cell In Rng
            .Range("A" & .Rows.Count).End(xlUp).Offset(1).Resize(Cell.Value).Value = Cell.Offset(, 1).Value
        Next Cell
    End With
End Sub

 

[shg]

BTW, the formula in D3 calculates the deviation for the prices as a population, not a sample (which is $2.72).

 

[Handro]

Thanks Shg and Andrew for such quick responses!


Shg: I tried using your formulas but the standard deviation function is returning the error #Value! I understand why you're using the squareroot and sumproduct functions conceptually, and tried to diagnose why it wasn't working myself, but the rest of the function goes over my head to be honest haha. If you have a few minutes, I'd appreciate it if you could explain the rest of the function for a fellow Texan.

Andrew: I tried your macro and it works exactly as I imagined originally. I'm going to see if Shg's idea works just as well and compare which one better suits my purpose.

Thanks again!

 

[Handro]

Nevermind, Shg. I figured it out, I accidentally changed your original response when I pasted it into my spreadsheet. Your response works great as well. Thanks for your help!

 

[shg]

I posted the workbook at https://app.box.com/s/f2c155ffe8c327dc7237

The formula is explained at http://www.itl.nist.gov/div898/software/dataplot/refman2/ch2/weightsd.pdf

EDIT: You're welcome.