Copying from one sheet to another and choose samples

[rangudu_2008]

Hi,
I've a workbook which contains 48 columns of data. This is used for a analysis in which i need to choose sample rows of data. Out of the 48 columns, in particular, 10 columns are useful for this analysis. They are as follows:

C_NAME - Name representing the AL_ID
AL_ID - Unique identifier field
STATUS - Status of AL_ID
C_SRC_ID - Primary Reference for AL_ID
A_SRC_ID - Secondary Reference for AL_ID
NOTES - Description of AL_ID
UNIT_NAME - Category of AL_ID
RWT - Weightage of identity denoted by AL_ID
RSK_LVL - Level of AL_ID
USER_NAME - Name of the user who processed AL_IDI need to split the entire sheet of data and copy specific rows into 3 new sheets created at runtime (titled LVL1, LVL2 and LVL3) based on RSK_LVL (which is classified into 3 values 1, 2 or 3)

Say if the value of RSK_LVL for a particular row is 1 then it should be copied to LVL1, if it is 2 then to LVL2 and if its 3 it should be copied to LVL3. After all the data is copied to respective sheets, i need to randomly choose rows from each sheet.

How can the data be copied to 3 new sheets based on the RSK_LVL field?

Ranga

[rangudu_2008]

Sample workbook attached. Only main columns are included.

[rangudu_2008]

Hi,
I've managed to develop the code to transfer the contents from one sheet to another, but i need to implement a condition and choose all rows based on the result of that condition - (Conditions attached)

The sample rate column in the above column indicates that out of 'n' rows of data in the sheet for RSK_LVL 3, then 6 % of n should be chosen as samples (similarly for other rates).

I need to set a mark/flag for all rows which should indicate for which sample rate that row will be used. To set the flag, i would like to insert a new column before column A at runtime and set the text as Six/Eight/Ten to indicate the sample rate. How can this flag be set?

Ranga

[rangudu_2008]

I've formulated the condition which needs to be converted into code. The condition is as below:

=IF(AND(T2<=6,AP2=3),IF(AND(T2>=7,OR(AP2=1,AP2=2,AP2=3)),10,8),IF(AND(T2>=7,OR(AP2=1,AP2=2,AP2=3)),10,6))

The above formula needs to be checked through code and to be computed at runtime after inserting a new column before Column A in the attached workbook and should be extended to all cells.
Now, those rows which are computed as 6 should be copied to a new sheet, 8 to another new sheet and 10 to another sheet.

How can this condition be implemented?

Ranga

[mdmackillop]

Your formula is only returning a value of 6 for the data provided. I'm not clear how the 3 LVL sheets with their samples are filtered only to 3 new sheets. The attached goes as far as setting the Six, Eight and Ten flags (formula permitting)

[rangudu_2008]

Hey mdm,
Please find attached the original workbook that i use. The formula relates to this workbook and columns T & AP refer to RWT and RSK_LVL respectively after the rule split is done. Can u repost the same code u've written modified according to the columns in this workbook?


Ideally, it is preferable for me to search for the RWT and RSK_LVL columns and then implement the formula inorder to allocate the records to the 3 sheets.


How can this be done?


Ranga

[mdmackillop]

Ideally, it is preferable for me to search for the RWT and RSK_LVL columns and then implement the formula inorder to allocate the records to the 3 sheets
Not sure what you mean by this.

[rangudu_2008]

Hi mdm,

The formula is static in the sense that column T and column AP must have the values RWT and RSK_LVL respectively.

By formulating a code to search for the RWT and RSK_LVL values, my formula becomes dynamic and can be used in a workbook that need not be in a specific format (the no. of columns can vary).

Ranga

[mdmackillop]

In the "Dump" you just posted, you don't use these column headings.

[rangudu_2008]

Yes, the column headings don't matter for executing the macro. All 48 columns in the sheet need not be used. Only some 17 or 18 columns of this sheet are useful for my purpose.

I'll use the Sub Allocate() to split the contents of Dump sheet across the other 3 sheets and apply the condition to do some further processing. If these 17 or 18 columns be in some random order (the ordering of columns is different) then how can the RWT and RSK_LVL be checked in the condition?

The further processing that i do is to randomly choose 6% of the total no. of records in RWT>0 and RWT<=6 for RSK_LVL = 1 & 2, 8% of total no. of records in RWT>0 and RWT<=6 for RSK_LVL = 3 and 10% of total no. of records in RWT>=7 for RSK_LVL = 1, 2 or 3.

I've formulated the code that will randomly choose certain no. of records from a sheet, but how can the condition be applied effectively in case the ordering of columns is different?

Ranga

[mdmackillop]

Yes, the column headings don't matter for executing the macro.
They matter when you start referring to them in your questions and asking for them to be manipulated.
This example uses the headings to suit the sample provided (not RWT or RSK_LVL) . If you're going to change these, you'll need to amend the code.