Selecting randomly three unique rows per user

[joj_john]

Hello I am new member. My name is Jolly

For audit purposes, I need to select 3 notes per physician from a database (see attached excel sheet). They are namely Discharge Summary, Progress Notes, History and Physical from different dates.
Currently I am using the If function and assigning values for each note type. Then using the pivot table randomly pick 3 notes. Please see excel sheet Alex brit.
I am sure there is an easier way. Awaiting your advice and help.

 

[GraH - Guido]

If you have Office 365, you can use a combination of UNIQUE and RANDARRAY with a couple of other functions.
[G7]=UNIQUE(staff3[Physician])
[H6]=TRANSPOSE(UNIQUE(staff3[Note Type]))
[H7]=BYCOL($H$6#;LAMBDA(Note;LET(
data;FILTER(staff3[Medical Record Number];(staff3[Physician]=G7)*(staff3[Note Type]=Note));
Count;COUNTA(data);
Random;INDEX(data;RANDARRAY(1;;1;Count;TRUE));Random)))

True wizards over here manage to this in a single formula. For me the nested arrays are in the way.

 

[p45cal]

Refresh the pivot table for a new random set.
Top10 selecting the Top 1 in the Medical Record Number field.
Same as in your other thread: https://chandoo.org/forum/threads/r...ithout-duplication-from-an-excel-sheet.56539/

 

[p45cal]

Formula in cell G7
or, same formula converted to a lambda formula in cell L7
Generate new random selection by sorting column E.
I've been lazy in not trying to include the randomisation within the formula.
(Confused the hell out of me until I discovered randbetween in column B!)