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 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.)
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:
- Select C2:C12.
- Type the function =RAND() but don't press Enter.
- Press [Ctrl]+[Enter] to enter the function into all of the selected cells.
- Enter the label RAND() in C1 -- you'll need this label later. Don't include the preceding = symbol that you normally would.
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:
- Click anywhere inside the data, and then click the Insert tab.
- Choose PivotTable in the Tables group.
- 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.
- 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:
Claim No values are indented.
- Click the contextual Design tab.
- 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.
Claim No values are on the same level.
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:
- Click the Claim No field's drop-down.
- Choose Value Filters in the resulting menu.
- In the resulting submenu, choose Top 10, as shown in Figure E.
- In the resulting dialog, change the default value in the second control from 10 to 2, as shown in Figure F.
- Click OK to see the filtered results shown in 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:
- Click the contextual Design tab.
- Click the Subtotals drop-down (in the Layout group) and select Do Not Show Subtotals.
- From the Grand Totals drop-down, choose Off For Rows and Columns. Figure H shows the simple data set.
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 firstname.lastname@example.org.