Segregation of Rows Amount Wise

[khawarameer]

I want to be able to do following with my excel sheet which contain three columns "Bill Number", "Date" and "Amount".

The data is arranged in order of Amount. (Largest to Samllest)

a. I want add a blank row within data with following criteria.

rows having amount above 10Million, Rows having amount 5 to 10M, Rows having amount 2 to 5M and rows having amount below 2M

b. I want to select random 50% rows from each of above seperated data.

"I am unable to post attachment so you have to assume to data according to above field".

Can any one help.

Regards

 

[hiker95]

khawarameer,

Please post a screenshot of your sheet(s), what you have and what you expect to achieve, with Excel Jeanie HTML 4 (contains graphic instructions).
http://www.excel-jeanie-html.de/html/hlp_schnell_en.php

Or, if your file does not contain sensitive information (or a manually entered non-sensitive file), you can upload it to www.box.net and provide us with a link to your workbook.

 

[khawarameer]

I have uploaded to file @ box.net

here is the link http://www.box.net/shared/ae9oy3xthg

Regards

 

[mikerickson]

Something like this should work.
But I have misgivings about inserting blank rows between classes of data. Blank rows cause many of Excel's features to fall over.

Sub test()
    Dim dataRange As Range
    With ThisWorkbook.Sheets("Sheet1").Range("A:C")
        On Error Resume Next
        .SpecialCells(xlCellTypeBlanks).EntireRow.Delete shift:=xlUp
        On Error Goto 0
        Set dataRange = Range(.Cells(1, .Columns.Count), .Cells(.Rows.Count, 1).End(xlUp))
    End With
    
    With dataRange
        .Parent.AutoFilterMode = False
        .Sort Key1:=.Cells(1, 3), Order1:=xlDescending, Header:=xlYes, _
            OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
            
        With .Columns(3)
            .Cells(1, 1).Offset(Application.Match(10000000, .Cells, -1), 0).EntireRow.Insert
            .Cells(1, 1).Offset(Application.Match(5000000, .Cells, -1), 0).EntireRow.Insert
            .Cells(1, 1).Offset(Application.Match(2000000, .Cells, -1), 0).EntireRow.Insert
        End With
    End With
    
    With dataRange.Offset(0, 4).Columns(1)
        .FormulaR1C1 = "=IF(RC1<>"""",RAND()<.5)"
        .AutoFilter Field:=1, Criteria1:="FALSE"
        .Hidden = True
    End With
End Sub

 

[khawarameer]

Thanks Mike.

Further to your VBA Solution.

With dataRange.Offset(0, 4).Columns(1)
.FormulaR1C1 = "=IF(RC1<>"""",RAND()<.5)"
.AutoFilter Field:=1, Criteria1:="FALSE"
.Hidden = True
End With

I guess the above section of the code randonly select 50% rows right. Can i place a variable or array containing these {50%, 30%, 20% & 120%} at the place of 50% if yes, how?

 

[mikerickson]

I'm not sure how to select 120% of a range of cells.

To control the percentage selected, assign a Double variable, desiredPercentage, to the percentage being used, between 0 and 1.

.FormulaR1C1 = "=IF(RC1<>"""",RAND()<" & (1 - desiredPercentage) & ")"

 

[khawarameer]

Sorry Mike.

It was a typing Mistake Actually its 10%.

 

[mikerickson]

I should explain that desiredPercentage is the percent you want found.

If you have 100 records and a desiredPercentage of 60%, the filter will show 60 records.
(+/- the vagaries of using RAND.)