how to sort and then move data to new column?

[livermore]

Hi.

I've been debating on how to do this but so far couldn't find a way.
I have 3 columns, A, B and C.
Column A show my buy and sell orders.
Column B show how many products I bought and sold
Column C show the price at which I bought and sold

My goal is to sort columns A, B and C and transfer that sorted data into Column D and E.
My reading at this is as follow, if A2 = Buy or Sell then copy C2 to D2 or E2. 1 product sold, nothing more to do.
Now the problem comes in B2 where I have 2 products sold. I need to have 2 times the price which I sold that product into the list.
Also, the orders need to be aligned, meaning, first order was a buy then go to the first row on Buy column. Second order was a sell then goes to first row of Sell column.
How to do all this?


[TABLE="width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]BUY[/TD]
[TD]SELL[/TD]
[/TR]
[TR]
[TD]Buy[/TD]
[TD]1[/TD]
[TD]2803[/TD]
[TD]2803[/TD]
[TD]2803.25[/TD]
[/TR]
[TR]
[TD]Sell[/TD]
[TD]2[/TD]
[TD]2803.25[/TD]
[TD]2803[/TD]
[TD]2803.25[/TD]
[/TR]
[TR]
[TD]Sell[/TD]
[TD]3[/TD]
[TD]2804.5[/TD]
[TD]2803[/TD]
[TD]2804.50[/TD]
[/TR]
[TR]
[TD]Sell[/TD]
[TD]3[/TD]
[TD]2804.25[/TD]
[TD]2803.25[/TD]
[TD]2804.50[/TD]
[/TR]
[TR]
[TD]Buy[/TD]
[TD]2[/TD]
[TD]2803[/TD]
[TD]2803.25[/TD]
[TD]2804.50[/TD]
[/TR]
[TR]
[TD]Buy[/TD]
[TD]5[/TD]
[TD]2803.25[/TD]
[TD]2803.25[/TD]
[TD]2804.25[/TD]
[/TR]
[TR]
[TD]Sell[/TD]
[TD]2[/TD]
[TD]2803.75[/TD]
[TD]2803.25[/TD]
[TD]2804.25[/TD]
[/TR]
[TR]
[TD]Buy[/TD]
[TD]2[/TD]
[TD]2805[/TD]
[TD]2803.25[/TD]
[TD]2804.25[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]2805[/TD]
[TD]2803.75[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]2805[/TD]
[TD]2804.75[/TD]
[/TR]
</tbody>[/TABLE]


Thank you for the help.
Regards

 

[tonyyy]

livermore,

If you're comfortable with a vba approach you might consider the following...

Sub SortMove()
Dim rng As Range, r As Range
Set rng = Range("A2", Cells(Rows.Count, "C").End(xlUp))
rng.Select

With ActiveSheet.Sort
    .SortFields.Clear
    .SortFields.Add Key:=Range("A2:A9"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    .SortFields.Add Key:=Range("B2:B9"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    .SortFields.Add Key:=Range("C2:C9"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        
    .SetRange rng
    .Header = xlGuess
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With

For Each r In rng.Columns(1).Rows
    If r.Value = "Buy" Then
        Cells(Rows.Count, "D").End(xlUp).Offset(1, 0).Resize(r.Offset(0, 1)).Value = r.Offset(0, 2)
    Else
        Cells(Rows.Count, "E").End(xlUp).Offset(1, 0).Resize(r.Offset(0, 1)).Value = r.Offset(0, 2)
    End If
Next r
End Sub

I did note the last entry in Column E of your sample data (2804.75) doesn't exist anywhere in Column C of your sample data; assuming the sample data is incomplete or in error.

Cheers,

tonyyy

 

[Special-K99]

Not saying I can do this but the question is do you want this performed via VBA or formulas?

Just for anyone else who wants to tackle this it looks like you want the values in column C placed in D or E depending on column A being BUY or SELL but you also want the value to appear a set number of times indicated by column B.

UPDATE: Too late, nice one Tonyyy

I'd be interested in seeing a formulaic solution to this

 

[livermore]

Many thanks Tonyyy

The 2804.75 in column E is in fact an error.

Unfortunately my VBA knowledge is... zero.
Will need to research on how to apply the formula on Excel.

Again I thank you for your help.
Regards.

 

[tonyyy]

You're very welcome, livermore...

Just to be clear, vba is programming code, not formulas.

Contextures has easy-to-follow tutorials on how to add and run code. (The code provided should be placed into a Regular Module.)