Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

Selecting random rows for a sampling Results

This may too easy for most of you but I am a new user and need some help.

How do I select 100 random rows from a spreadsheet that has 4529 rows of data?

or

How do I select for example every 50th row from the same sheet?

Thanks in advance fro your help.

Good evening

I have a large sheet of data, and I need to sample individual rows as part of random testing.
In column A is someones name, in column B a unique reference. In column C - is there a formula I can copy down which will display text/value that shows the row has been selected as part of a sample? For each user name in column A, I need to select TWO unique references from column B. As there are a number of different users in A, it is too tedious to do this manually. An example of the data is:

Column A Column B

I often use ms query to pouplate a spreadsheet from an external database. After doing so I also often manipulate data, add columns, formulas etc. When adding columns, the cells that were added after creating the query do not cause the "External Data" toolbar buttons to be active - you need to select a cell that was in the original query result range to see that you in fact have an external data query built into the sheet. After going back to such spreadhseets after some time elapses, and I don't necessarily remember that it includes a query, is there an easy way for me to know that the sheet does have an external query? Right now, I randomly clicking in a sampling of the cells to see if the "external Data" buttons change to active?

Thanks,SL

When I copy and paste all four columns manually by using "end home" to find the last row there is not a problem.

I had to modify code that copied formulas from row 3 of col F G H I, as a group, and pasting to last row, to copying and pasting one column at a time. Instances where there were more than 2200 rows would return runtime error "Selection Too Large" .
The following works but looks rather sloppy. Does anyone have any suggestions to make this portion more efficient? The number of rows can range from 20 to 8000. The spreadsheet is being used to generate random numbers in order to pull samples of work. The spreasheet is cleared and rows are deleted prior to each use.

original

	VB:
	
Sheets("FX566604").Select 
num_Rows = ActiveSheet.UsedRange.Rows.Count 
Range("A3").Select 
Selection.Copy 
Range("a4:a" & num_Rows).Select 
ActiveSheet.Paste 
Application.CutCopyMode = False 
Range("f3:i3").Select 
Selection.Copy 
Range("f4:i" & num_Rows).Select 
ActiveSheet.Paste 
Application.CutCopyMode = False 
Range("b1").Select 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
modified to

	VB:
	
Sheets("FX566604").Select 
num_Rows = ActiveSheet.UsedRange.Rows.Count 
Range("A3").Select 
Selection.Copy 
Range("a4:a" & num_Rows).Select 
ActiveSheet.Paste 
Application.CutCopyMode = False 
Range("f3").Select 
Selection.Copy 
Range("f4:f" & num_Rows).Select 
ActiveSheet.Paste 
Application.CutCopyMode = False 
Range("g3").Select 
Selection.Copy 
Range("g4:g" & num_Rows).Select 
ActiveSheet.Paste 
Application.CutCopyMode = False 
Range("h3").Select 
Selection.Copy 
Range("h4:h" & num_Rows).Select 
ActiveSheet.Paste 
Application.CutCopyMode = False 
Range("i3").Select 
Selection.Copy 
Range("i4:i" & num_Rows).Select 
ActiveSheet.Paste 
Application.CutCopyMode = False 
Range("b1").Select 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
As always, thank you for your help and, in this case, valuable input.

Hi there.

I have a spreadsheet which contains a list of chemicals in Column A, and a list of numbers in Column B. The chemicals are separated into groups.

I am trying to create a macro which sorts the chemicals in their groups by column B, so that the lowest number is at the top.

I have 8 groups, and the number of lines in each group may vary, so I need to make the code dynamic (if that makes sense).

I have attempted it, but am stuck. The way I thought it could be done was to search for the first group header, then save the address. Do the same for the next group header, then offset that by -1 row and 1 column, then sort using these two addresses as the range.

The first part of the code works, where I find the address for both group headers, but everything after that doesn't work.

I may be going about it in a completely random way! I would appreciate any advice.


	VB:
	
 Sort() 
     
    Dim CNMT(8) As String 
    Dim j As Integer 
    Dim fromRow As String 
    Dim toRow As String 
    Dim rng1 As Range 
    Dim rng2 As Range 
     
     
    CNMT(1) = "TPH Fractions" 
    CNMT(2) = "BTEX & MTBE" 
    CNMT(3) = "PAHs" 
    CNMT(4) = "VOCs" 
    CNMT(5) = "SVOCs" 
    CNMT(6) = "Metals" 
    CNMT(7) = "Inorganics" 
    CNMT(8) = "Pesticides" 
     
     
    For j = 1 To 8 
         
         
        fromRow = Cells.Find(What:=CNMT(j), After:=ActiveCell, LookIn:=xlFormulas, _ 
        LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ 
        MatchCase:=False, SearchFormat:=False).Address 
         
        toRow = Cells.Find(What:=CNMT(j + 1), After:=ActiveCell, LookIn:=xlFormulas, _ 
        LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ 
        MatchCase:=False, SearchFormat:=False).Address 
         
        Set rng1 = fromRow 
         
        Set rng2 = toRow 
         
        Set rng2 = rng2.Offset(-1, 1) 
         
        Range(fromRow, toRow).Sort Key1:=Range(fromRow.Offset(-1, 0)), Order1:=xlAscending, Header:=xlGuess, _ 
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ 
        DataOption1:=xlSortNormal 
         
    Next 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I can attach a sample if needed, but the data is formatted as follows

GroupHeader
Chemical number
Chemical number
Chemical number
GroupHeader
Chemical number
Chemical number

Any help or advice would be appreciated. My thanks in advance.

AL

I have a table detailing products and product options showing the options possible for each product. Each row has an option class and value(in separate columns):Color Red,Color Orange,Color Blue,Style A Style B and so on. Each product is in column. Valid options for a product are indicated with a 1, invalid options with a 0. See attached sample file.

For instance, if the product in question is Alpha, then available colors are red and blue.

For a given product I need to return a randomly generated valid option. As I want to be able to drop in additional products and options, I want to return a value without resorting to manually generating a column containing only the valid options. The real data set amounts to dozens of options types and hundreds of parts for which I want to generate test data.

Any suggetions would be welcome. Thank you for your consideration.

My data sheet contains 10,000 rows. I want to sample 100 files from this list of 10,000. Column A contains the original Reviewer ID. There are 10,000 rows of data but Column A is only made up of about 100 unique IDs.

I am looking for a macro or formula that can scan down this list of 10,000 lines and randomly select 2 rows from each unique ID it finds in Column A.

Ex. When the function finds "Mary Smith" in Column A it randomly select 2 lines from her entire range of lines which is going to be a variable range for each unique ID. Mary Smith might have 200 lines of data showing and the next unique ID (John Snyder) may only have 75 lines of data.

Any thoughts on how I could go about this in a quicker fashion than just using the Autofilters, picking each name 1 by 1, and selecting 2 files at random manually?

***Also, I want this function to actually select the rows it chooses and highlight them yellow.

I have at set of data (a few thousands rows) and I need to randomly sample a certain number of rows for testing.

Each row contains a group name (in column E) and I need to sample a different number of rows from each group. The data sheet may look something like:

Field1 Field2 Group
2 M Group2
2 T Group1
8 L Group2
3 D Group1
9 Y Group2
3 R Group4
6 C Group4

Another sheet contains the group names and numbers indicating how many rows to sample for each group:

Group Sample
Group1 50
Group2 15
Group3 0
Group4 25

My idea was to give each row a random number and use the formula suggested elsewhere on this forum to implement a conditional RANK function. This way the rows in each group would be numbered, but in random order. For each row I could then test if this number is lower than the number of rows to be sampled (using a LOOKUP).

This approach works, but with thousands of lines in the data set it takes quite a lot of time to compute, due to the extensive use of the SUMPRODUCT an RAND functions. So I would really appreciate if somebody could come up with an alternative solution to this problem (or reduce the calculations required for my current solution).

I have tried searching the forum, but haven't found any posts that quite match this problem.

Preferably the solution does not make any assumptions on the order of the rows in the set of data, and does not use macros or add-ins that may not exists in all installations of Excel 2003. I just want a new column in the data set indication if the row is selected for testing (TRUE/FALSE).

How to use Visual Basic for Applications (VBA) to change UserForms in Excel

How to Programmatically Manipulate a UserForm

How to Programmatically Create UserForms in Visual Basic for Applications

Download Attachment At Bottom For Live Links

The Hey, Scripting Guy! Archive: Microsoft Office

Microsoft Excel
β€’ How Can I Import a Fixed-Width Data File into Microsoft Excel?
β€’ How Can I Sort a Spreadsheet By Cell Color?
β€’ How Can I Check the Value of a Specific Cell in a Bunch of Excel Spreadsheets?
β€’ How Can I Mark the First Occurrence of a Number in an Excel Spreadsheet?
β€’ How Can I Read Custom Summary Information Properties for an Excel File?
β€’ How Can I Change the Color of a Spreadsheet Cell Based on a Range of Values?
β€’ How Can I Import Multiple Worksheets into an Access Database?
β€’ How Can I Search For Values in an Excel Worksheet?
β€’ How Can I Cut a Row From One Excel Spreadsheet and Paste That Row Into Another Spreadsheet?
β€’ How Can I Determine the Value of the Last Cell in an Excel Range?
β€’ How Can I Convert an Excel Serial Number to a Date and Then Back?
β€’ How Can I Insert New Rows Above the Last Row in an Excel Spreadsheet?
β€’ How Can I Copy Data from Excel to PowerPoint?
β€’ How Can I Retrieve Hyperlink Information From an Excel Spreadsheet?
β€’ How Can I Insert Blank Rows (and Formulas) Into an Excel Worksheet?
β€’ How Can I Update and Then Break All the Links in an Excel Spreadsheet?
β€’ How Can I Open All the Excel Spreadsheets in a Folder and Run a Specified Macro Found in Each of Those Spreadsheets?
β€’ How Can I List the Addresses in My Sent Items Folder in an Excel Spreadsheet?
β€’ How Can I Add a Background Picture to an Excel Worksheet?
β€’ How Can I Replace Numeric Values in an Excel Spreadsheet?
β€’ How Can I Copy Data From One Spreadsheet to Another?
β€’ How Can I Replace Text in an Excel Spreadsheet?
β€’ How Can I Set the Print Area in an Excel Spreadsheet?
β€’ How Can I Delete Specified Rows in an Excel Spreadsheet?
β€’ How Can I Color Every Other Row in an Excel Spreadsheet?
β€’ How Can I Copy Selected Columns From a CSV File to an Excel File?
β€’ How Can I Change the Background Color of Spreadsheet Rows That Have a Date in a Specified Cell?
β€’ How Can I Export an HTA Table to Excel?
β€’ How Can I Delete All the Worksheets in a Spreadsheet Except for the First Worksheet?
β€’ How Can I Assign a Background Color to Cells in a Spreadsheet and Then β€œSum” Those Cells?
β€’ How Can I Sort a Row in an Excel Spreadsheet?
β€’ How Can I Remove the Password When Opening an Excel Spreadsheet?
β€’ How Can I Import an Excel Spreadsheet Into an Access Database?
β€’ How Can I Save a Table in an Access Database as a Spreadsheet?
β€’ How Can I Use a Blank Row to Separate Data in an Excel Spreadsheet?
β€’ How Can I Specify the Number of Decimal Places to Display in an Excel Spreadsheet?
β€’ How Can I Configure Excel to Autosave Every 5 Minutes?
β€’ How Can I Save an Excel Chart as a Picture?
β€’ How Can I Change the Font Color in Excel If a Specified Condition is Met?
β€’ How Can I Convert an Excel Spreadsheet to XML?
β€’ How Can I Set the Default File Path in Excel to a User’s Home Directory?
β€’ How Can I Compare a List of Names in One Excel Column to a List of Names in Another Column?
β€’ How Can I Convert a Tilde-Delimited File to Microsoft Excel Format?
β€’ How Can I Search Active Directory for User Names Stored in an Excel Spreadsheet?
β€’ How Can I Add a Total Row to an Excel Spreadsheet?
β€’ How Can I Create a Custom Date Format in Microsoft Excel?
β€’ How Can I Keep the Screen From Updating While Running an Excel Script?
β€’ How Can I Use Information in an Excel Spreadsheet to Rename a Set of Folders?
β€’ How Can I Determine the Background Color of a Spreadsheet Cell?
β€’ How Can I Make the First Character in a Cell Uppercase and All the Other Characters Lowercase?
β€’ How Can I Copy a Worksheet to a New Spreadsheet?
β€’ How Can I Use Windows PowerShell to Automate Microsoft Excel?
β€’ How Can I Tell If a Specified Worksheet Exists in an Excel Workbook?
β€’ How Can I Add a COUNTIF Formula to an Excel Spreadsheet?
β€’ How Can I Copy All the Comments From an Excel Worksheet to a Word Document?
β€’ How Can I Change All the Lowercase Letters in an Excel Worksheet to Uppercase Letters?
β€’ How Can I Create a New Excel Spreadsheet at Midnight Each Night?
β€’ How Can I Select a Column of Data in Excel and Then Paste that Data into a Text File?
β€’ How Can I Convert a Number to a Date in Excel?
β€’ How Can I Locate and Replace Information for a Specific Item in a Spreadsheet?
β€’ How Can I Save an Excel Spreadsheet, and Then Save a Copy as HTML?
β€’ How Can I Format an Excel Spreadsheet So It Retains Leading Zeroes?
β€’ How Can I Determine the Last Row in an Excel Spreadsheet?
β€’ How Can I Remove All the Formatting from an Excel Spreadsheet?
β€’ How Can I Change the Footer in an Excel Spreadsheet?
β€’ How Can I Add Additional Worksheets to an Excel Workbook?
β€’ How Can I Sort Worksheets in a Workbook?
β€’ How Can I Import a Fixed-Width File into Microsoft Excel?
β€’ How Can I Add a Hyperlink to an Excel Spreadsheet?
β€’ How Can I Build an Array from a Column of Data in Excel?
β€’ How Can I Copy Column C of One Worksheet to Column A of a Second Worksheet?
β€’ How Can I Center Text in an Excel Cell?
β€’ How Can I Change the Background Color of a Cell Depending on the Date?
β€’ How Can I Delete Data from a Spreadsheet Yet Keep All the Formatting?
β€’ How Can I Open an Excel Workbook and Retrieve the Names of All the Worksheets?
β€’ How Can I Tell if an Excel Workbook is Open and, If It Isn’t, Open It?
β€’ How Can I Insert a Column into a Spreadsheet?
β€’ How Can I Save a Single Excel Worksheet to a CSV File?
β€’ How Can I Password-Protect an Excel Spreadsheet?
β€’ How Can I Make Changes to and Then Re-Save an Existing Excel Spreadsheet?

Microsoft Word
β€’ How Can I Randomly Assign a Font to Characters in a Word Document?
β€’ How Can I Find a Word in a Document and Change the Background Color of the Paragraph Where That Word Appears?
β€’ How Can I Count the Number of Sentences and Paragraphs in a Word Document?
β€’ How Can I Extract Specific Information From a Word Document and Then Use That Information to Rename the Document?
β€’ How Can I Tally All the Items in a Microsoft Word Document?
β€’ How Can I Reset the Revision Number of a Word Document to 1?
β€’ How Can I Retrieve Field Values in a Microsoft Word Document?
β€’ How Can I Change the Paragraph Case in Microsoft Word?
β€’ How Can I Insert a Manual Line Break into a Microsoft Word Document?
β€’ How Can I Search For and Modify Formatted Text in a Word Document?
β€’ How Can I Search For Red Text in a Microsoft Word Document?
β€’ How Can I Change the Default File Save Format in Microsoft Word?
β€’ How Can I Put the File Name in the Footer of a Microsoft Word Document?
β€’ How Can I Add a Page X of Y Footer to a Microsoft Word Document?
β€’ How Can I Save Each Paragraph in a Word Document as a Database Record?
β€’ How Can I Replace a Specified Font in a Microsoft Word Document?
β€’ How Can I Apply a Theme to a Microsoft Word Document?
β€’ How Can I Add the Last-Saved Date to the Footer of a Microsoft Word Document?
β€’ How Can I Insert a Date Field in Word?
β€’ How Can I Add a New Item to the Microsoft Word AutoCorrect List?
β€’ How Can I Clear All the Formatting From a Microsoft Word Document?
β€’ How Can I Change the Default Highlight Color for a Microsoft Word Document?
β€’ How Can I Run a Macro After Opening Up Word?
β€’ How Can I Center-Align a Picture in a Word Document?
β€’ How Can I Change an Existing Hyperlink in a Microsoft Word Document?
β€’ How Can I Add a Blank Line Between the Existing Paragraphs in a Word Document?
β€’ How Can I Insert Text Into an Existing Microsoft Word Bookmark?
β€’ How Can I Get a Total Page Count for All the Word Documents in a Folder?
β€’ How Can I Set the Document Orientation in Microsoft Word to Landscape?
β€’ How Can I Replace Text in a Microsoft Word Document?
β€’ How Can I Search For (and Reformat) Highlighted Text in a Word Document?
β€’ How Can I Get a List of the Unique Words Used in a Microsoft Word Document?
β€’ How Can I Get a List of Available Metadata for Microsoft Office Documents?
β€’ How Can I Add Multiple Tables to a Word Document?
β€’ How Can I Search a Word Document for All the Words in Double Brackets?
β€’ How Can I Add Centered Page Numbers to the Footer of a Word Document?
β€’ How Can I Create a Table and Fill the First Column With a Range of Dates?
β€’ How Can I Rename a Word Document Using the First Three Characters in That Document?
β€’ How Can I Search for and Highlight Words in a Microsoft Word Document?
β€’ How Can I Right-Align a Single Column in a Word Table?
β€’ How Can I Boldface a Specific Word Throughout a Microsoft Word Document?
β€’ How Can I Change the Font Name and Size for an Entire Word Document?
β€’ How Can I Set Word’s Revision View Mode to Final?
β€’ How Can I Insert Multiple Files Into a Word Document, Putting a Page Break Between Each File?
β€’ How Can I Set Word’s Default File Location to be the User’s Home Directory?
β€’ How Can I Open Word with the Cursor Positioned at the Start of a Specified Line?
β€’ How Can I Change File Locations for Microsoft Word?
β€’ How Can I Insert a Symbol into a Word Document?
β€’ How Can I Hide a Specific Toolbar in Microsoft Word?
β€’ How Can I Extract Word Paragraphs That Use a Specific Style?
β€’ How Can I Insert Files into a Word Document?
β€’ How Can I Convert 1,000 .RTF Files to Word Documents?
β€’ How Can I Add a Hyperlink to a Word Document?
β€’ How Can I Put the User Name into the Footer of a Microsoft Word Document?
β€’ How Can I Determine Which Version of Word is Installed on a Computer?
β€’ How Can I Save Word Documents as Text Files By Using a Script?

Microsoft Access
β€’ How Can I Import Multiple Worksheets into an Access Database?
β€’ How Can I Add a Record to a Database Using Windows PowerShell?
β€’ How Can I Delete a Set of Records From an Access Database?
β€’ How Can I Be Notified if an Access Database File Exceeds a Specific Size?
β€’ How Can I Retrieve the Field Size and a Sample Record For All the Tables and Fields in an Access Database?
β€’ How Can I List All the Fields and Data Types in an Access Database?
β€’ How Can I Append a Value to a Specified Field For Each Record in a Database?
β€’ How Can I Add the Contents of a Group of Text Files to an Access Database?
β€’ How Can I Import a .CSV File into an Access Database?
β€’ How Can I List All the Access Database Files on a Computer?
β€’ How Can I Print a Microsoft Access Report?
β€’ How Can I Compact an Access Database?
β€’ How Can I Use Windows PowerShell to Pull Records From a Microsoft Access Database?
β€’ How Can I List All the Tables in an Access Database?
β€’ How Can I Determine Which Version of Access was Used to Create a Database?
β€’ How Can I Delete a Table from a Jet Database?

Microsoft Outlook
β€’ How Can I Schedule a Meeting Each Monday Through Friday For Two Weeks?
β€’ How Can I Sort Items Retrieved From a Microsoft Outlook Folder?
β€’ How Can I Set a Reminder on All My Outlook Appointments?
β€’ How Can I Schedule an All-Day Event in Microsoft Outlook?
β€’ How Can I Run a Script Any Time Outlook is Started?
β€’ How Can I Determine the Age, in Years, of an Outlook Contact?
β€’ How Can I Save the Attachments for All My New Outlook Messages?
β€’ How Can I Convert an Extension Number to an Actual Phone Number?
β€’ How Can I Create a New Folder in Microsoft Outlook?
β€’ How Can I List the Addresses in My Sent Items Folder in an Excel Spreadsheet?
β€’ How Can I Get a List of Appointments for a Specific Month?
β€’ How Can I Save Emails That Are More Than One Month Old?
β€’ How Can I Create a New Outlook Distribution List Based On the Membership of an Active Directory Group?
β€’ How Can I Save All My Contacts as VCards?
β€’ How Can I Delete Unread Emails That Are More Than 6 Months Old?
β€’ How Can I Randomly Select an Email From an Outlook Mail Folder?
β€’ How Can I List All the Meetings Scheduled By a Specified Person?
β€’ How Can I Filter Outlook Messages By Email Address?
β€’ How Can I List All the Members of a Microsoft Outlook Distribution List?
β€’ How Can I Determine the Follow-Up Status of Outlook Emails?
β€’ How Can I Tell If Any of My Contacts Have a Birthday This Month?
β€’ How Can I Delete All the Messages in My Sent Items Folder?
β€’ How Can I Start Outlook If It Isn’t Already Running?
β€’ How Can I Connect to the Junk Mail Folder in Outlook?
β€’ How Can I Get Access to a Mail Folder That Isn’t a Subfolder of My Outlook Inbox?
β€’ How Can I Get a List of All the Senders’ Email Addresses in an Outlook Folder?
β€’ How Can I Delete All the Email Sent From a Specific Person?
β€’ How Can I Write a Script That Accesses All the Subfolders in My Outlook Inbox?
β€’ How Can I Get Total Size and Number of Items in an Outlook Folder?
β€’ How Can I Convert an Outlook Email Message into a Text File?
β€’ How Can I Get a List of All the .PST Files on a Computer?

Microsoft PowerPoint
β€’ How Can I Retrieve the User Name and User Initials From Microsoft PowerPoint?
β€’ How Can I Copy Data from Excel to PowerPoint?
β€’ How Can I Apply a New Template to a PowerPoint Presentation?
β€’ How Can I Configure PowerPoint to Print Handouts Instead of Slides?
β€’ How Can I Run a PowerPoint Slide Show From a Script?

Other Office Tasks
β€’ How Can I Use Windows PowerShell to Look at All the Microsoft Office Documents in a Folder?
β€’ How Can I Change the User Information in Microsoft Office?

Download Attachment At Bottom For Live Links

The Hey, Scripting Guy! Archive: Microsoft Office

Microsoft Excel
• How Can I Import a Fixed-Width Data File into Microsoft Excel?
• How Can I Sort a Spreadsheet By Cell Color?
• How Can I Check the Value of a Specific Cell in a Bunch of Excel Spreadsheets?
• How Can I Mark the First Occurrence of a Number in an Excel Spreadsheet?
• How Can I Read Custom Summary Information Properties for an Excel File?
• How Can I Change the Color of a Spreadsheet Cell Based on a Range of Values?
• How Can I Import Multiple Worksheets into an Access Database?
• How Can I Search For Values in an Excel Worksheet?
• How Can I Cut a Row From One Excel Spreadsheet and Paste That Row Into Another Spreadsheet?
• How Can I Determine the Value of the Last Cell in an Excel Range?
• How Can I Convert an Excel Serial Number to a Date and Then Back?
• How Can I Insert New Rows Above the Last Row in an Excel Spreadsheet?
• How Can I Copy Data from Excel to PowerPoint?
• How Can I Retrieve Hyperlink Information From an Excel Spreadsheet?
• How Can I Insert Blank Rows (and Formulas) Into an Excel Worksheet?
• How Can I Update and Then Break All the Links in an Excel Spreadsheet?
• How Can I Open All the Excel Spreadsheets in a Folder and Run a Specified Macro Found in Each of Those Spreadsheets?
• How Can I List the Addresses in My Sent Items Folder in an Excel Spreadsheet?
• How Can I Add a Background Picture to an Excel Worksheet?
• How Can I Replace Numeric Values in an Excel Spreadsheet?
• How Can I Copy Data From One Spreadsheet to Another?
• How Can I Replace Text in an Excel Spreadsheet?
• How Can I Set the Print Area in an Excel Spreadsheet?
• How Can I Delete Specified Rows in an Excel Spreadsheet?
• How Can I Color Every Other Row in an Excel Spreadsheet?
• How Can I Copy Selected Columns From a CSV File to an Excel File?
• How Can I Change the Background Color of Spreadsheet Rows That Have a Date in a Specified Cell?
• How Can I Export an HTA Table to Excel?
• How Can I Delete All the Worksheets in a Spreadsheet Except for the First Worksheet?
• How Can I Assign a Background Color to Cells in a Spreadsheet and Then “Sum” Those Cells?
• How Can I Sort a Row in an Excel Spreadsheet?
• How Can I Remove the Password When Opening an Excel Spreadsheet?
• How Can I Import an Excel Spreadsheet Into an Access Database?
• How Can I Save a Table in an Access Database as a Spreadsheet?
• How Can I Use a Blank Row to Separate Data in an Excel Spreadsheet?
• How Can I Specify the Number of Decimal Places to Display in an Excel Spreadsheet?
• How Can I Configure Excel to Autosave Every 5 Minutes?
• How Can I Save an Excel Chart as a Picture?
• How Can I Change the Font Color in Excel If a Specified Condition is Met?
• How Can I Convert an Excel Spreadsheet to XML?
• How Can I Set the Default File Path in Excel to a User’s Home Directory?
• How Can I Compare a List of Names in One Excel Column to a List of Names in Another Column?
• How Can I Convert a Tilde-Delimited File to Microsoft Excel Format?
• How Can I Search Active Directory for User Names Stored in an Excel Spreadsheet?
• How Can I Add a Total Row to an Excel Spreadsheet?
• How Can I Create a Custom Date Format in Microsoft Excel?
• How Can I Keep the Screen From Updating While Running an Excel Script?
• How Can I Use Information in an Excel Spreadsheet to Rename a Set of Folders?
• How Can I Determine the Background Color of a Spreadsheet Cell?
• How Can I Make the First Character in a Cell Uppercase and All the Other Characters Lowercase?
• How Can I Copy a Worksheet to a New Spreadsheet?
• How Can I Use Windows PowerShell to Automate Microsoft Excel?
• How Can I Tell If a Specified Worksheet Exists in an Excel Workbook?
• How Can I Add a COUNTIF Formula to an Excel Spreadsheet?
• How Can I Copy All the Comments From an Excel Worksheet to a Word Document?
• How Can I Change All the Lowercase Letters in an Excel Worksheet to Uppercase Letters?
• How Can I Create a New Excel Spreadsheet at Midnight Each Night?
• How Can I Select a Column of Data in Excel and Then Paste that Data into a Text File?
• How Can I Convert a Number to a Date in Excel?
• How Can I Locate and Replace Information for a Specific Item in a Spreadsheet?
• How Can I Save an Excel Spreadsheet, and Then Save a Copy as HTML?
• How Can I Format an Excel Spreadsheet So It Retains Leading Zeroes?
• How Can I Determine the Last Row in an Excel Spreadsheet?
• How Can I Remove All the Formatting from an Excel Spreadsheet?
• How Can I Change the Footer in an Excel Spreadsheet?
• How Can I Add Additional Worksheets to an Excel Workbook?
• How Can I Sort Worksheets in a Workbook?
• How Can I Import a Fixed-Width File into Microsoft Excel?
• How Can I Add a Hyperlink to an Excel Spreadsheet?
• How Can I Build an Array from a Column of Data in Excel?
• How Can I Copy Column C of One Worksheet to Column A of a Second Worksheet?
• How Can I Center Text in an Excel Cell?
• How Can I Change the Background Color of a Cell Depending on the Date?
• How Can I Delete Data from a Spreadsheet Yet Keep All the Formatting?
• How Can I Open an Excel Workbook and Retrieve the Names of All the Worksheets?
• How Can I Tell if an Excel Workbook is Open and, If It Isn’t, Open It?
• How Can I Insert a Column into a Spreadsheet?
• How Can I Save a Single Excel Worksheet to a CSV File?
• How Can I Password-Protect an Excel Spreadsheet?
• How Can I Make Changes to and Then Re-Save an Existing Excel Spreadsheet?

Microsoft Word
• How Can I Randomly Assign a Font to Characters in a Word Document?
• How Can I Find a Word in a Document and Change the Background Color of the Paragraph Where That Word Appears?
• How Can I Count the Number of Sentences and Paragraphs in a Word Document?
• How Can I Extract Specific Information From a Word Document and Then Use That Information to Rename the Document?
• How Can I Tally All the Items in a Microsoft Word Document?
• How Can I Reset the Revision Number of a Word Document to 1?
• How Can I Retrieve Field Values in a Microsoft Word Document?
• How Can I Change the Paragraph Case in Microsoft Word?
• How Can I Insert a Manual Line Break into a Microsoft Word Document?
• How Can I Search For and Modify Formatted Text in a Word Document?
• How Can I Search For Red Text in a Microsoft Word Document?
• How Can I Change the Default File Save Format in Microsoft Word?
• How Can I Put the File Name in the Footer of a Microsoft Word Document?
• How Can I Add a Page X of Y Footer to a Microsoft Word Document?
• How Can I Save Each Paragraph in a Word Document as a Database Record?
• How Can I Replace a Specified Font in a Microsoft Word Document?
• How Can I Apply a Theme to a Microsoft Word Document?
• How Can I Add the Last-Saved Date to the Footer of a Microsoft Word Document?
• How Can I Insert a Date Field in Word?
• How Can I Add a New Item to the Microsoft Word AutoCorrect List?
• How Can I Clear All the Formatting From a Microsoft Word Document?
• How Can I Change the Default Highlight Color for a Microsoft Word Document?
• How Can I Run a Macro After Opening Up Word?
• How Can I Center-Align a Picture in a Word Document?
• How Can I Change an Existing Hyperlink in a Microsoft Word Document?
• How Can I Add a Blank Line Between the Existing Paragraphs in a Word Document?
• How Can I Insert Text Into an Existing Microsoft Word Bookmark?
• How Can I Get a Total Page Count for All the Word Documents in a Folder?
• How Can I Set the Document Orientation in Microsoft Word to Landscape?
• How Can I Replace Text in a Microsoft Word Document?
• How Can I Search For (and Reformat) Highlighted Text in a Word Document?
• How Can I Get a List of the Unique Words Used in a Microsoft Word Document?
• How Can I Get a List of Available Metadata for Microsoft Office Documents?
• How Can I Add Multiple Tables to a Word Document?
• How Can I Search a Word Document for All the Words in Double Brackets?
• How Can I Add Centered Page Numbers to the Footer of a Word Document?
• How Can I Create a Table and Fill the First Column With a Range of Dates?
• How Can I Rename a Word Document Using the First Three Characters in That Document?
• How Can I Search for and Highlight Words in a Microsoft Word Document?
• How Can I Right-Align a Single Column in a Word Table?
• How Can I Boldface a Specific Word Throughout a Microsoft Word Document?
• How Can I Change the Font Name and Size for an Entire Word Document?
• How Can I Set Word’s Revision View Mode to Final?
• How Can I Insert Multiple Files Into a Word Document, Putting a Page Break Between Each File?
• How Can I Set Word’s Default File Location to be the User’s Home Directory?
• How Can I Open Word with the Cursor Positioned at the Start of a Specified Line?
• How Can I Change File Locations for Microsoft Word?
• How Can I Insert a Symbol into a Word Document?
• How Can I Hide a Specific Toolbar in Microsoft Word?
• How Can I Extract Word Paragraphs That Use a Specific Style?
• How Can I Insert Files into a Word Document?
• How Can I Convert 1,000 .RTF Files to Word Documents?
• How Can I Add a Hyperlink to a Word Document?
• How Can I Put the User Name into the Footer of a Microsoft Word Document?
• How Can I Determine Which Version of Word is Installed on a Computer?
• How Can I Save Word Documents as Text Files By Using a Script?

Microsoft Access
• How Can I Import Multiple Worksheets into an Access Database?
• How Can I Add a Record to a Database Using Windows PowerShell?
• How Can I Delete a Set of Records From an Access Database?
• How Can I Be Notified if an Access Database File Exceeds a Specific Size?
• How Can I Retrieve the Field Size and a Sample Record For All the Tables and Fields in an Access Database?
• How Can I List All the Fields and Data Types in an Access Database?
• How Can I Append a Value to a Specified Field For Each Record in a Database?
• How Can I Add the Contents of a Group of Text Files to an Access Database?
• How Can I Import a .CSV File into an Access Database?
• How Can I List All the Access Database Files on a Computer?
• How Can I Print a Microsoft Access Report?
• How Can I Compact an Access Database?
• How Can I Use Windows PowerShell to Pull Records From a Microsoft Access Database?
• How Can I List All the Tables in an Access Database?
• How Can I Determine Which Version of Access was Used to Create a Database?
• How Can I Delete a Table from a Jet Database?

Microsoft Outlook
• How Can I Schedule a Meeting Each Monday Through Friday For Two Weeks?
• How Can I Sort Items Retrieved From a Microsoft Outlook Folder?
• How Can I Set a Reminder on All My Outlook Appointments?
• How Can I Schedule an All-Day Event in Microsoft Outlook?
• How Can I Run a Script Any Time Outlook is Started?
• How Can I Determine the Age, in Years, of an Outlook Contact?
• How Can I Save the Attachments for All My New Outlook Messages?
• How Can I Convert an Extension Number to an Actual Phone Number?
• How Can I Create a New Folder in Microsoft Outlook?
• How Can I List the Addresses in My Sent Items Folder in an Excel Spreadsheet?
• How Can I Get a List of Appointments for a Specific Month?
• How Can I Save Emails That Are More Than One Month Old?
• How Can I Create a New Outlook Distribution List Based On the Membership of an Active Directory Group?
• How Can I Save All My Contacts as VCards?
• How Can I Delete Unread Emails That Are More Than 6 Months Old?
• How Can I Randomly Select an Email From an Outlook Mail Folder?
• How Can I List All the Meetings Scheduled By a Specified Person?
• How Can I Filter Outlook Messages By Email Address?
• How Can I List All the Members of a Microsoft Outlook Distribution List?
• How Can I Determine the Follow-Up Status of Outlook Emails?
• How Can I Tell If Any of My Contacts Have a Birthday This Month?
• How Can I Delete All the Messages in My Sent Items Folder?
• How Can I Start Outlook If It Isn’t Already Running?
• How Can I Connect to the Junk Mail Folder in Outlook?
• How Can I Get Access to a Mail Folder That Isn’t a Subfolder of My Outlook Inbox?
• How Can I Get a List of All the Senders’ Email Addresses in an Outlook Folder?
• How Can I Delete All the Email Sent From a Specific Person?
• How Can I Write a Script That Accesses All the Subfolders in My Outlook Inbox?
• How Can I Get Total Size and Number of Items in an Outlook Folder?
• How Can I Convert an Outlook Email Message into a Text File?
• How Can I Get a List of All the .PST Files on a Computer?

Microsoft PowerPoint
• How Can I Retrieve the User Name and User Initials From Microsoft PowerPoint?
• How Can I Copy Data from Excel to PowerPoint?
• How Can I Apply a New Template to a PowerPoint Presentation?
• How Can I Configure PowerPoint to Print Handouts Instead of Slides?
• How Can I Run a PowerPoint Slide Show From a Script?

Other Office Tasks
• How Can I Use Windows PowerShell to Look at All the Microsoft Office Documents in a Folder?
• How Can I Change the User Information in Microsoft Office?

Hello everyone,

I have had too many of my excel troubles solved by the good members of this thread so I feel compelled to contribute my accidental excel accomplishment for which I could not find a solution online (sorry, if I just missed it) - obtaining random samples in excel without duplicates.

The purpose of the attached file is to obtain a true random sample of unique results from any data population of size N. This can’t be done in Excel’s data analysis tool-pack (Data tab > Data Analysis > Random number generation) because in that output, values can be repeated in sample extract of sufficient size proportional to total population, i.e., duplicates appear in random sample.

This file is set to work for a data population of 28 comprising of 26 letters of the English alphabet and 2 intentionally inserted duplicates (letters J and Q) to prove that duplicates will not be included in random sample obtained and that the formulas work as intended. Therefore, the maximum number of unique results that can be obtained in a random sample is 26 (28 letters – 2 duplicates).

1. To refresh random samples of size n=1 to n=26, press F9.

2. To use this file for a larger population:

a. Insert lines as needed below index value 2 since formulas are different for row containing index value 1
b. Copy your selection criteria (names, numbers etc.) in column B
c. Copy formulas down in lines inserted.
d. Make sure numbers in column A are in sequential order.

3. To obtain a sample size larger than 26, simply enter the desired size in row 1 of any column and delete the remaining sample columns.

I hope you’d find this file useful. Your comments / corrections are welcome!

Thanks!

Hello everyone,

Attached at the bottom of this post is an Excel add-in – Randomizer – which when added to the Quick Access Toolbar and used after having selected a specific row/column randomizes its content.

The add-in worked great for its purpose in the past, however for the kind of sheets I am now mostly dealing with on a deal basis I am encountering this frustrating issue where, since I have multiple rows in columns filled with a specific color, the add-in randomizes all the data within the column and on multiple times may have many color-filled rows following each other after the randomizing process.

I have attached a sample spreadsheet below as well to illustrate this issue, simply add the add-in to your Excel version, select the first column, and click the randomize button 1 or 2 times and you’ll notice that on many occasions multiple color-filled rows are now presented one after another which really is an unwanted outcome.

It'd be best to have at least one normal non-color-filled row between each color-filled row at the minimum.

Any line of code or specific modification can be made to prevent this issue?

Thanks a lot in advance,
Eric

Back again to ask what I hope will be a simple question to someone (Thanks again to Marcol for showing me earlier an easy way to remove an annoyance from the original file).

In the attached sample workheet i want to randomly select 30 products and copy the result to a separate worksheet. So basically I need a macro (or something I can record to a macro) to randomly select 30 codes from column C and then copy the entire rows to a separate spreadsheet.
As usual I will be most thankful for any guidance

Hi,

I'm posting this thread as part of a random sampling requirement for a process. I've posted in this forum earlier for this and the related thread link are as below:

Filter & Flag as Sample
Randomly Select Data in Excel

As requested by Marvin, i'm posting this thread for a code review, probably, i can explain my code line by line if required. I've posted my code and sample workbooks in the above threads. The main idea is to randomly select rows of data for each user one after another. The records of each user are filtered out and from the filtered set of rows, i need to choose a certain number (say x% of total visible rows) of rows as samples and mark them.

I need code that will look for a number in cell C1 and then it starts at C9
selecting cells to copy until it sees a cell with a number larger than what is in C1 and stops.
i need code for this since the number in C1 is always changing

Example: if there is an 18 in C1 it selects all cells that are >0 and <19

these cells are always sorted 123456........36 they are not random in order like 1 17 6 12 18 4 3

I have attached a sample file so you can better understand what i am looking for. Thanks

I am trying to create a function (?) that will allow me to get a specific
number of randomly selected cells out of a range. I know this is available,
but I can't seem to get it to do the following:

BACKGROUND- I have a spreadsheet that has employee names for the column
titles, and training module names for each row. The connecting cells
indicate the date which these training modules were completed [ex- Joe Shmoe
(column title) was training on Beancounting (row title) on 7/7/77
(coordinating cell data).]

PROBLEM- I need to randomly select 315 cells out of a range of
employees/training modules (for example, range B2-AF247). I know this can be
accomplished through the analysis tool, but I can't seem to get it to simply
HIGHLIGHT THE RANDOMLY SELECTED CELLS within the actual spreadsheet.
Instead, it seems to only pull the actual data out of the cells and throw it
somewhere else, which prevents me from having any reference as to the
employee and training module.

SOLUTION- ???

Hello,
Life has presented me with an opportunity to teach some math to
children/young adults with challenging backgrounds. Many have come a long way
and want to pursue more challenging material. I want to teach them more about
probability and want to design an Excel simulation. My idea was to get them
to work out how variables altered probability.

Specifically I am looking for help to develop a game where students would
alter variables "probability" (expressed as a ratio or percentage) of picking
a colored ball from a bag. So if they said there were three red balls, 4
blue, six white etc. the simulation would calculate the probability of each
ball. And I'd like to show them the impact of selecting "out" or "in" where
"out" is keeping the ball (and all others selected) out of the bag for
subsequent turns and "in" is putting the ball back in the bag. Then a random
generator would be used to "pick" the next ball and excel would display a
cell of the matching ball color. I then want excel to show some statistics
like comparison of theoretical probability to outcome (to show sample size
variance), the number of times in a row a particular ball was drawn (as in a
streak).

I know the math but am a baby beginner at Excel programming. It looks like
from the help that a Binomial distribution is the function with a rounded
random function for selection. Am I on the right track? Any ideas on "out"
and "in"?

Thanks very much for your help

Rob

Hi all.

I am double-posting this question because I am in dire need of a solution. Here is the link to my other post: http://www.mrexcel.com/forum/showthr...41#post2819941

I have a workbook that contains the following code used to select a sample from another workbook:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim nResult As Long
'With Workbooks(fName & ".csv").Sheets(sName)
Set sRange = Range("EG2:EG" & Cells(Rows.Count, 1).End(xlUp).Row)
If Target.Cells.Count > 1 _
Or Intersect(Target, Range("EG2:EG" & Cells(Rows.Count, 1).End(xlUp).Row)) Is Nothing Then Exit Sub
        If UCase(Cells(Target.Row, "EE")) <> "PAID" Then Exit Sub
        
                If Target = vbNullString Then
                    Target = "RK"
                Else
                    Target = vbNullString
                End If
Range("EK2") = Application.WorksheetFunction.CountIf(sRange, "RK")
If Range("EK2") >= Range("EJ2") Then

nResult = MsgBox( _
Prompt:="You have already selected the minimum Risk Based Claims.  Do you want you want to select the Random Claims
now?", _
Buttons:=vbYesNo)
If nResult = vbNo Then
Exit Sub
Else
Call Random_Claims_Selection
Call CopySelectedClaims
MsgBox Application.WorksheetFunction.Sum(Range("EK2"), Range("EL2")) & " (" &
Range("EK2") & " Risk Based and " & Range("EL2") & " Random) Claims have been
selected for auditing."
End If
End If
Application.ScreenUpdating = True
End Sub

The code is in WorkbookA but the data to be selected is in WorksheetB.

The code works fine when the data is and the code are in the same workbook but not when they are in different workbooks. It is not possible to have them in the same workbook.

Can anyone help me get it working, please?

Thank you,
Gos-C

Hi All,

I need help in excel, I've attached a workbook for reference I need a macro that,

Will copy the entire row in sheet 2 to sheet 3 given the criteria(Name and the sample size) in sheet 1.
The sample size must be random among the entry of the Analyst

Than kyou

Hello:

I have attached a sample workbook. Upon opening the workbook, the code to limit the scroll area does not seem to have an affect. However, after randomly selecting a few different cells, the code kicks in and limits the scroll area. Have I placed the code incorrectly?

This code borrowed from a previous Dave Hawley post.


	VB:
	
 Range) 
     
    Dim LastColumn As Integer 
    Dim LastRow As Long 
     
    If WorksheetFunction.CountA(Cells) > 0 Then 
         
         'Search for any entry, by searching backwards by Rows.
        LastRow = Cells.Find(What:="*", After:=[A1], _ 
        SearchOrder:=xlByRows, _ 
        SearchDirection:=xlPrevious).Row 
        If LastRow  65536 Then LastRow = LastRow + 1 
         
         'Search for any entry, by searching backwards by Columns.
        LastColumn = Cells.Find(What:="*", After:=[A1], _ 
        SearchOrder:=xlByColumns, _ 
        SearchDirection:=xlPrevious).Column 
        If LastColumn  256 Then LastColumn = LastColumn + 1 
         
        Me.ScrollArea = Range(Cells(1, 1), Cells(LastRow, LastColumn)).Address 
    Else 
        Me.ScrollArea = "" 
    End If 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
All help is greatly appreciated.


No luck finding an answer? You could always try Google.