Solved:

[rangudu_2008]

I've got a sheet full of data (not a column alone) where rows of data have to be transferred out randomly based on names in a particular column called LAST_UPDATE_NAME.

I've coded out a sampling code which can pick out specific no. of rows randomly and also a filtering code which can filter and transfer specific rows to another sheet.

Can you help me out in merging these two modules together so that rows of data for specific names and no. of samples (mentioned in another sheet) can be sampled out to a single sheet?




<Refer Names.png attached>

For example, I need to randomly choose out rows (one of each name) as shown below. How can this be done?

Output: <Refer Names Output.png attached>

Regards,
Sarang

[Bob Phillips]

Pictures are generally not all that helpful, a workbbok with before and after examples is uually much more helpful in explaining your requirement.

[rangudu_2008]

I've got a sheet full of data (named Dump) where rows of data have to be transferred out randomly based on names in a particular column called LAST_UPDATE_NAME.

I've coded out a sampling code which can pick out specific no. of rows randomly.

I need to modify the code inorder to pick out specific no. of sample rows from the Dump sheet (mentioned by end user in the Sampler sheet against each name) and transfer it to another sheet. How can this be done?

Regards,
Sarang

[mdmackillop]

To be clear, you want specfied number of each name in Dump transferred to another sheet?
If so,
Which column contains the names you wish to sample?
Do you want percentage of each name or specified number?

[rangudu_2008]

Hi mdm,
As i had mentioned earlier, the names are listed in the column titled LAST_UPDATE_NAME.

There is a specific % of samples i need to choose for each name which is decided by number of records cleared & closed (indicated by STATUS NAME column) for each DS Rule Code and KYC Level (indicated by the columns RULE CODE and RSK_LVL)

I've already attached a sample workbook titled "Sample.xls" in my previous post. The other file is a random sampling module that i coded which can choose specific no. of rows as required.
I'm attaching the sampling conditions with this post.

Ranga

[rangudu_2008]

Hi,

Have been trying to code a module to combine 2 other modules (Filter_N_Transfer & Sampler -- Both modules will used alternatively for users for whom samples have to be chosen). Here's the code that i've come up with:

[vba]
Option Explicit

Sub MergeWorksheets()
Dim sh, ShtDest As Worksheet
Dim StartRow, Last, shtLast As Long
Dim CopyRng As Range
With Application
.ScreenUpdating = False
.EnableEvents = False
End With
'Delete the sheet "MergedSheet" if it exist
Application.DisplayAlerts = False
On Error Resume Next
ActiveWorkbook.Worksheets("MergedSheet").Delete
On Error GoTo 0
Application.DisplayAlerts = True
'Add a worksheet with the name "MergedSheet"
Set ShtDest = ActiveWorkbook.Worksheets.Add
ShtDest.Name = "MergedSheet"
StartRow = 2 'Fill in the start row
'Loop through all worksheets except the MergedSheet worksheet and Information worksheet and copy the data to the Destination Sheet
For Each sh In ActiveWorkbook.Worksheets 'Needs tweaking here, to copy only specific sheets
If IsError(Application.Match(sh.Name, Array(ShtDest.Name, "Information"), 0)) Then
Last = LastRow(ShtDest) 'Find the last row with data on the ShtDest and sh
shtLast = LastRow(sh)
'If sh is not empty and if the last row >= StartRow copy the CopyRng
If shtLast > 0 And shtLast >= StartRow Then
Set CopyRng = sh.Range(sh.Rows(StartRow), sh.Rows(shtLast)) 'Set range to copy
'Test if there enough rows in the ShtDest to copy all data
If Last + CopyRng.Rows.Count > ShtDest.Rows.Count Then
MsgBox "There are not enough rows in Destination sheet"
GoTo ExitTSub
End If
CopyRng.Copy
With ShtDest.Cells(Last + 1, "A")
.PasteSpecial xlPasteValues
.PasteSpecial xlPasteFormats
Application.CutCopyMode = False
End With
End If
End If
Next

ExitTSub:
Application.GoTo ShtDest.Cells(1)
ShtDest.Columns.AutoFit 'AutoFit column width
With Application
.ScreenUpdating = True
.EnableEvents = True
End With
End Sub
[/vba]

The problem i'm having with the above code is that i already have some worksheets in the master workbook (where all the modules work together) and some new sheets are generated during execution. I want to consolidate data from these generated worksheets (for each user) alone.

How can data be consolidated from the generated sheets alone?

A possible solution to the problem (which i've thought of) - Whenever any new sheet is generated it needs to be renamed and transferred to another workbook (at the click of a button) from which data can be consolidated (with the above code).

Can anyone help me out??

Ranga

[rangudu_2008]

Hi,

Let me explain all that's required..

I'm trying to build a macro code that can be used as an automated tool for a back-office business where I’m trying to implement a solution for a sampling module. To understand my requirements, just go through the attached document.

I've already coded few modules, but there are some shortfalls which I’m trying to fix as well trying to build a master module where I can integrate everything together.

I use auto filtering a lot which will export data to many worksheets created at runtime. But towards the end, data needs to be consolidated from all such sheets created.

Can anyone help me by with some coding / suggestions where I can accomplish the following?

• Export data to new sheet(s) (using FilterNTransfer) by creating (sort of) a recordset (like in a database) - this must happen every time when data is being exported.
• Use Random Sampler recursively (overlapped with FilterNTransfer) to choose samples before exporting this recordset to a new sheet.
• Get the consolidated sheet from all sheets created at the click of a button?

Just to explain more clearly of my requirement,

I've a big chunk of data (which is used to choose samples) which is filtered frequently and is used up in parts to determine the samples.

• When I say I want to export data to new sheet(s), I need to determine the filtered rows and not actually exporting the whole data at that point of time (when the code is in execution) which can be stored temporarily (in an array or a defined data structure).
• Since this sampling needs to be done recursively (for all users), I need to set some sort of flagging points (to identify the rows that have met the sampling conditions) and also need to randomly sample out these identified rows taking one user at a time. Probably these flags can be cleared out every time and reset and I also should take care of the overriding conditions.
• This way I can have a control of the data being copied to many sheets from the master sheet (and avoid duplication) and also can consolidate the samples chosen for various users to a single sheet.

Probably, collating the samples for various types of users (as per the over-riding conditions) can be done separately.

Can anyone help out??

Note: The attachment's in compressed .rar format but has been converted to .doc format. When the original document's compressed in zip format, i'm not able to upload it as its just over the 1 MB limit (1.03 MB).

Ranga