This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
Hello - new to the forum and I apologize in advance if I'm not speaking VB/Excel properly and/or if a similar question has been asked/answered.
My scenario - I am provided with a table of data which consists of multiple separate populations. I am tasked with drawing a statistically significant random sample of each population within the table. So for example:
ColumnA | Column B | Column C
Smith | John | John Smith
Smith | Eric | Erick Smith
Smith | Jean | Jean Smith
Smith | Barbara | Barbara Smith
Johnson | Tony | Tony Johnson
Johnson | Gary | Gary Johnson
Johnson | Francis | Francis Johnson
Johnson | Tim | Tim Johnson
Johnson | Michelle | Michelle Johnson
And I require a random sampling of all "Smiths" and then from all "Johnsons." Ideally, my output will not be a random # assigned to each person, but rather it based on Column C, ie:
Results
Jean Smith
Tony Johnson
Francis Johnson
Thank you in advance
Excel workbook with a PQ solution avail. for download here. See the notes at the top of tblMonthlyTargets (the blue Table). I put in place a couple of things to secure your process
A brief description on how to add/remove rows from my tblMonthlyTargets as needed?
Just add Last Names at the bottom of the existing table. One of the big advantages of Excel Tables => they auto-resize :)
Ultimately I will need to employ this PQ on two different data sets, one containing 35 rows of targets, and one containing 24 rows of targets (and more importantly - if down the road if either population should happen to change, I could make the adjustments as needed)
Same as above. Add as many rows as you need to tblFullNames and tblMonthlyTargets. On the other hand if i.e. a name should disappear from tblFullNames just clear the content of the row. Same thing with tblMonthlyTargets but clear the content of column [Last Name] only to preserve the formula in column [Count Unique]
When you have tblFullNames and tblMonthlyTargets ready with your data click somewhere in the green table (MonthlySample) > Refresh - NB: the 1st Refresh within an Excel session takes more time than the next ones
Any question/issue let me know and if this solves the issue please Accept as answer to help others with a similar scenario - Thanks
Thank you for your efforts and very thorough explanation - it helps me follow the steps very well.
So now the problem I'm running into is that the formula for Count Unique doesn't seem to like when I add new last names to tblMonthlyTargets (#Name?; quick sample off of yours below).
I'm wondering if it has anything to do with saving it from OneDrive to my computer (Excel version 365 Apps for enterprise). Any thoughts? Thanks in advance!
EDIT: I tested your formulas on Excel on my Mac and it worked perfectly... so it does seem to be a "version" issue. :( Thoughts?
I tested your formulas on Excel on my Mac and it worked perfectly... so it does seem to be a "version" issue. :( Thoughts?
This basically means the version+build on your Mac is ahead. The LET function was made avail. with Version 2009 (Build 13231.20262) on the Current Channel for Windows (no idea for Mac) and with Version 2102 (Build 13801.20808) on the Semi-Annual Enterprise channel. Assuming your PC is on the latter hopefully you should get the LET function in a few weeks. Better you check with your IT...
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(3.2, 99%, 10%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EHS%3C/text%3E%3C/svg%3E)
Excel 365 Pro Plus with Power Query.
Pick one person from all the "Smiths" at random.
No formulas, no VBA macro.
https://www.mediafire.com/file/6cxj9rpd0lhhau5/07_21_21b.xlsx/file
https://www.mediafire.com/file/21k34gor3ptml1m/07_21_21b.pdf/file
I just found Power Query yesterday! I'm still pretty green - but have found how to use it for a couple other things. But primarily, I'm learning on the fly by searching other's work online.
In trying to recreate what you built, I am getting stuck between steps 4 (Dups) and 5 (Seq). I cannot figure out how to create the column "Data" under which "Table" results are listed.
Can you point me in a direction here? (note see attachment in @Emily Hua-MSFT thread.
THANK YOU!!!!
Randomly select a given set (25% in this example) of persons
from a variably sized population of last names.
With the latest formulas, Defined Names and PQ.
Updated files.
Rohn007 has posted a plethora of pointers to PQ Putorials perpetually.
This gets me INCREDIBLY close to what I need! The problem I am currently running into with this solution is that when I enter my true "Last Names", my list is significantly longer than the sample "Last Name" that we've been working with. So I am facing issues with "Hits":
1.) #CALC! errors for months with a zero count/sample (I have not yet had a chance to test its ultimate effect, because), and;
2.) #REF! errors as it seems that when I grow the table, the new rows no longer seem to recognize the same formula, and;
3.) This all of course creates a "DataFormat.Error: Invalid cell value '#ERROR!' in the PQ.
Not that I necessarily "need" you to do it for me, but if you were so willing for the good karma in the universe... I essentially need 35 different potential "last names" to fit into the TableSam (see below). Alternatively - if you can show me how to fix it myself - that is 100% good by me too!!
I WILL keep fiddling with this - but if you have time on your hands - your help will almost certainly get me to the finish line!!!
Pass.
Seems you just copied and pasted my example and fiddled with it.
You need to learn PQ and its "M" programming language,
Hope to hear from you in three months.
Interesting.
I apologize if I seem like I'm taking advantage of your good will - but the Q&A thread is a bit by its nature, a place where continued Q's can benefit from continued A's. I am attempting to reverse-engineer "what" you did so that I can understand "how" it works. While the output you have demonstrated is nearly what I need, it is also nearly useless to me without some level of explanation. I am not an Excel user (surely nowhere near the experienced level you are) - I am simply tasked with a challenge which uses Excel, after which I am to go about my day far, far from this task. As I indicated in my previous response, I would gladly take & run with guidance on where to make changes myself.
Good day.
Hi @m8ksmelaf
Assuming I understood your challenge I'm very close to a full Power Query solution.
However, I have a "little" challenge that I can't resolve right now. Before I spend more time on this I wanted to know if you have found a solution or if I should continue my investigations?
Thanks
That would be absolutely amazing LZ - I tried to find the solution myself over the weekend with no success.
Is there a requirement for the number of samples?
I found for different data from Column A, we may use different formulas, according to required sample quantity, we can manually drop down to fill the cells.
For sample from "Smith", please try the following formula.
=INDEX($C$2:$C$10,RANDBETWEEN(1,COUNTIF($A$2:$A$10,$A$2)))
For sample from "Johnson", you may try the formula below.
=INDEX($C$2:$C$10,RANDBETWEEN(COUNTIF($A$2:$A$10,$A$2)+1,COUNTIF($A$2:$A$10,$A$2)+COUNTIF($A$2:$A$10,$A$10)))
Please note, these results would be changed if you press F9.
Any misunderstanding, you may post back.
If an Answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Nice! Thank you for your insight!
So I presently have a formula that does my statistical calculations (MOE, Confidence, etc) of each population.
I need to draw a sample from one table which consists of my search criteria, against a 2nd table which consists of how many of each sample I need. Keep in mind the "0" results belong to a list of names that are static, and that list of names will be checked every single month for potential sampling.
I realize the complexity of my "ask" is greater than I originally stated - my optimism that I could fill in the gaps got the better of me.
Probably best way to work on this would be to give you an example of what I'm working with (attachment).
I presently have a formula that does my statistical calculations (MOE, Confidence, etc) of each population.
I need to draw a sample from one table which consists of my monthly results, against a 2nd table which consists of how many of each sample I need. Keep in mind the "0" results belong to a list of names that are static, and that list of names will be checked every single month for potential sampling.
I realize the complexity of my "ask" is greater than I originally stated at the start of this thread - my optimism that I could fill in the gaps got the better of me.
Any guidance would be incredibly appreciated! Thank you all in advance!
Case also posted on Microsoft Answer
Removing from MS Answer thread as solution near complete in this thread.
Hi @m8ksmelaf
Following Comment from earlier today:
Assuming I understood your challenge I'm very close to a full Power Query solution.
However, I have a "little" challenge that I can't resolve right now. Before I spend more time on this I wanted to know if you have found a solution or if I should continue my investigations?
Thanks
Issue fixed. I now have a full Power Query solution (pic. truncated):
Let me know...
@Lz._ -
Yes this looks just about exactly what I need.
Could I get the PQ & so that this birdy can fly on his own, a brief description on how to add/remove rows from my tblMonthlyTargets as needed? Ultimately I will need to employ this PQ on two different data sets, one containing 35 rows of targets, and one containing 24 rows of targets (and more importantly - if down the road if either population should happen to change, I could make the adjustments as needed).
Thank you sooo much!