Microsoft

Pro tip: Limit random records by group in Excel, without VBA

Limiting a random record set to a specific number of records per group isn't hard if you combine a few Excel features. Susan Harkins explains how.
Excel without VBA

Excel has a number of features that work great together, but combining them often unleashes easy-to-implement solutions to complex problems. A good example is limiting a group to a specific number of random items. For a VBA-less solution, we'll combine a function, PivotTable, and a built-in filter to create a random data set that displays only two records per group.

We'll work with the simple data set shown in Figure A. Specifically, we'll apply this technique to return only two random records for each manager. You can download the demo xlsm file or build the solution yourself. (You can't use this solution in Excel 2003 or earlier.)

Figure A

Figure A

Without using VBA, we can display only two random records for each manager.

Return random values

The first tool you need is the RAND() function. Figure B below shows the results of adding this function to the existing data. To enter this new column of functions quickly, do the following:

  1. Select C2:C12.
  2. Type the function =RAND() but don't press Enter.
  3. Press [Ctrl]+[Enter] to enter the function into all of the selected cells.
  4. Enter the label RAND() in C1 -- you'll need this label later. Don't include the preceding = symbol that you normally would.

Figure B

Figure B

Add the RAND() function to calculate a random value for each record.

At this point, you could return a set of random records by sorting the RAND() column. However, that data set won't be grouped by the managers in column A. That's where the PivotTable comes in.

Add the PivotTable

The RAND() function returns a random value for each record. Now you're ready to display this data in a PivotTable, which will allow you to easily group by the managers in column A. To insert a PivotTable based on the data, do the following:

  1. Click anywhere inside the data, and then click the Insert tab.
  2. Choose PivotTable in the Tables group.
  3. In the resulting dialog, click OK -- you don't want to change any of the defaults. You can, if you like, insert the table in the sheet with the data.
  4. Using the PivotTable frame, drag the fields as follows: Account Mgr and Claim No to Rows and RAND() to Values.

If the Claim No values appear under the managers but indented a bit (Figure C), follow the steps below:

Figure C

Figure C

Claim No values are indented.
  1. Click the contextual Design tab.
  2. From the Report Layout drop-down menu (in the Layout group), choose Show In Tabular Form. Figure D shows the new layout. As you can see, the Claim No values are on the same level as the Account Mgr values.

Figure D

Figure D

Claim No values are on the same level.

Filtering

The final step is to use a built-in filter to return only two random records for each group of managers (column A). To apply this filter, do the following:

  1. Click the Claim No field's drop-down.
  2. Choose Value Filters in the resulting menu.
  3. In the resulting submenu, choose Top 10, as shown in Figure E.
    Figure E
    Figure E
  4. In the resulting dialog, change the default value in the second control from 10 to 2, as shown in Figure F.
    Figure F
    Figure F
  5. Click OK to see the filtered results shown in Figure G.
    Figure G
    Figure G

The filtered set displays two or fewer records for each manager, but the filter used the results of the RAND() functions to choose the top two (or less) records in each group.

To improve visibility, you can hide the totals as follows:

  1. Click the contextual Design tab.
  2. Click the Subtotals drop-down (in the Layout group) and select Do Not Show Subtotals.
  3. From the Grand Totals drop-down, choose Off For Rows and Columns. Figure H shows the simple data set.
    Figure H
    Figure H

Updating

Once you've got the PivotTable filtered, you can quickly return a new random set by updating the PivotTable. Click anywhere inside the PivotTable, click the contextual Analyze tab, and click Refresh in the Data group. Or, click [Alt]+[F5].

This simple solution to a complex problem requires no VBA code or hoop-jumping. Just combine the existing features!

Send me your question about Office

I answer readers' questions when I can, but there's no guarantee. When contacting me, be as specific as possible: For instance, "Please troubleshoot my workbook and fix what's wrong" probably won't get a response, but "Can you tell me why this formula isn't returning the expected results?" might. I'm not reimbursed by TechRepublic for my time or expertise, nor do I ask for a fee from readers. You can contact me at susansalesharkins@gmail.com.

About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

6 comments
nabeak
nabeak

How can I open Excel in web browser. I am developing a programme in account program in MS EXCEL and I need a platform. I don't want my client to know I use Excel. so please help

mw00110011
mw00110011

Not sure why Excel 2003 was indicated as not able to function this way.  I applied the concepts presented here to an array of 68 records with five different "managers" and then used the "Pivot Table and Field Advanced Options to set the Top 10 to Top 2 (Right-click CLAIM heading, click "Advanced..." in PivotTable Field dialog, turn Top 10 AutoShow ON and set range to 2. 


Ilekead: I could see this as a quality control tool used to select documents or activities for review.  Otherwise just a fun exercise.


jbonsteel: As far as RANGES. I was surprised the first time I opened Excel 2007 in a store and tried to fill the entire sheet with the =rand() formula. There wasn't enough memory.  Only later did I discover the row and column count had gone astronomical (millions of rows where 2003 has about 65000). When I'm sloppy I'll reference an entire column (G:G) but usually have issues or want to go back and clean things up before other users are invited to use the sheet.


I've been holding off on diving into later versions of Excel. I think I started with Excel 4... Everything was nice and familiar until Excel 2007!. New territory to explore.


Thanks for the article Susan.

jbonsteel@coventryresrcs
jbonsteel@coventryresrcs

I have users of Excel 2013 in Windows 7 64-bit running into corrupt files frequently. The files are large with lots of VBA code. I know one problem is these users' ill-advised use of ranges in formulae that do not specify rows, just whole columns, which I demonstrated is a cause of very long wait-times when recalcing, moving, copying, etc. I have not been able to pin down the corruption issue. I look forward to improvements in Office 2013 but some problems are just poorly designed spreadsheets, as Microsoft warns. Maybe some ideas for avoiding these issues that seem to afflict Excel 2013 more than they do Excel 2007/2010 will come to light in your articles and your readers' contributions.

iIekead
iIekead

Susan, great tip.  I was trying to figure out what, in this example, you were trying to accomplish though.  Knowing why you were trying to come up with 2 random claims might help me to figure out ways to apply this to my business.

ssharkins
ssharkins

Good news on 2003 -- I didn't think it would work!

ssharkins
ssharkins

@iIekead  Auditors often ask for a specific number of random records. :) 

Editor's Picks