Closed Thread
Results 1 to 36 of 36

randomized rows based on percentage of total rows

  1. #1
    Registered User
    Join Date
    03-26-2014
    Location
    Phoenix, AZ
    MS-Off Ver
    Excel 2010
    Posts
    18

    Question randomized rows based on percentage of total rows

    I am unfamiliar with VBA however the language seems easier to read than to write. I am familiar with other aspects of Excel.

    I have medium size sets of data. They have different properties including alpha, integer, date, etc..
    I would like to set up a macro to search through the data dump based on criteria asked of me specific to a row and return a random list of entire rows that is approximately 10% of total rows where the criteria matches up with what I have specified.

    Site Building Room Tag
    100-Emerson Court A A101 SS102058
    100-Emerson Court A A115 SS107052
    100-Emerson Court A A123 SS104800
    100-Emerson Court B B117 1000875
    102-Capitol_School Cafeteria 128 800187

    So if I choose the site to be "100-Emerson_Court" and there are 400 rows with "100-Emerson_Court" in column A, in a separate sheet called "audit", it will transpose a random 10% or 40 of the rows.

    I am unsure if there is information I am accidentally omitting. Ask and I will try to answer as well as I know

  2. #2
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 2003, 2010, 2016 (Windows)
    Posts
    4,855

    Re: randomized rows based on percentage of total rows

    You don't really need to use VBA - add a column of formulas

    =RAND()*100

    and copy down to match your list. Apply data filters, and after you filter your data based on the desired site, simply filter the column of formulas using Less than or equal to, and choose 10, 20, 30 or 40 as your number (percent of the population). Note that this will not be completely precise, but will be close.

    After you filter, the numbers may change, but ignore that. Then just select your table, copy, and paste into Audit.

    If you want a macro, post back - the macro could create the Audit sheet, select the 10 or 20 or 40 percent based on the formula column, and paste into Audit. Not a great time savings, unless you need to do it very frequently, or for every site in your list. (the macro could create an audit sheet for every site....)
    Bernie Deitrick
    Excel MVP 2000-2010

  3. #3
    Registered User
    Join Date
    03-26-2014
    Location
    Phoenix, AZ
    MS-Off Ver
    Excel 2010
    Posts
    18

    Re: randomized rows based on percentage of total rows

    I prefer a macro as I will be doing it for many sites. Eventually I want to archive the audit listing and run a macro to search for assets not previously searched thus I can cover a different 10% every time I run it. The "not previously searched assets" would include any new assets that are added to the listing. This way the audit is rolling and in theory I should be able to get the full inventory every 10 cycles.

  4. #4
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 2003, 2010, 2016 (Windows)
    Posts
    4,855

    Re: randomized rows based on percentage of total rows

    Then you want to record previous selections, and not choose entirely randomly at all. You will need to add a record to your data set to flag assets that have been audited (or designated to be audited, at least) and then not choose from those items again.

    I have helped somebody with something very similar - take a look at this thread:

    http://answers.microsoft.com/en-us/o...1-3da4538773f2

    The method should be easily adapted to your situation, though a sample workbook with sensitive data removed would be helpful.

  5. #5
    Forum Expert
    Join Date
    07-31-2010
    Location
    California
    MS-Off Ver
    Excel 2007
    Posts
    4,070

    Re: randomized rows based on percentage of total rows

    Try this.

    I cannot take credit for the Function. I think it originally came from C. Pearson.

    Please Login or Register  to view this content.

  6. #6
    Registered User
    Join Date
    03-26-2014
    Location
    Phoenix, AZ
    MS-Off Ver
    Excel 2010
    Posts
    18

    Re: randomized rows based on percentage of total rows

    Although that function seemed to work, it did not seem to give me a random listing of items based on criteria. I have uploaded the data in the format I want sans sensitive information. The output file from is in a separate sheet.

    I am currently reviewing the post of Bernie Deitrick that was posted on microsoft and seeing if it will work for me.

    Thank you for your input. I will continue to work at this until I figure something workable.

    data dump.xlsx

  7. #7
    Forum Expert
    Join Date
    07-31-2010
    Location
    California
    MS-Off Ver
    Excel 2007
    Posts
    4,070

    Re: randomized rows based on percentage of total rows

    It worked perfectly for me on your workbook. Did you change the sheet name from "sheet1" to "data"? Did you change the filter word "test" to "100-Emerson_Court". When I did this I got 68 values. Approximately 10% of 678.

  8. #8
    Registered User
    Join Date
    03-26-2014
    Location
    Phoenix, AZ
    MS-Off Ver
    Excel 2010
    Posts
    18

    Re: randomized rows based on percentage of total rows

    I did. I received the same amount of values. However they are not random. They are simply the last 10% of the listing based on criteria.

  9. #9
    Forum Expert
    Join Date
    07-31-2010
    Location
    California
    MS-Off Ver
    Excel 2007
    Posts
    4,070

    Re: randomized rows based on percentage of total rows

    Yeah I goofed on the code I pasted, but had it right in mine. This line right here was wrong:

    Please Login or Register  to view this content.
    I made some more changes which should make it more stable. Give this a try:

    Please Login or Register  to view this content.

  10. #10
    Registered User
    Join Date
    03-26-2014
    Location
    Phoenix, AZ
    MS-Off Ver
    Excel 2010
    Posts
    18

    Re: randomized rows based on percentage of total rows

    Ok. So I took your latter code and changed the name "output" to "audit" (an existing worksheet) and tried it. However I am ending up with 40% rather than 10% and there are a few cells in column a that are colored red. If I try it unadulterated then I receive a Run-time error '1004': Method 'Range' of object'_Worksheet' failed and when I debug I am led to line

    ws2.Range("A" & qArray(icell)).Interior.ColorIndex = 3

    At this point I am completely at your mercy as I have no idea based on the code what is happening.

  11. #11
    Forum Expert
    Join Date
    07-31-2010
    Location
    California
    MS-Off Ver
    Excel 2007
    Posts
    4,070

    Re: randomized rows based on percentage of total rows

    I am at a loss. I am not able to duplicate what you are experiencing. I have a feeling you didnt change all the "output" to "audit". You might have missed one. Can you submit the code that you are running for me to see.

    I have tried multiple variations of sheet names and multiple sites, each time I get 10% on a proper sheet. There might be some piece of information you are not telling me, which you may not think important, but is completely affecting how the code runs.

  12. #12
    Registered User
    Join Date
    03-26-2014
    Location
    Phoenix, AZ
    MS-Off Ver
    Excel 2010
    Posts
    18

    Re: randomized rows based on percentage of total rows

    I am including the entire workbook with the other macros that I have put in. I have put in the code verbatim and still received the run time error. Maybe I am missing something or one of the other sheets, macros or whatever is impacting the function or macro you made.
    randomized audit.xlsm

  13. #13
    Forum Expert
    Join Date
    07-31-2010
    Location
    California
    MS-Off Ver
    Excel 2007
    Posts
    4,070

    Re: randomized rows based on percentage of total rows

    The reason you experience the 40% with a few in red is because on your sheet there is something causing the code to run real slow.

    I removed all the other worksheets in the randomized audit workbook and it works instantaneously but with the other sheets there it doesn't want to go. If you are referencing the audit sheet in formulas on these other sheets that is probably the cause. I didn't spend too much time trying to track it down.

    I am looking into optimizing the code but am running into an issue trying to get the array to reorganize itself in numerical order. So that I can eliminate a the last loop. I will look into it as time permits.

  14. #14
    Registered User
    Join Date
    03-26-2014
    Location
    Phoenix, AZ
    MS-Off Ver
    Excel 2010
    Posts
    18

    Re: randomized rows based on percentage of total rows

    I think I will create a vba in the initial workbook to copy all records from "data" into a new workbook with same naming conventions (data and audit) and that should take care of it. I will let you know how I do. Thank you again. I appreciate your willingness and expertise.

  15. #15
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 2003, 2010, 2016 (Windows)
    Posts
    4,855

    Re: randomized rows based on percentage of total rows

    Create Audit List.xlsm

    Try this version of your file - I have added a userform to help with selecting the site, and a column to record the date of the audit, allowing the random pick to exclude previously audited items.

  16. #16
    Registered User
    Join Date
    03-26-2014
    Location
    Phoenix, AZ
    MS-Off Ver
    Excel 2010
    Posts
    18

    Re: randomized rows based on percentage of total rows

    That is very exciting. However upon opening your file and selecting the site I receive this error
    Run-time error ' 13': Type mismatch
    lngR = Application.Match(rngC.Offset(0, iTagNo.Column - 1), Worksheets("Data").Columns(iTagNo.Column), False)

    Any suggestions?

  17. #17
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 2003, 2010, 2016 (Windows)
    Posts
    4,855

    Re: randomized rows based on percentage of total rows

    Are you running the macro in the workbook that I posted, or in another workbook? The macro worked flawlessly in my workbook - try replacing that line with

    MsgBox "Column with data is " & iTagNo.Column
    MsgBox "List is in " & .Range(.Range("A2"), .Cells(.Rows.Count, "A").End(xlUp)).Address
    MsgBox "Looking for " & rngC.Offset(0, iTagNo.Column - 1)
    MsgBox "Looking in " & Worksheets("Data").Columns(iTagNo.Column).Address
    lngR = Application.Match(rngC.Offset(0, iTagNo.Column - 1), Worksheets("Data").Columns(iTagNo.Column), False)

    There should be no errors, and you should get 4 messages describing what is being used in the line that is throwing the error.....

  18. #18
    Registered User
    Join Date
    03-26-2014
    Location
    Phoenix, AZ
    MS-Off Ver
    Excel 2010
    Posts
    18

    Re: randomized rows based on percentage of total rows

    The messages are
    "Column with data is 5"
    "List is in $A$1:$A$2"
    "Looking for "
    "Looking in $E:$E"

    and then the same run time error

  19. #19
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 2003, 2010, 2016 (Windows)
    Posts
    4,855

    Re: randomized rows based on percentage of total rows

    Remove the Msgbox lines, and then change that line and the next one to be run conditionally (you had a blank value in your tag number cell):

    Please Login or Register  to view this content.

  20. #20
    Registered User
    Join Date
    03-26-2014
    Location
    Phoenix, AZ
    MS-Off Ver
    Excel 2010
    Posts
    18

    Re: randomized rows based on percentage of total rows

    So I did as you suggested. It looks it is running correctly but there is no output sheet. I still only have the two initial sheets. Here is an excerpt of the code I changed based on your suggestion.

    Please Login or Register  to view this content.

  21. #21
    Registered User
    Join Date
    03-26-2014
    Location
    Phoenix, AZ
    MS-Off Ver
    Excel 2010
    Posts
    18

    Re: randomized rows based on percentage of total rows

    I made a mistake. The macro creates new sheets however there is no data in any of them. I am attaching one for reference.

    104-Dunbar_School Audit 2014-04-01 10-55-26.xlsx

  22. #22
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 2003, 2010, 2016 (Windows)
    Posts
    4,855

    Re: randomized rows based on percentage of total rows

    104-Dunbar_School Audit 2014-04-01 15-25-32.xlsx

    This is what I get from your sample file. Can you post the file you are currently using?

  23. #23
    Registered User
    Join Date
    03-26-2014
    Location
    Phoenix, AZ
    MS-Off Ver
    Excel 2010
    Posts
    18

    Re: randomized rows based on percentage of total rows

    Certainly. It is frustrating that there may be something wrong with my files and I am simply too ignorant to know what to look for.

    307469d1395943543-randomized-rows-based-on-percentage-of-total-rows-create-audit-list (1).xlsm

  24. #24
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 2003, 2010, 2016 (Windows)
    Posts
    4,855

    Re: randomized rows based on percentage of total rows

    Your file worked fine for me - this is the result.

    104-Dunbar_School Audit 2014-04-01 15-38-39.xlsx

    The only thing I can think of is that for some reason when the formulas are entered, the cells are formatted incorrectly on your machine, or the formulas are not otherwise being properly calculated. Try using this code to see if it helps.

    Please Login or Register  to view this content.
    Last edited by Bernie Deitrick; 04-01-2014 at 03:49 PM.

  25. #25
    Registered User
    Join Date
    03-26-2014
    Location
    Phoenix, AZ
    MS-Off Ver
    Excel 2010
    Posts
    18

    Re: randomized rows based on percentage of total rows

    That is so odd. I did the same thing. And here is my result. I am going to check with my IT department to see if for some reason the network won't let me save files made from an xlsm.

    104-Dunbar_School Audit 2014-04-01 12-53-06.xlsx

  26. #26
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 2003, 2010, 2016 (Windows)
    Posts
    4,855

    Re: randomized rows based on percentage of total rows

    Before you do that, comment out or remove these three lines - the created sheet will remain in the original workbook, and you can see whether there is data in it.

    Please Login or Register  to view this content.

  27. #27
    Registered User
    Join Date
    03-26-2014
    Location
    Phoenix, AZ
    MS-Off Ver
    Excel 2010
    Posts
    18

    Re: randomized rows based on percentage of total rows

    I commented out the lines. I used your new code. I tried running it from a local drive and a network drive. However there is no data in the generated worksheet.

  28. #28
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 2003, 2010, 2016 (Windows)
    Posts
    4,855

    Re: randomized rows based on percentage of total rows

    Do you know how to step through the code or put a break into your code? For stepping/troubleshooting, put your cursor in the macro code and then press F8. Switch back to Excel, see what happened, then switch back to the VBE and press F8 again. Continue ad-naseum, until you figure out what happened.

    With the break, select the line that starts

    For Each rngC...

    then press F9 - which will put a break on that line, then press F5 to run until that point.

    Then stop the macro (the square button), save the file under a new name, and post that file here.

    Troubleshooting is hard without being at the keyboard
    Last edited by Bernie Deitrick; 04-01-2014 at 04:46 PM.

  29. #29
    Registered User
    Join Date
    03-26-2014
    Location
    Phoenix, AZ
    MS-Off Ver
    Excel 2010
    Posts
    18

    Re: randomized rows based on percentage of total rows

    As I was going through each line I noticed that the macro inserted a column and added the word "FALSE" to each row despite selecting one of the criteria. I would have thought it would have labeled all rows that did not fit the criteria as FALSE, delete the FALSE rows, randomize the remaining rows and copy the results to the sheet.

    I think I did what you asked. Here it is.

    revised randomized rows.xlsm

  30. #30
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 2003, 2010, 2016 (Windows)
    Posts
    4,855

    Re: randomized rows based on percentage of total rows

    I think I have it worked out.

    I was using 2003 to develop the macro, and I was setting the formula using the .Formula property and not the .FormulaR1C1 property. Using .Formula = "=RC...." in 2003 works the same as .FormulaR1C1 = "=RC...." because there is no column RC. In 2010, that column exists, and so the code gives a different formula. I don't know why I used formula and not formulaR1C1 - I usually do, so I apologize for giving you the run around.

    Anyway, this version will work (I hope! )

    Please Login or Register  to view this content.

  31. #31
    Registered User
    Join Date
    03-26-2014
    Location
    Phoenix, AZ
    MS-Off Ver
    Excel 2010
    Posts
    18

    Re: randomized rows based on percentage of total rows

    By jove, that works! Excellent. I initially thought it might have been my version of Excel but I knew I had the most recent version. Thank you for your incredible help.

    Now to my next part. Archiving, storing lists so as to not randomly create the same lists consistently. I need to think through and test myself. Thank you again.

  32. #32
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 2003, 2010, 2016 (Windows)
    Posts
    4,855

    Re: randomized rows based on percentage of total rows

    Quote Originally Posted by Uwantachat View Post
    By jove, that works! Excellent. I initially thought it might have been my version of Excel but I knew I had the most recent version. Thank you for your incredible help.

    Now to my next part. Archiving, storing lists so as to not randomly create the same lists consistently. I need to think through and test myself. Thank you again.
    The code that you have already does that - it is a random selection without replacement. The code adds a column of dates called "Audited" to the far right of your Data sheet, and the code will only select items for each audit if that column is blank for that item. When an item is selected for an audit, the column is filled with the current data. In that way, items will not be re-audited - until you blank out that date - when you do that, just sort based on date and delete the earliest dates from the column.

  33. #33
    Registered User
    Join Date
    03-26-2014
    Location
    Phoenix, AZ
    MS-Off Ver
    Excel 2010
    Posts
    18

    Re: randomized rows based on percentage of total rows

    I have another question. May I update the code to seek out a fixed number of rows, lets say 22.
    I believe it is in this portion of the code

    Please Login or Register  to view this content.
    But before I try to understand this wondrous (still grateful by the way) work, I figure you were the author. You would know how to do it better.
    Thank you.

  34. #34
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 2003, 2010, 2016 (Windows)
    Posts
    4,855

    Re: randomized rows based on percentage of total rows

    Change
    Please Login or Register  to view this content.
    to
    Please Login or Register  to view this content.
    Using 24 will leave you with 23 rows - 22 values and the header row.

  35. #35
    Registered User
    Join Date
    03-22-2021
    Location
    india
    MS-Off Ver
    2016
    Posts
    1

    Re: randomized rows based on percentage of total rows

    Hi All,

    I am looking for macro for random sampling based on percentage wise . my daily inflow data is not constant some time it will be 5000 lines , 1000 lines with multiple users details.

    below is sample of my data, where i need to select each user sample of 10%

    E: If User A has 100 Entries i need to pick 10% of 100= 10 Entries in my output , next to that like user B 10% of his entreis, form whole data.

    Username amount Vid Vname Date
    a 100$ 101 A.LTD 3/22/2020
    a 100$ 102 A.LTD 3/22/2020
    a 100$ 103 A.LTD 3/22/2020
    b 100$ 104 A.LTD 3/22/2020
    b 100$ 105 b.LTDs 3/22/2020
    b 100$ 106 b.LTDs 3/22/2020
    c 100$ 107 b.LTDs 3/22/2020
    c 100$ 108 b.LTDs 3/22/2020
    c 100$ 109 b.LTDs 3/22/2020

    Could you plase help me getting macro based on those conditions.
    Attached Files Attached Files
    Last edited by msn18; 03-22-2021 at 01:12 PM. Reason: sample excel file attached

  36. #36
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Sadly... in lockdown, but based in Ireland.
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    29,882

    Re: randomized rows based on percentage of total rows

    Hi.

    It's a forum rule that you start your own thread, rather than "piggy-backing" on someone else's thread. This helps prevent massive confusion arising - which will happen if it is not clear which question is being answered. So please start your own thread and explain your own problem (make sure that, right from the start, you use a meaningful title (NOT things like "help needed" or "urgent problem"... think of the Google search terms that you would use to find the solution).

    A picture is worth 1,000 words. An Excel sheet is worth 1,000 pictures. So, prepare a SMALL sample sheet (10-20 rows, not thousands!!!). Make sure there is enough data to demonstrate your need. Make sure your desired results are shown, mock them up manually if necessary. Remember to remove ALL confidential information first!!!


    The yellow banner about sample worksheets, at the top of the screen tells you how to post a sheet here on the Forum.
    Glenn



Closed Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Delete Duplicate Rows based on one duplicating value- and keep total at the top the same.
    By jeremynorthwestern in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 11-21-2014, 01:32 AM
  2. [SOLVED] Macro to insert blank rows so that the total number of rows with data is equal to 1021
    By nsm1411 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 03-07-2013, 10:25 AM
  3. [SOLVED] Copy data to different sheet based on filter and add total rows
    By ravibemail in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-09-2012, 07:25 AM
  4. Sorting randomized question/answers within rows?
    By tsiklon in forum Excel General
    Replies: 2
    Last Post: 07-28-2011, 03:40 PM
  5. code to insert rows and add total - inserts rows, but doesn't add
    By thole in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 05-06-2009, 04:25 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1