The other day, I got a text message (SMS) from one of our readers. It read,
So today, let us learn a very easy trick to select random sample from your data.
Lets take a look at the data
Since the text message has no actual data, I made up this.
Now, if you just want to select any 10 (or x number of) random items from this list, then your job would very simple.
- Shuffle (or randomly arrange) this list
- Just pick first 10 items
But our problem is to get 2 random samples per user.
Selecting random samples from data
Follow below steps.
- Add an extra column and fill it with =RAND() formula. This generates random fraction between 0 and 1.
- Create a pivot table from this data (tutorial: How to create a pivot table?)
- Add User ID & Case ID as Row labels and Random as value field.
- Click on the filter icon on Case ID column, choose Value filter > Top 10
- Filter for top 2 random values. (related: Filter top 10 values in pivot tables – how to?)
- Adjust report layout (Table layout, no sub-totals, no grand totals)
- Done!
To see new samples
Just select any cell in the pivot table, press ALT+F5. Your pivot table will be refreshed and you get new samples.
That is just easy and awesome!
Download Example Workbook
Click here to download the example file. Refresh the pivot table (ALT+F5) to see fresh samples.
Do you sample your data?
Drawing samples, running experiments, analyzing results are life breath for many businesses. As business data is growing, these analytical skills are becoming important.
How do you draw samples? What techniques you use when analyzing the data? Please share your stories, experiences & tips using comments.
A sample of our awesome collection of Excel tips
Here is a tiny sample of our awesome Excel tips. Don’t hold back, take them all, and more.
- Introduction to Pivot Tables
- How to shuffle a list of values in Excel
- How to shuffle a list using Formulas
- How to generate a random date, phone number
- Introduction to Excel’s random functions
- Case study: Generating housie (bingo) number cards using Excel
- Game: Simulating Deal or No Deal game in Excel
13 Responses to “Data Validation using an Unsorted column with Duplicate Entries as a Source List”
Pivot Table will involve manual intervention; hence I prefer to use the 'countif remove duplicate trick' along with 'text sorting formula trick; then using the offset with len to name the final range for validation.
if using the pivot table, set the sort to Ascending, so the list in the validation cell comes back alphabetically.
Hui: Brillant neat idea.
Vipul: I am intrigued by what you are saying. Please is it possible to show us how it can be done, because as u said Hui's method requires user intervention.
Thks to PHD and all
K
Table names dont work directly inside Data validation.
You will have to define a name and point it to the table name and then use the name inside validation
Eg MyClient : Refers to :=Table1[Client]
And then in the list validation say = MyClient
Kieranz,
Pls download the sample here http://cid-e98339d969073094.skydrive.live.com/self.aspx/.Public/data-validation-unsorted-list-example.xls
Off course there are many other ways of doing the same and integrating the formulae in multiple columns into one.
Pls refer to column FGHI in that file. Cell G4 is where my validation is.
Vipul:
Many thks, will study it latter.
Rgds
K
[...] to chandoo for the idea of getting unique list using Pivot tables. What we do is that create a pivot table [...]
@Vipul:
Thanks, that was awesome! 🙂
@Playercharlie Happy to hear that 🙂
Great contribution, Hui. Solved a problem of many years!
Thanks to you, A LOT
Hi Hui,
Greeting
hope you are doing well.
I'm interested to send you a private vba excel file which i need to show detail of pivot in new workbook instead of showing in same workbook as new sheet.
Please contact me on muhammed.ye@gmail.com
Best Regards