Solved

EXCEL: Random Select and Copy 10% of the Row

Posted on 2016-10-26
7
53 Views
Last Modified: 2016-11-01
I have been copying random 10% of entire row of data into the new sheet. For example, i have 1000 row of the data, i have to select and copy 10% row of data (which is 100) randomly into the new sheet. Been doing this on weekly basis.

Is there way to do it automatically by selecting and copying 10% of row into the new sheet using VBA Excel?. It would be great if you can create VBA macro code to detect the entire number of row automatically and select 10% of the row randomly.

 I have attached mock spreadsheet attachment and you can use the example to random select 10% of the row into new sheet

Please let me know if you have issue.
SelectRandom.xlsx
0
Comment
Question by:Sherry
  • 4
  • 3
7 Comments
 
LVL 21

Expert Comment

by:Ejgil Hedegaard
ID: 41861205
Check attached file.
Press button to run.

This is the code

Option Explicit

Sub Extract10Percent()
    Dim ws1 As Worksheet, ws2 As Worksheet
    Dim rwMax As Long, rw As Long
    
    Application.ScreenUpdating = False
    
    Set ws1 = Worksheets("Sheet1")
    Set ws2 = Worksheets.Add
    ws1.Range("A2").CurrentRegion.Copy ws2.Range("A1")
    rwMax = ws2.Range("A1").CurrentRegion.Rows.Count
    ws2.Range("D1") = "Random"
    For rw = 2 To rwMax
        ws2.Range("D" & rw) = WorksheetFunction.RandBetween(1, 10)
    Next rw
    ws2.Range("A1").CurrentRegion.Sort Key1:="Random", Order1:=xlAscending, Header:=xlYes
    rw = WorksheetFunction.RoundUp((rwMax - 1) / 10, 0) + 2
    ws2.Range("D1:D" & rwMax).ClearContents
    ws2.Range("A" & rw & ":C" & rwMax).ClearContents
    ws2.Range("A1").CurrentRegion.Sort Key1:="No", Order1:=xlAscending, Header:=xlYes
End Sub

Open in new window

SelectRandom.xlsm
0
 

Author Comment

by:Sherry
ID: 41861309
Ejgil Hedegaard

Many thanks for the prompt solution, it has met my expectation entirely and very interesting coding. I have one more query.

If i have 30 column of data, is there way that your code that identify the last column instead of putting column "D" in your coding.

Just incase if i have come across with different number of column

Many thanks
0
 

Author Comment

by:Sherry
ID: 41862589
Apologies another query, I have changed the number to 5 in the coding and it doesn't generate 5% number of alert.
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 21

Accepted Solution

by:
Ejgil Hedegaard earned 500 total points
ID: 41862675
The values (1 to 10) in the RandBetween formula does not set the percentage, so changing 10 to 5 does not change the number of output rows.
The random values are not used to select the rows, so it can be anything (more or less), but is used to set the sorting values.
I think it will be better with more random values, so I have changed the interval to 1 to 1000.
The number of result rows is in the RoundUp formula.
Divide by 10 is equal to 10 %, changed to accept any percentage.
Code changed to accept any number of columns.
Set the percentage in cell K1, and run.
It is a named range (the name is used in the program), so you can move (not copy) the cell to another location.

Sub ExtractPercent()
    Dim ws1 As Worksheet, ws2 As Worksheet
    Dim rwMax As Long, rw As Long, colMax As Integer
    
    Application.ScreenUpdating = False
    
    Set ws1 = Worksheets("Sheet1")
    Set ws2 = Worksheets.Add
    ws1.Range("A2").CurrentRegion.Copy ws2.Range("A1")
    rwMax = ws2.Range("A1").CurrentRegion.Rows.Count
    colMax = ws2.Range("A1").CurrentRegion.Columns.Count + 1
    ws2.Cells(1, colMax) = "Random"
    For rw = 2 To rwMax
        ws2.Cells(rw, colMax) = WorksheetFunction.RandBetween(1, 1000)
    Next rw
    ws2.Range("A1").CurrentRegion.Sort Key1:="Random", Order1:=xlAscending, Header:=xlYes
    rw = WorksheetFunction.RoundUp((rwMax - 1) * [ExtractPercentage], 0) + 2
    ws2.Range(Cells(1, colMax), Cells(rwMax, colMax)).ClearContents
    ws2.Range(Cells(rw, 1), Cells(rwMax, colMax)).ClearContents
    ws2.Range("A1").CurrentRegion.Sort Key1:="No", Order1:=xlAscending, Header:=xlYes
    ws2.Columns.AutoFit
End Sub

Open in new window

SelectRandom.xlsm
0
 

Author Comment

by:Sherry
ID: 41862839
Thanks Ejigil

That was smart coding but received coding error when i run them. see attached

Good idea on the custom percentage
Screen-Shot-2016-10-27-at-19.29.25.png
Screen-Shot-2016-10-27-at-19.32.57.png
0
 
LVL 21

Expert Comment

by:Ejgil Hedegaard
ID: 41863016
Guess you used the code in another workbook, where the named range ExtractPercentage is missing.
Name the cell with the percentage value, and it will work.
The name must be ExtractPercentage, or if you use something else, change the name in the brackets [ ] to whatever you name it.
0
 

Author Closing Comment

by:Sherry
ID: 41869146
Excellent Answer and Interesting VBA coding. Expectation met
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now