Pull and Match Random number

[MiGon]

Hi All,
I need a help with the attached data. Need to pull 12 random numbers for each ID.
I mean for each UNIQUE ID need to get number from colum B where ID matches

Thanks a mill
MiGon

 

[Sufiyan97]

Try in cell E2 and Drag right and down

=IFERROR(INDEX($B$2:$B$1000,AGGREGATE(15,6,(ROW($B$2:$B$1000)-ROW($B$2)+1)/($C$2:$C$1000=$C2),D$1)),"")

 

[MiGon]

=IFERROR(INDEX($B$2:$B$1000,AGGREGATE(15,6,(ROW($B$2:$B$1000)-ROW($B$2)+1)/($C$2:$C$1000=$C2),D$1)),"")

Hi Sufiyan97,
Thank you for the answer but I am getting blank
Thanks
MiGon

 

[MiGon]

Id_Last_Update	NUM		Unique ID	1	2	3	4	5	6	7	8	9	10	11	12
AB1624 01	9987489		AB1624 01
AB1624 01	9987509		AH904 01
AB1624 01	9987667		AK1656 01
AB1624 01	9987728		AP087 01
AB1624 01	9987770		BD547 01
AB1624 01	9987787		BK1625 01
AB1624 01	9987813		BS338 01
AB1624 01	9987847		CD018 01
AB1624 01	9987907		DB150 01
AB1624 01	9988227		DM1663 01
AB1624 01	9988247		DP300 01
JM968 01	9988287		DS969 01
JM968 01	9988329		EC052 01
JM968 01	9988448		EM923 01
JM968 01	9988469		JC1411 01
JM968 01	9988488		JM1623 01
JM968 01	9988527		JM968 01
JM968 01	9988587		JS1437 01
JM968 01	9988690		KB1407 01
JM968 01	9988889		KL1664 01
RH1320 01	9988927		LM402 01
RH1320 01	9989007		LO1671 01
RH1320 01	9911567		LO350 01
RH1320 01	9911587		LS1642 01
RH1320 01	9911628		MG672 01
RH1320 01	9911650		MM1635 01
RH1320 01	9911727		MO008 01
RH1320 01	9911769		MO1241 01
RH1320 01	9911827		PM298 01
RH1320 01	9911848		RH1320 01
AB1624 01	9912027		SM996 01
AB1624 01	9912070		SP1653 01

 

[Fluff]

Do the numbers from col B have to be pulled at random?

 

[MiGon]

Do the numbers from col B have to be pulled at random?

yeah, need to get 12 random numbers from column B which matches ID

 

[Fluff]

Ok, how about this, using a helper column in col C

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Id_Last_Update	NUM		Unique ID	1	2	3	4	5	6	7	8	9	10
2	AB1624 01	9987489	0.898508	AB1624 01	9987787	9988227	9987667	9987907	9987728	9987813	9912027	9987770	9987509	9912070
3	AB1624 01	9987509	0.803192	AH904 01
4	AB1624 01	9987667	0.505541	AK1656 01
5	AB1624 01	9987728	0.570061	AP087 01
6	AB1624 01	9987770	0.790683	BD547 01
7	AB1624 01	9987787	0.006266	BK1625 01
8	AB1624 01	9987813	0.62964	BS338 01
9	AB1624 01	9987847	0.847246	CD018 01
10	AB1624 01	9987907	0.561885	DB150 01
11	AB1624 01	9988227	0.47855	DM1663 01
12	AB1624 01	9988247	0.983957	DP300 01
13	JM968 01	9988287	0.020272	DS969 01
14	JM968 01	9988329	0.391673	EC052 01
15	JM968 01	9988448	0.50741	EM923 01
16	JM968 01	9988469	0.281464	JC1411 01
17	JM968 01	9988488	0.392467	JM1623 01
18	JM968 01	9988527	0.492281	JM968 01	9988287	9988469	9988690	9988329	9988488	9988527	9988587	9988448	9988889
19	JM968 01	9988587	0.495079	JS1437 01
20	JM968 01	9988690	0.339259	KB1407 01
21	JM968 01	9988889	0.73381	KL1664 01
22	RH1320 01	9988927	0.272037	LM402 01
23	RH1320 01	9989007	0.318849	LO1671 01
24	RH1320 01	9911567	0.96604	LO350 01
25	RH1320 01	9911587	0.645709	LS1642 01
26	RH1320 01	9911628	0.769054	MG672 01
27	RH1320 01	9911650	0.825364	MM1635 01
28	RH1320 01	9911727	0.348458	MO008 01
29	RH1320 01	9911769	0.23924	MO1241 01
30	RH1320 01	9911827	0.260804	PM298 01
31	RH1320 01	9911848	0.640203	RH1320 01	9911769	9911827	9988927	9989007	9911727	9911848	9911587	9911628	9911650	9911567
32	AB1624 01	9912027	0.632466	SM996 01
33	AB1624 01	9912070	0.807402	SP1653 01
List

Cell Formulas
Range	Formula
E2:N33	E2	=IFERROR(INDEX($B$2:$B$100,AGGREGATE(15,6,(ROW($B$2:$B$100)-ROW($B$2)+1)/($A$2:$A$100=$D2)/($C$2:$C$100=SMALL(IF($A$2:$A$100=$D2,$C$2:$C$100),E$1)),1)),"")
C2:C33	C2	=RAND()

 

[MiGon]

Hi Fluff,
Still not quite fix, as Need all the time 12 numbers against each ID
thanks

 

[MiGon]

Not sure if VBA would help

 

[Fluff]

With the sample you gave not all of the values occur 12 times.