ARANDOM

[Xlambda]

ARANDOM creates a random array of "n" rows using elements of another array "a"
Idea credited to Rico, different approach.
Other functions on minisheet ACOMBINE , ADATE , ATEXTJOIN

=LAMBDA(a,n,
    LET(c,COLUMNS(a),
       x,INDEX(a,INT(RANDARRAY(n,c)*MMULT(SEQUENCE(,ROWS(a))^0,--(a<>"")))+1,SEQUENCE(,c)),
       IF(x="null","",x)
    )
)

LAMBDA 7.0.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
1	1st name	Mid name	Lst Name	2nd Lst Name		shuffle	n				Date	ID-nr_Name	Product	Package	Region		shuffle	y
2											=ADATE(RANDARRAY(40,,"1-jan-20","30-05-21",1),"f",)
3	sample					=IF(G1="y",ARANDOM(A4:D23,20),F4#)						=ATEXTJOIN(F4#,"ID-"&SEQUENCE(ROWS(F4#))&"_",," ")					=IF(R1="y",ARANDOM(K4:O43,45),Q4#)
4	Mel	null	Dawkins	Mattei		Theodore		Kriger	Ryburn		01-04-20	ID-1_Theodore Kriger Ryburn	Quad	3	West		01-01-21	ID-19_Houston Myron Enderle	Yanaki	18	South
5	Winford	null	Filer	Speth		Antione	Myron	Scheidler	Fogle		01-04-21	ID-2_Antione Myron Scheidler Fogle	Majestic	6	South		01-06-20	ID-8_Rey Myron Mcglothlin Mattei	Sunset	6	Canada
6	Miguel	null	Valtierra	Yale		Antione		Grinnell			01-01-21	ID-3_Antione Grinnell	Yanaki	12	MidWest		01-04-20	ID-13_Rey Jamel Dawkins Fogle	Crested	6	NorthWest
7	Hollis	null	Shelton	Levison		Freddie	Jere	Otten	Levison		01-07-20	ID-4_Freddie Jere Otten Levison	Crested	18	Canada		01-09-20	ID-14_Houston Grinnell Levison	Gigi	12	South
8	Davis	null	Pasquariello	Fogle		Mel	Jere	Scheidler			01-01-20	ID-5_Mel Jere Scheidler	Sunset		Mexico		01-03-20	ID-17_Rudolf Bradly Garth	Crested	12	East
9	Houston	null	Grinnell	Ryburn		Jesus		Pasquariello	Ryburn		01-03-20	ID-6_Jesus Pasquariello Ryburn	Gigi		NorthWest		01-12-20	ID-3_Antione Grinnell	Yanaki	12	East
10	Antione	Jarrod	Dechant	Hoge		Houston	Arnoldo	Ybarbo			01-08-20	ID-7_Houston Arnoldo Ybarbo			East		01-04-21	ID-5_Mel Jere Scheidler	Gigi	3	West
11	Theodore	Randolph	Pozo	null		Rey	Myron	Mcglothlin	Mattei		01-12-20	ID-8_Rey Myron Mcglothlin Mattei					01-04-20	ID-8_Rey Myron Mcglothlin Mattei	Yanaki	12	East
12	Vance	Arnoldo	Mcglothlin	null		Ricky	Jamel	Buskirk			01-01-20	ID-9_Ricky Jamel Buskirk					01-05-21	ID-11_Theodore Grinnell Speth	Yanaki	6	Canada
13	Lindsey	Harry	Buskirk	null		Mel	Bradly	Buskirk			01-01-20	ID-10_Mel Bradly Buskirk					01-01-20	ID-13_Rey Jamel Dawkins Fogle	Quad	18	MidWest
14	Rudolf	null	Otten	null		Theodore		Grinnell	Speth		01-01-20	ID-11_Theodore Grinnell Speth					01-12-20	ID-13_Rey Jamel Dawkins Fogle	Crested	12	South
15	Ricky	null	Kitterman	null		Alfred	Jamel	Poulsen	Mattei		01-09-20	ID-12_Alfred Jamel Poulsen Mattei					01-07-20	ID-11_Theodore Grinnell Speth	Majestic	18	South
16	Jesus	Jamel	Garth			Rey	Jamel	Dawkins	Fogle		01-05-20	ID-13_Rey Jamel Dawkins Fogle					01-03-20	ID-14_Houston Grinnell Levison	Gigi	6	South
17	Tom	Bradly	Enderle			Houston		Grinnell	Levison		01-09-20	ID-14_Houston Grinnell Levison					01-03-21	ID-19_Houston Myron Enderle	Yanaki	3	South
18	Freddie	Myron	Poulsen			Vance	Harry	Garth	Levison		01-03-20	ID-15_Vance Harry Garth Levison					01-01-20	ID-1_Theodore Kriger Ryburn	Crested	12	Canada
19	Alvin	Jere	Ybarbo			Rey	Bradly	Shelton	Ryburn		01-03-20	ID-16_Rey Bradly Shelton Ryburn					01-01-20	ID-2_Antione Myron Scheidler Fogle	Gigi	6	South
20	Alfonso		Silvestri			Rudolf	Bradly	Garth			01-02-20	ID-17_Rudolf Bradly Garth					01-04-20	ID-11_Theodore Grinnell Speth	Sunset	12	MidWest
21	Eduardo		Scheidler			Eduardo	Jarrod	Dawkins	Fogle		01-06-20	ID-18_Eduardo Jarrod Dawkins Fogle					01-04-20	ID-18_Eduardo Jarrod Dawkins Fogle	Sunset	6	East
22	Alfred		Luiz			Houston	Myron	Enderle			01-01-21	ID-19_Houston Myron Enderle					01-04-21	ID-8_Rey Myron Mcglothlin Mattei	Sunset	6	NorthWest
23	Rey		Kriger			Mel	Jamel	Valtierra	Hoge		01-05-21	ID-20_Mel Jamel Valtierra Hoge					01-04-20	ID-2_Antione Myron Scheidler Fogle	Sunset	3	NorthWest
24	Note:										01-04-21						01-03-20	ID-3_Antione Grinnell	Crested	18	Mexico
25	We can set "null" cells if we want random										01-02-20						01-03-21	ID-6_Jesus Pasquariello Ryburn	Sunset	18	NorthWest
26	created array to contain null strings										01-09-20						01-03-20	ID-13_Rey Jamel Dawkins Fogle	Quad	3	East
27											01-07-20						01-08-20	ID-2_Antione Myron Scheidler Fogle	Majestic	18	MidWest
28	Create a random array										01-04-21						01-01-20	ID-3_Antione Grinnell	Majestic	12	NorthWest
29	using this data			=ARANDOM(A30:B33,30)			Fun quest:				01-02-20						01-09-20	ID-17_Rudolf Bradly Garth	Gigi	18	Mexico
30	LG	phone		Hp	laptop		Does the random array				01-04-20						01-01-20	ID-6_Jesus Pasquariello Ryburn	Yanaki	3	NorthWest
31	Samsung	camera		Hp	phone		created all pos.combinations?				01-08-20						01-07-20	ID-1_Theodore Kriger Ryburn	Yanaki	3	West
32	Sony	laptop		LG	camera		=UNIQUE(D30#)				01-03-21						01-12-20	ID-3_Antione Grinnell	Crested	6	West
33	Hp			Sony	laptop		Hp	laptop			01-12-20						01-07-20	ID-6_Jesus Pasquariello Ryburn	Gigi	6	Mexico
34				Samsung	phone		Hp	phone			01-12-20						01-12-20	ID-12_Alfred Jamel Poulsen Mattei	Quad	18	Mexico
35	All possible			Samsung	phone		LG	camera			01-09-20						01-01-21	ID-14_Houston Grinnell Levison	Crested	3	South
36	combinations			LG	laptop		Sony	laptop			01-03-21						01-08-20	ID-9_Ricky Jamel Buskirk	Majestic	12	NorthWest
37	=ACOMBINE(A30:B33,)			LG	phone		Samsung	phone			01-02-20						01-01-20	ID-7_Houston Arnoldo Ybarbo	Yanaki	18	Mexico
38	Hp	camera		Hp	camera		LG	laptop			01-07-20						01-04-21	ID-16_Rey Bradly Shelton Ryburn	Yanaki	3	South
39	Hp	laptop		Samsung	laptop		LG	phone			01-01-20						01-02-20	ID-8_Rey Myron Mcglothlin Mattei	Quad	6	East
40	Hp	phone		Samsung	camera		Hp	camera			01-01-21						01-09-20	ID-15_Vance Harry Garth Levison	Majestic	6	NorthWest
41	LG	camera		Samsung	phone		Samsung	laptop			01-02-20						01-08-20	ID-8_Rey Myron Mcglothlin Mattei	Yanaki	12	South
42	LG	laptop		LG	phone		Samsung	camera			01-01-20						01-03-20	ID-2_Antione Myron Scheidler Fogle	Majestic	3	Mexico
43	LG	phone		Hp	camera		Sony	phone			01-04-20						01-01-20	ID-13_Rey Jamel Dawkins Fogle	Quad	12	Canada
44	Samsung	camera		LG	laptop		Sony	camera									01-12-20	ID-18_Eduardo Jarrod Dawkins Fogle	Quad	6	Mexico
45	Samsung	laptop		Sony	phone												01-04-21	ID-6_Jesus Pasquariello Ryburn	Quad	12	West
46	Samsung	phone		Samsung	laptop		If rows array above					other functions on minisheet					01-03-20	ID-13_Rey Jamel Dawkins Fogle	Quad	12	West
47	Sony	camera		Samsung	laptop		is equal with rows array					ACOMBINE					01-12-20	ID-20_Mel Jamel Valtierra Hoge	Quad	18	Canada
48	Sony	laptop		Sony	phone		of all posib.comb then yes					ADATE					01-02-20	ID-3_Antione Grinnell	Gigi	3	NorthWest
49	Sony	phone		LG	laptop		otherwise no					ATEXTJOIN
50				Sony	camera
51				LG	laptop		=IF(ROWS(G33#)=ROWS(A38#),"Yes","No")
52				Samsung	laptop		Answer:
53				Samsung	camera		Yes
54				Samsung	laptop
55				Sony	laptop
56				Samsung	laptop
57				LG	phone
58				Sony	phone
59				Samsung	phone
60
ARANDOM post

Cell Formulas
Range	Formula
K2,G51	K2	=FORMULATEXT(K4)
F3,A37,G32,D29,Q3,L3	F3	=FORMULATEXT(F4)
F4:I23	F4	=IF(G1="y",ARANDOM(A4:D23,20),F4#)
K4:K43	K4	=ADATE(RANDARRAY(40,,"1-jan-20","30-05-21",1),"f",)
L4:L23	L4	=ATEXTJOIN(F4#,"ID-"&SEQUENCE(ROWS(F4#))&"_",," ")
Q4:U48	Q4	=IF(R1="y",ARANDOM(K4:O43,45),Q4#)
D30:E59	D30	=ARANDOM(A30:B33,30)
G33:H44	G33	=UNIQUE(D30#)
A38:B49	A38	=ACOMBINE(A30:B33,)
G53	G53	=IF(ROWS(G33#)=ROWS(A38#),"Yes","No")
Dynamic array formulas.

 

[Xlambda]

Task: Create a random array n rows(50) that should contain, op date, sales person name, product name, product cost, product unit price, region , region discount, units sold, revenue, profit, and print 3 PT reports: tot revenue for product\region ; profit for person\product, all time ; tot revenue monthly\region.
Functions used on minisheet: ADATE , ACLMSPLIT , ARANDOM , APIVOT

LAMBDA 7.0.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z
1	Task: Create a random table n rows(50) that should contain,op date, sales person name,product, product cost, product unit price, region , region discount, units sold,revenue,profit
2	and print 3 PT reports: tot revenue for product\region ; profit for person\product, all time ; tot revenue monthly\region
3		Random input data:						Random array calculation
4			=RANDARRAY(30,,5,15,1)					Formulas											PT 1 Tot revenue - Product\Region
5	=RANDARRAY(50,,"1-05-20","30-05-21",1)				↓		=ADATE(ACLMSPLIT(ACLMSPLIT(ARANDOM(A7:E56,50),4,"-"),3,"-"),"m",)												=APIVOT(G7:P56,3,6,9,)
6	Date	Name	Prd.,Cost,P/u	Region,Disc	Unt.Sld.		Date	Name	Prod.	Cost	U.Prc	Region	Reg.Disc	Unt.Sold	Revenue	Profit			(3\6) 9 vf=0	East	North	South	West	Grand Total
7	07-08-20	Theodore Kriger	Quad-7-10	West-0.03	5		2021-05-May	Antione Scheidler	Yanaki	6	11	East	6%	13	134.42	56.42			Crested	263.2	107.52	186.2	258.02	814.94
8	07-07-20	Antione Scheidler	Majestic-8-12	South-0.05	6		2020-06-Jun	Theodore Kriger	Crested	5	14	East	6%	13	171.08	106.08			Majestic	191.76	403.2	193.8	232.8	1021.56
9	11-08-20	Antione Grinnell	Yanaki-6-11	East-0.06	12		2021-01-Jan	Rey Mcglothlin	Majestic	8	12	West	3%	11	128.04	40.04			Quad	244.4	240	123.5	126.1	734
10	13-02-21	Freddie Otten	Crested-5-14	North-0.04	7		2020-06-Jun	Antione Scheidler	Sunset	9	13	South	5%	9	111.15	30.15			Sunset	97.76	586.56	456.95	214.37	1355.64
11	29-11-20	Mel Scheidler	Sunset-9-13		9		2020-11-Nov	Houston Ybarbo	Majestic	8	12	South	5%	5	57	17			Yanaki	237.82	496.32	365.75	330.77	1430.66
12	04-12-20	Jesus Pasquariello			11		2021-03-Mar	Freddie Otten	Yanaki	6	11	West	3%	8	85.36	37.36			Grand Total	1034.94	1833.6	1326.2	1162.06	5356.8
13	08-03-21	Houston Ybarbo			11		2020-05-May	Antione Scheidler	Crested	5	14	South	5%	6	79.8	49.8
14	28-06-20	Rey Mcglothlin			12		2020-12-Dec	Rey Mcglothlin	Quad	7	10	West	3%	13	126.1	35.1
15	14-07-20				12		2020-07-Jul	Antione Grinnell	Crested	5	14	South	5%	8	106.4	66.4			PT 2 Profit - Name\Product
16	14-05-20				13		2020-12-Dec	Theodore Kriger	Yanaki	6	11	North	4%	6	63.36	27.36			=APIVOT(G7:P56,2,3,10,)
17	18-06-20				13		2021-04-Apr	Antione Grinnell	Quad	7	10	South	5%	13	123.5	32.5			(2\3) 10 vf=0	Crested	Majestic	Quad	Sunset	Yanaki	Grand Total
18	10-09-20				12		2021-03-Mar	Antione Scheidler	Crested	5	14	North	4%	8	107.52	67.52			Antione Grinnell	66.4	21.12	133.9	25.76	174.71	421.89
19	09-04-21				8		2020-08-Aug	Freddie Otten	Sunset	9	13	West	3%	12	151.32	43.32			Antione Scheidler	211.7	0	0	56.95	56.42	325.07
20	09-01-21				11		2021-04-Apr	Rey Mcglothlin	Majestic	8	12	North	4%	11	126.72	38.72			Freddie Otten	57.12	29.52	0	61.37	123.12	271.13
21	22-03-21				15		2020-09-Sep	Antione Grinnell	Majestic	8	12	North	4%	6	69.12	21.12			Houston Ybarbo	0	52.2	0	24.36	0	76.56
22	13-12-20				10		2021-01-Jan	Antione Grinnell	Quad	7	10	East	6%	15	141	36			Jesus Pasquariello	0	0	26	0	135.7	161.7
23	07-05-20				8		2020-11-Nov	Antione Scheidler	Sunset	9	13	South	5%	8	98.8	26.8			Mel Scheidler	0	26.24	0	0	48.95	75.19
24	09-05-21				6		2021-01-Jan	Antione Grinnell	Quad	7	10	East	6%	11	103.4	26.4			Rey Mcglothlin	68.64	106.92	35.1	62.64	21.7	295
25	26-05-21				11		2020-11-Nov	Antione Grinnell	Yanaki	6	11	North	4%	10	105.6	45.6			Theodore Kriger	106.08	73.56	0	143.56	54.06	377.26
26	19-05-21				10		2020-08-Aug	Mel Scheidler	Majestic	8	12	East	6%	8	90.24	26.24			Grand Total	509.94	309.56	195	374.64	614.66	2003.8
27	12-10-20				12		2020-10-Oct	Freddie Otten	Majestic	8	12	East	6%	9	101.52	29.52
28	27-05-20				8		2020-07-Jul	Theodore Kriger	Majestic	8	12	South	5%	12	136.8	40.8
29	24-08-20				13		2020-08-Aug	Rey Mcglothlin	Yanaki	6	11	East	6%	5	51.7	21.7			PT 3 Tot revenue - month\Region
30	19-01-21				8		2020-05-May	Houston Ybarbo	Sunset	9	13	North	4%	7	87.36	24.36			=APIVOT(G7:P56,1,6,9,)
31	18-11-20				7		2021-05-May	Freddie Otten	Sunset	9	13	West	3%	5	63.05	18.05			(1\6) 9 vf=0	East	North	South	West	Grand Total
32	29-03-21				15		2021-03-Mar	Jesus Pasquariello	Yanaki	6	11	North	4%	9	95.04	41.04			2020-05-May	0	202.56	178.6	0	381.16
33	17-07-20				9		2020-05-May	Houston Ybarbo	Majestic	8	12	North	4%	10	115.2	35.2			2020-06-Jun	171.08	149.76	278.35	160.05	759.24
34	21-06-20				5		2021-05-May	Mel Scheidler	Yanaki	6	11	South	5%	11	114.95	48.95			2020-07-Jul	0	0	243.2	0	243.2
35	28-08-20				8		2020-12-Dec	Antione Scheidler	Crested	5	14	West	3%	11	149.38	94.38			2020-08-Aug	141.94	0	83.6	151.32	376.86
36	11-05-20				11		2020-06-Jun	Antione Grinnell	Yanaki	6	11	West	3%	15	160.05	70.05			2020-09-Sep	0	131.52	0	0	131.52
37	04-10-20						2021-03-Mar	Rey Mcglothlin	Majestic	8	12	North	4%	8	92.16	28.16			2020-10-Oct	193.64	306.24	0	0	499.88
38	17-04-21						2020-10-Oct	Rey Mcglothlin	Sunset	9	13	North	4%	13	162.24	45.24			2020-11-Nov	0	105.6	155.8	0	261.4
39	28-12-20						2021-05-May	Theodore Kriger	Majestic	8	12	West	3%	9	104.76	32.76			2020-12-Dec	97.76	188.16	0	384.12	670.04
40	27-09-20						2020-06-Jun	Jesus Pasquariello	Yanaki	6	11	South	5%	10	104.5	44.5			2021-01-Jan	244.4	212.16	0	128.04	584.6
41	24-05-20						2020-12-Dec	Theodore Kriger	Sunset	9	13	North	4%	10	124.8	34.8			2021-02-Feb	0	0	148.2	0	148.2
42	02-04-21						2020-12-Dec	Rey Mcglothlin	Crested	5	14	West	3%	8	108.64	68.64			2021-03-Mar	51.7	294.72	0	85.36	431.78
43	19-05-21						2021-03-Mar	Antione Grinnell	Yanaki	6	11	East	6%	5	51.7	21.7			2021-04-Apr	0	126.72	123.5	0	250.22
44	11-04-21						2020-06-Jun	Theodore Kriger	Yanaki	6	11	South	5%	6	62.7	26.7			2021-05-May	134.42	116.16	114.95	253.17	618.7
45	23-01-21						2020-08-Aug	Freddie Otten	Yanaki	6	11	South	5%	8	83.6	35.6			Grand Total	1034.94	1833.6	1326.2	1162.06	5356.8
46	03-01-21						2021-02-Feb	Theodore Kriger	Sunset	9	13	South	5%	12	148.2	40.2
47	28-07-20						2020-06-Jun	Theodore Kriger	Sunset	9	13	North	4%	12	149.76	41.76
48	21-03-21						2021-01-Jan	Jesus Pasquariello	Quad	7	10	North	4%	10	96	26			Other functions on spreadsheet
49	28-05-20						2021-01-Jan	Jesus Pasquariello	Yanaki	6	11	North	4%	11	116.16	50.16			ADATE
50	05-09-20						2020-12-Dec	Antione Grinnell	Sunset	9	13	East	6%	8	97.76	25.76			ACLMSPLIT
51	12-06-20						2021-05-May	Freddie Otten	Yanaki	6	11	North	4%	11	116.16	50.16			ARANDOM
52	10-01-21						2020-09-Sep	Rey Mcglothlin	Sunset	9	13	North	4%	5	62.4	17.4			APIVOT
53	18-08-20						2020-10-Oct	Freddie Otten	Crested	5	14	East	6%	7	92.12	57.12
54	06-11-20						2020-10-Oct	Antione Grinnell	Quad	7	10	North	4%	15	144	39
55	30-05-20						2021-05-May	Antione Grinnell	Yanaki	6	11	West	3%	8	85.36	37.36
56	28-10-20						2020-05-May	Theodore Kriger	Sunset	9	13	South	5%	8	98.8	26.8
57			↑	↑
58			Note: Is important to have data in this format when there are unique relations (like product, cost price, selling price or region, region discount) before "randomize" it, for consistency.
59			Only after randomize it, splitting it with ACLMSPLIT(nested if we need more clms to split), will guarantee that data is kept consistent.
60			That is the main reason is no need to write a separate function for randomizing values, plus, to create real life alike random data sets, is better to add columns that do calculations, and not inert data that has no meaning.
61
ARANDOM example

Cell Formulas
Range	Formula
C4	C4	=FORMULATEXT(E7)
A5,G5	A5	=FORMULATEXT(A7)
S5,S30,S16	S5	=FORMULATEXT(S6)
S6:X12	S6	=APIVOT(G7:P56,3,6,9,)
A7:A56	A7	=RANDARRAY(50,,"1-05-20","30-05-21",1)
E7:E36	E7	=RANDARRAY(30,,5,15,1)
G7:N56	G7	=ADATE(ACLMSPLIT(ACLMSPLIT(ARANDOM(A7:E56,50),4,"-"),3,"-"),"m",)
O7:O56	O7	=K7:K56*N7:N56*(1-M7:M56)
P7:P56	P7	=O7#-(J7:J56*N7:N56)
S17:Y26	S17	=APIVOT(G7:P56,2,3,10,)
S31:X45	S31	=APIVOT(G7:P56,1,6,9,)
Dynamic array formulas.