Sampling of data using formula or macros

[Sailadarohit]

Book1
	A	B	C
1	U.ID(Not Unique)	Main ID (Unique)	Priority
2	1	10	High
3	1	11	Medium
4	2	12	Medium
5	3	13	Low
6	5	14	High
7	4	15	Low
8	7	16	Medium
9	5	17	High
10	7	18	Medium
11	1	19	Medium
12	2	20	Low
13	8	21	High
14	9	22	Low
15	6	23	Medium
16	5	24	High
17	4	25	Medium
18	2	26	Medium
19	8	27	Low
20	1	28	High
21	6	29	Low
22	9	30	Medium
23	3	31	High
24	6	32	Medium
25	9	33	Medium
26	1	34	Low
27	1	35	High
28	2	36	Low
29	3	37	Medium
30	5	38	High
31	4	39	Medium
32	7	40	Medium
33	5	41	Low
34	7	42	High
35	1	43	Low
36	2	44	Medium
37	8	45	High
38	9	46	Medium
39	6	47	Medium
40	5	48	Low
41	4	49	High
42	2	50	Low
43	8	51	Medium
44	1	52	High
45	6	53	Medium
46	9	54	Medium
47	3	55	Low
48	6	56	High
49	9	57	Low
Sheet1

There is a use case where we have U.ID which is not unique, main ID which is unique and priority which covers high, medium and low. In a general scenerio we have mutiple Main ID's associated with one U.ID . For audit purpose we require a sample of this data which will have only 3 main ID's associated with each U.ID.
Please note in real scenario this is a huge data set so manually doing this is not possible.
Can anyone help with an excel formula (preferred) or a macro

 

[Fluff]

How about

Sub sailadarohit()
   Dim Rng As Range
   Dim Ary As Variant, Nary As Variant
   Dim nr As Long, r As Long
  
   Set Rng = Range("A2:C" & Range("A" & Rows.Count).End(xlUp).Row)
   Ary = Evaluate("sortby(" & Rng.Address & ",randarray(" & Rng.Rows.Count & "))")
   ReDim Nary(1 To UBound(Ary), 1 To 3)
   With CreateObject("scripting.dictionary")
      For r = 1 To UBound(Ary)
         If .Item(Ary(r, 1)) < 3 Then
            .Item(Ary(r, 1)) = .Item(Ary(r, 1)) + 1
            nr = nr + 1
            Nary(nr, 1) = Ary(r, 1)
            Nary(nr, 2) = Ary(r, 2)
            Nary(nr, 3) = Ary(r, 3)
         End If
      Next r
   End With
   Nary = Application.Sort(Nary)
   Range("E2").Resize(nr, 3).Value = Nary
End Sub

EDIT
Forgot to mention, this will only work with 2021 or 365

 

[Sailadarohit]

How about

Sub sailadarohit()
   Dim Rng As Range
   Dim Ary As Variant, Nary As Variant
   Dim nr As Long, r As Long
 
   Set Rng = Range("A2:C" & Range("A" & Rows.Count).End(xlUp).Row)
   Ary = Evaluate("sortby(" & Rng.Address & ",randarray(" & Rng.Rows.Count & "))")
   ReDim Nary(1 To UBound(Ary), 1 To 3)
   With CreateObject("scripting.dictionary")
      For r = 1 To UBound(Ary)
         If .Item(Ary(r, 1)) < 3 Then
            .Item(Ary(r, 1)) = .Item(Ary(r, 1)) + 1
            nr = nr + 1
            Nary(nr, 1) = Ary(r, 1)
            Nary(nr, 2) = Ary(r, 2)
            Nary(nr, 3) = Ary(r, 3)
         End If
      Next r
   End With
   Nary = Application.Sort(Nary)
   Range("E2").Resize(nr, 3).Value = Nary
End Sub

EDIT
Forgot to mention, this will only work with 2021 or 365

Thanks fluff for taking out time and helping me with this, i have 2 questions:
1. If my range changes what should i change in the code
2. If the sample size number changes from 3 per U.ID to a different number what should i change in the code.

TIA

 

[Fluff]

If the sample size changes then change the 3 on this line

If .Item(Ary(r, 1)) < 3 Then

The code uses the last used row in col A to pick-up all the data.

 

[Sailadarohit]

If the sample size changes then change the 3 on this line

If .Item(Ary(r, 1)) < 3 Then

The code uses the last used row in col A to pick-up all the data.

for the first part if i have my data from Row A to Row F i should change it as shown below. Correct?
Set Rng = Range("A2:F" & Range("A" & Rows.Count).End(xlUp).Row)

 

[Fluff]

for the first part if i have my data from Row A to Row F i should change it as shown below. Correct?

No, that will still only return the 1st 3 columns.
If your data is not like what you posted, then post something that is correct.

 

[RobP]

@Fluff - just for my learning here - when you do this part:

 If .Item(Ary(r, 1)) < 3 Then
            .Item(Ary(r, 1)) = .Item(Ary(r, 1)) + 1

I can see it is creating a dictionary of each unique U.ID from Col A. I'm not following how it distinguishes between the actual "item", and the "running count of the items" and where they are stored ?
Can you elaborate a little by chance please, as it look pretty genius.

thanks
Rob

 

[Fluff]

The Item is the running count, whilst Ary(r,1) is the Key. You can find out more about dictionaries here Excel VBA Dictionary - A Complete Guide - Excel Macro Mastery

 

[RobP]

thanks - indeed that site is my goto on Dictionaries .. and I've poured over it for ages trying to get my head around it.

I guess I was a little fried on the fact that its both creating items, and counting items at the same time. Makes sense now - cheers

 

[Fluff]

You're welcome.