Randomly Select Varying Number of Rows

Hi all,

I have been trying (without success) to randomly select a number of rows - the difference between the number in cell EK1 and cell EH1, from only the rows that have Paid in column EE and nothing in column EG - see the attached speadsheet.

I will appreciate your help.

Thank you,
Gos-C

There will be multiple approaches to this type of thing - most of which will be more efficient than the below...

Please Login or Register  to view this content.

General premise being you push your range to an array, create a second array which holds RAND values (for valid rows) ... use a small (where max k determined by EK1/EH1) to identify rows valid for selection.

Above not really tested to any great extent but it when each time it is run it will select 69 rows randomly (from valid pool of 266 rows)
[we assume that EK1/EH1 will never generate a value > valid pool]

Looks good! You are the best. Thank you very much DonkeyOte. I sincerely appreciate you help.

One other request though: Can you mark the selected rows in column EG (with x or some other letter/symbol) as I have a code to copy and paste the selected rows to another sheet,

Thank you,
Gos-C

Originally Posted by Gos-C

Can you mark the selected rows in column EG (with x or some other letter/symbol)

You can:

Please Login or Register  to view this content.

Originally Posted by Gos-C

I have a code to copy and paste the selected rows to another sheet

Whether or not you need to denote the rows is another question... rather than Selecting the range (per earlier code sample) you could just Copy the range and proceed from there.

If you do opt to use denotation you must remember to clear Col EG before re-populating so as to remove legacy values.

Thank you, DonkeyOte, I will combine all the subroutines, see how everything works and the post a sample of my project for further help.

Gos-C

Hi all,

I need to modify DonkeyOte's code but it is too complexed for me to do. Can someone please help me.

Here is the change needed:

When column EE has PAID and there is a duplicate record (i.e., there is another record where columns G, Q, AF are the same as those of the PAID record) but column EE of the duplicate record has PDVOID, that PDVOID record cancels the PAID record. Therefore, the PAID record is not eligible for selection

OR

When column EE has PAID but column EF (of the same record) has REVERSAL, that PAID record is not eligible for selection.

Thank you very much,
Gos-C

Originally Posted by Gos-C

When column EE has PAID but column EF (of the same record) has REVERSAL, that PAID record is not eligible for selection.

Change:

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

Regards:

Originally Posted by Gos-C

When column EE has PAID and there is a duplicate record (i.e., there is another record where columns G, Q, AF are the same as those of the PAID record) but column EE of the duplicate record has PDVOID, that PDVOID record cancels the PAID record. Therefore, the PAID record is not eligible for selection

That would require a little more adaptation ...

1. can you outline the XL version(s) you will be running this against ?
Your profile implies XL2007 or above, the sample is .xls which could imply 2003 requirements though might have been to get maximum visibility on the board

2. how many lines of data are you likely to have ? (worse case)

What you want to do is straightforward enough, however, answers to the above might dictate which approach we opt to use to achieve end result.

I will be at work shortly so I may not reply immediately...

Hi DonkeyOte,

Thanks for your response.

Yes, I am using xl2007 at work (and xl2010 at home) but I post my samples as xl2003 to get the maximum visible on the board.

The file can have 1,000's (even over 10,000) lines.

I sincerely appreciate your help.

Gos-C

Hi all,

I haven't been able to figure out how to prevent the Paid record described below from being selected randomly.

When column EE has PAID and there is a duplicate record (i.e., there is another record where columns G, Q, AF are the same as those of the PAID record) but column EE of the duplicate record has PDVOID, that PDVOID record cancels the PAID record. Therefore, the PAID record is not eligible for selection

Any help, please?

Thank you,
Gos-C

I will try and post up some code today but in short you could look to either:

a) given XL2007 use temp formulae to create the RAND values, eg:

Please Login or Register  to view this content.

which would be repeated for each row

else

b)

in VBA-exclusive terms - rather than iterate the Array repeatedly for the PDVOID test I'd be inclined to use a dictionary object to create a unique list of those lines that contain PDVOID and subsequently test the iterated item against the Dictionary to see if a PDVOID item exists ignoring those that do.

There will be plenty of methods available. It might help to post a modified sample that reflects the latest requirements.

Looking forward with great anticipation to it.

Thanks in advance,
Gos-C

Hi DonkeyOte,

Using the formula in a) of your suggestions below, I was able to identify a field with a common value (string) that would help eliminate the Paid / PDVoid records.

When a record is PDVoided, the number (a string) in column BA is the same as the Paid and record that it cancelled. Therefore, if column EE is Paid and the number in column BA appears more than once in that column, it is not eligible for selection. So now, I need a piece of code to test for that.

Can you help, please?

Thank you,
Gos-C

Gos-C, again I'm afraid I'm in the same boat as before time-wise, however, this has sat open for a while so hopefully the below will help ?

Please Login or Register  to view this content.

It's not an ideal approach and if I had the time I'd be inclined to keep VBA-focused (ie avoid the COUNTIF approach above)

Without a meaningful sample file it's also hard to test... so the above is theoretical...

Hi DonkeyOte,

It didn't select anything. I will post a sample sheet later today.

I sincerely appreciate you help.

Regards,
Gos-C

Hi DonkeyOte,

After making a few changesto the code (to match the changes I had made to the data layout) it is working now. Thank you very much for your invaluable assistance.

One other question:

I added the following line at the end of the code:

Please Login or Register  to view this content.

See the following:

Please Login or Register  to view this content.

but it gives me the error: Invalid procedure call or argument. Any idea what I did wrong?

Gos-C

Regards:

Please Login or Register  to view this content.

As far as I can tell neither Range nor sRange are declared. What are you trying to do exactly ?

On an aside the remainder of the code is not the latest version (re: PDVOID etc...)

Hi DonkeyOte,

Range s/b Range("EO2") as follows:

Please Login or Register  to view this content.

I want to write the total number of records selected randomly to cell EO2 - to be used in another calculation.

I had already delcared sRange globally:

Please Login or Register  to view this content.

Maybe, I just posted the wrong version . . . or, out of desperation, I used the wrong code and thought that it was the latest version. I will check it when I get back in the office tomorrow.

Gos-C

Though we can see sRange is declared we can't see the Range being assigned to it, otherwise the latest code snippet you posted appears to be ok.

FWIW, however, I believe the number of items being selected is determined by lngK, no ?

Regards the code you posted in your last post but one... note too that in your "x" insertion line at the end of the routine you are no longer qualifying column EG correctly and are thus at risk of inserting the "x"'s in the wrong sheet - see Post 4 for the correct syntax.

Hi DonkeyOte,

I had posted the wrong version. It is working! I will test it more thoroughly and get back to you later.

Thank you,
Gos-C