Formula help.

[Dan Wilson]

Good day. I am running Excel out of Office365 (updated) and Windows 10 Home (Updated). I have a workbook that tracks all of the songs in my music library. One of the worksheets is used to generate 40 random numbers between 1 and 3000. The worksheet titled "50-69" contains several Columns dedicated to the identification of each song in the library. Column A contains the song title. Row 1 contains the Column headers and is set as the "Freeze Top Row" for the worksheet. Thus, Rows 2 through 3000 identify each song. There is a separate worksheet titled "Sheet 1" that creates the random numbers in 40 separate cells as show below.

1​	2​	3​	4​	5​	6​	7​	8​	9​	10​
2611​	268​	2587​	2023​	348​	1505​	127​	1795​	2926​	1433​
11​	12​	13​	14​	15​	16​	17​	18​	19​	20​
1362​	604​	2034​	2901​	2268​	2979​	552​	550​	1454​	331​
21​	22​	23​	24​	25​	26​	27​	28​	29​	30​
187​	772​	201​	2438​	2786​	1961​	1392​	2883​	2117​	2050​
30​	32​	33​	34​	35​	36​	37​	38​	39​	40​
2443​	708​	1122​	691​	2976​	580​	2884​	2949​	1877​	2083​

In the example above, the numbers 1 thru 40 identify the order of the songs to be played. the numbers directly below them are the Row numbers of the worksheet "50-69" containing the 3000 songs. Thus, the first song to be played would be in Row 2611 with the song title in Column A. To make the whole process easier, I would like to create another set of numbers 1 thru 40 in a descending Column on the same worksheet with the order of 1 thru 40 in Column A and then fill column B with the actual song title from worksheet "50-69" as identified by the random number. I don't know how to search for a Row number in a formula and that is my problem. Any help will be appreciated.
Thank you,
Dan Wilson...

 

[hagia_sofia]

Hello, generally speaking would it for you e.g. to use SEQUENCE function to assign a number to each song, i.e. =SEQUENCE(3000), and then use XLOOKUP look for the song which corresponds to the assigned number?

 

[Jeffrey Mahoney]

Here is a simple example. My way of making the random choice is not optimal

Book2
	A	B	C
1	Item	Row	Song Name
2	1	1093	Song 1093
3	2	2005	Song 2005
4	3	1626	Song 1626
5	4	103	Song 0103
6	5	458	Song 0458
7	6	2108	Song 2108
8	7	150	Song 0150
9	8	276	Song 0276
10	9	1587	Song 1587
11	10	1305	Song 1305
12	11	2601	Song 2601
13	12	1112	Song 1112
14	13	319	Song 0319
15	14	1846	Song 1846
16	15	2023	Song 2023
17	16	651	Song 0651
18	17	2367	Song 2367
19	18	1756	Song 1756
Sheet1

Cell Formulas
Range	Formula
B2:B19	B2	=LET(V,RANDBETWEEN(1,3000),IF(COUNTIF(B$1:B1,V)>0,IF(V+1>3000,1,V+1),V))
C2:C19	C2	=INDEX(Sheet2!A$2:A$3001,Sheet1!B2)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B2:B41	Cell Value	duplicates	text	NO

 

[Dan Wilson]

Good day Jeffrey and thank you for responding. After examining your reply, I see that your solution provides its own random numbers. A few months ago, I asked for help with the Random function and the solution works well. The formulas given to me create 40 separate random numbers anywhere from 1 to 3010 with no duplicates. What I need is a formula that will use the existing random numbers to search the "50-69" worksheet and return the song title found in Column A of the Row identified by the random number. I appreciate the help, and I hope that my explanation is complete.
Thank you,
Dan Wilson...

 

[Jeffrey Mahoney]

Use the Index formula I gave you. Replace SHEET2 with 50-69

 

[Peter_SSs]

My '50-69' has this sample data extending down to row 3000

Dan Wilson.xlsm
	A
1	Title
2	Song 1
3	Song 2
4	Song 3
5	Song 4
6	Song 5
7	Song 6
8	Song 7
9	Song 8
10	Song 9
11	Song 10
12	Song 11
50-69

I'm assuming that 'Sheet1' has some sort of labelling & if that is so, perhaps you could use something like this?

Dan Wilson.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1
2	Seq	1	2	3	4	5	6	7	8	9	10			1	Song 2610
3	Row	2611	268	2587	2023	348	1505	127	1795	2926	1433			2	Song 267
4														3	Song 2586
5	Seq	11	12	13	14	15	16	17	18	19	20			4	Song 2022
6	Row	1362	604	2034	2901	2268	2979	552	550	1454	331			5	Song 347
7														6	Song 1504
8	Seq	21	22	23	24	25	26	27	28	29	30			7	Song 126
9	Row	187	772	201	2438	2786	1961	1392	2883	2117	2050			8	Song 1794
10														9	Song 2925
11	Seq	30	32	33	34	35	36	37	38	39	40			10	Song 1432
12	Row	2443	708	1122	691	2976	580	2884	2949	1877	2083			11	Song 1361
13														12	Song 603
14														13	Song 2033
15														14	Song 2900
16														15	Song 2267
17														16	Song 2978
18														17	Song 551
19														18	Song 549
20														19	Song 1453
21														20	Song 330
22														21	Song 186
23														22	Song 771
24														23	Song 200
25														24	Song 2437
26														25	Song 2785
27														26	Song 1960
28														27	Song 1391
29														28	Song 2882
30														29	Song 2116
31														30	Song 2049
32														31	Song 2442
33														32	Song 707
34														33	Song 1121
35														34	Song 690
36														35	Song 2975
37														36	Song 579
38														37	Song 2883
39														38	Song 2948
40														39	Song 1876
41														40	Song 2082
Sheet1

Cell Formulas
Range	Formula
N2:N41	N2	=SEQUENCE(40)
O2:O41	O2	=INDEX('50-69'!A:A,TOCOL(IF(A2:A12="row",B2:K12,1/0),2))
Dynamic array formulas.

BTW, sample data you gave would be better with XL2BB so we could see what rows/columns the data is in (& possibly any row/column headings).

 

[Dan Wilson]

Good day Peter_SSs and thank you for your response. I had already started working with the response from Jeffrey Mahoney before seeing your response. I will print out what you sent and play with it later. It does look interesting and who knows, I may learn something.
Thank you,
Dan Wilson...

 

[Dan Wilson]

Use the Index formula I gave you. Replace SHEET2 with 50-69

Good day again Jeffrey. Outstanding!!! After playing with your formula, all is working! I modified the formula to the following:
=INDEX('50-69'!A:A,Sheet1!A4)
This way the formula checks all of the song entries, even after I add more to the worksheet. The "A4" element is then changed for each of the 40 entries. After accomplishing that, I then added a Column for each of the 40 entries to use the XLOOKUP function to access one of the Columns in the '50-69' worksheet that shows the last podcast number that the song was played in. My apologies for not recognizing that your formula was at the bottom of your first response and that it would work so well. Thank you again for the EXCELLENT help.
Dan Wilson...

 

[Peter_SSs]

@Dan Wilson
Glad you have something that is working as you want. Just pointing out that with your version of Excel you should be able to produce all 40 results with a single formula, rather than 40 separate formulas.

@Dan Wilson ; @Jeffrey Mahoney
I would strongly recommend that you do not use the text Sheet1 in any formula that is on Sheet1. It is not necessary and can lead to incorrect results in some circumstances. For an example see/try the example here (looks like I have mentioned this to you before Jeffrey )

So instead of
=INDEX('50-69'!A:A,Sheet1!A4)
change to
=INDEX('50-69'!A:A,A4)

 

[Dan Wilson]

@Dan Wilson
Glad you have something that is working as you want. Just pointing out that with your version of Excel you should be able to produce all 40 results with a single formula, rather than 40 separate formulas.

@Dan Wilson ; @Jeffrey Mahoney
I would strongly recommend that you do not use the text Sheet1 in any formula that is on Sheet1. It is not necessary and can lead to incorrect results in some circumstances. For an example see/try the example here (looks like I have mentioned this to you before Jeffrey )

So instead of
=INDEX('50-69'!A:A,Sheet1!A4)
change to
=INDEX('50-69'!A:A,A4)

Good day again Peter_SSs. I already took care of that. Thank you for the follow-up. This forum is absolutely great!
Dan Wilson...