Solved: At each change find lowest two and copy

[ertan]

Hello all,

I would like to find lowest 2 values at each change of Volum and copy those rows to other sheet.

Data is sorted by "Date" then by "Volum" then by "Rate".

Is it possible by vba?

Regards

Ertan
PS Attached example workbook.

[lucas]

By value do you mean rate?

[ALe]

What do you mean by values? the field "Rate"? you want the lowest 2 values for each vol or the lowest 2 values for each vol of a certain date?

[ertan]

Thanks for replay,

By rate ..

I would like to filter out and copy lowest 2 rates at each change of volume..

Example : For Volume 7 , Rates 2049 and 2099
For Volume 8 , Rates 2362 and 2398

etc..
those rows should be copied to other sheet ..
Sorry for not explaining very good..

Thank you

[ALe]

Have a look (it's bad code but should be working)

[ertan]

ALe,

Thank you very much for your help.:clap:
It works allright..
But I tried to adapt your code to my real life workbook and get an error ..
My question is:

Do I need to keep the Sheet2 ?

Because on my real workbook I dont have blank sheet in between Sheet1 and Sheet3 .. and I think that is why I get error ..
Set lowest2 = Intersect(cella.EntireRow, ActiveSheet.UsedRange) in this line
if I dont have the Sheet2..

But using your workbook as base (by copying my real data to in your workbook Sheet1 ) it work the way it should be..

Thanks for your effort again..

And all the way Italia on World Cup :hi:

[ALe]

Ok,

the rule is that each time you find Sheet(n) or worksheet(n) change in
Sheet("nameofthesheet") and worksheets("nameofthesheet").

it's not a problem of blank sheet, is that the code refers to index of the sheet and not their name.

[ALe]

otherwise create a new workbook with four sheets. Put your data in sheet2 and you'll have ordered data in sheet4.

[ertan]

Thank you ALe,

I manage the adapt your solution to my original workbook.

Best Regards

[ALe]

if you get problems attach your original workbook so we can set the right names of the sheets

[ertan]

Thank you ALe,

I did managed to use the code you provide on my original workbook.
By arranging sheets as you recommend , it work fine and saved me a lot of time..
thanks again.

I would like to mark this tread as solved but I couldn't find the way to do it. (I did once before but forgot now.. I am sorry)

Regards

[ALe]

Thread tools at the top of the page