Help me on permutation plz..

S

sosrivi

New Member
Joined
Jan 18, 2023
Messages
6
Office Version
  1. 365
  2. 2021
Platform
  1. Windows
Hello, I am trying to create a 4d lottery data in excel spread sheet from our website. I will receive a set of 23 luck number. i have to match with multiple user betting number and find out the luck number matches with user bet. The problem is i can identify the straight number (eg 1st prize is 1234 and a user pick 1234 i can match it easily) But instead of 1234 if they wrote 4321 it comes under permutation (24,12,6,4,1) how can match with the luck number. plz guide me.
 

Attachments

  • test.png
    test.png
    22.2 KB · Views: 8

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
Sort by date Sort by votes
Welcome to the Forum!

Using Excel 365, you can generate your permutations like this:

ABC
1
2Permutations
3000112341212
4
5000112341212
6001012431221
7010013241122
8100013422112
914232121
1014322211
112134
122143
132314
142341
152413
162431
173124
183142
193214
203241
213412
223421
234123
244132
254213
264231
274312
284321
29
Sheet2
Cell Formulas
RangeFormula
A5:A8,C5:C10,B5:B28A5=LET(d,4,n,1+MOD(INT(SEQUENCE(d^d,,0)/d^SEQUENCE(,d,d-1,-1)),d),p,--UNIQUE(BYROW(MID(TEXT(A3,REPT("0",d)),FILTER(n,BYROW(n,LAMBDA(r,d=COUNT(UNIQUE(r,1))))),1),LAMBDA(r,CONCAT(r)))),p)
Dynamic array formulas.

Hence:

ABCDEFG
1
2LuckyPickMatch?
3
4326371231273
52447026706720726
667091234No
7552361581865
8303478657865
907262345No
104841
114050
123397
130023
143240
151865
167865
171273
183874
190733
202192
210672
223873
235881
240846
250982
260001
27
Sheet1
Cell Formulas
RangeFormula
E4,E6:E9,E5:F5E4=LET(d,4,n,1+MOD(INT(SEQUENCE(d^d,,0)/d^SEQUENCE(,d,d-1,-1)),d),p,--UNIQUE(BYROW(MID(TEXT(D4,REPT("0",d)),FILTER(n,BYROW(n,LAMBDA(r,d=COUNT(UNIQUE(r,1))))),1),LAMBDA(r,CONCAT(r)))),TRANSPOSE(FILTER(p,ISNUMBER(MATCH(p,B$4:B$26,)),"No")))


PS: My numbers are formatted "0000"
 
Last edited:
Upvote 0
Thank you so much for replying back for my post. i am very new to excel and learning new things day by day. I had attach the excel file along with this post. plz guide me on that sheet how to find a solution for my problem. i know am asking for more. but my work is manage 13th race per day. i am have to enter manually and cross check all. if i setup my 1 sheet properly i can manage result them easily. any new or modification totally welcomed. waiting for ur reply. thank you again.

test file..xlsx
ABCDEFGHIJKLMNOPQR
1User Digits
2ResultSenRameshKumarMiniGaviAllanManojVinoParthaParkash
30724172409284009096974256618976772485909
4Lotto19-01-20235413753402202978139959579504513955557158
5FirstSecondThird7770409172576686557670339157735949354152
65077544415225280785095881572853252511943227741371885
79396119417185748155156326195367448844324
8Special3419160127493493949195835909688375756015
9616644240008648720914374800033133994271506309586662808984139
10369875013158059800797715090656137874698003798784134245237183
119562686433844370894981691856877093027365
12Consolation8916833132332159916038944258974736597128
13386645366340939600802993693684501070100512498600736439993035
1491727941553421317002430882050305537535099118263120560026
15154354590083442471671006871741243944
1625852495282013657256011164764286
179348120361051099666480466340
18710370115582902341712957
19240731940527790580479234
2055116136809566899865
21609946434033
2287272446
2375878242
247651
253575
26
27
Sheet1
Cell Formulas
RangeFormula
G4G4=TODAY()
 
Upvote 0
Check the image for more detail. thank you again.
 

Attachments

  • Untitled.png
    Untitled.png
    56.4 KB · Views: 6
Upvote 0
Try this:

Book9
ABCDEFGHIJKLMNOPQRSTUV
1CHECK RESULTSLucky
2NumbersGaviMatch?ResultSenRameshKumarMiniGaviAllanManojVinoParthaParkash
31st23580969-0724172409284009096974256618976772485909
42nd54441399-Lotto19/01/20235413753402202978139959579504513955557158
53rd15225576-FirstSecondThird7770409172576686557670339157735949354152
6Special61668532Consolation1st5077544415225280785095881572853252511943227741371885
7Special36981551-9396119417185748155156326195367448844324
8Special44249491-Special3419160127493493949195835909688375756015
9Special75012715-616644248648720914374800033133994271506309586662808984139
10Special00086980-369875013158598797715090656137874698003798784134245237183
11Special31588949-9562686433844370894981691856877093027365
12Special64879160-Consolation8916833132332159916038944258974736597128
13Special05981005-3866453663409396802993693684501070100512498600736439993035
14Special20915375-91725328553421317002430882050305537535099118263120560026
15Special00794424Special154354590083442471671006871741243944
16Consolation3866-25852495282013657256011164764286
17Consolation9172-9348120361051099666480466340
18Consolation4536-710370115582902341712957
19Consolation5328-240731940527790580479234
20Consolation6340-55116136809566899865
21Consolation5534-609946434033
22Consolation9396-87272446
23Consolation2131-75878242
24Consolation0080-7651
25Consolation7002-3575
26
Sheet1
Cell Formulas
RangeFormula
C3:C15C3=LET(n,XLOOKUP(C2,M2:V2,M3:V28),FILTER(n,n<>0))
K4K4=TODAY()
B4B4=I6
B5B5=K6
B6:B15B6=TOCOL(G9:K10,,1)
B16:B25B16=TOCOL(G13:K14,,1)
D3:D5,D7:D25,D6:E6D3=IFERROR(INDEX(A$3:A$25,MATCH(LET(d,4,n,1+MOD(INT(SEQUENCE(d^d,,0)/d^SEQUENCE(,d,d-1,-1)),d),p,--UNIQUE(BYROW(MID(TEXT(C3,REPT("0",d)),FILTER(n,BYROW(n,LAMBDA(r,d=COUNT(UNIQUE(r,1))))),1),LAMBDA(r,CONCAT(r)))),TRANSPOSE(FILTER(p,ISNUMBER(MATCH(p,LuckyNumbers,))))),LuckyNumbers,)),"-")
Dynamic array formulas.
Named Ranges
NameRefers ToCells
LuckyNumbers=Sheet1!$B$3:$B$25D3:D25
Cells with Data Validation
CellAllowCriteria
C2List=$M$2:$V$2
 
Upvote 0
Solution
i am getting really hard time to understand the step. I am very much a amateur user, if u dont mind can u send me this excel file to my gmail. so i can edit, modify( just change of the value of the luck number(23) and multi user value) and use it for my work.
 
Last edited by a moderator:
Upvote 0
No need for anyone to send files, you can simply click on the copy icon of the mini-sheet & paste the data (including formulae) into A1 of a blank sheet.
1674132489475.png
 
Upvote 0
thank for letting me know that option. previously i tired to paste the file it comes in to the sheet. it comes in an improper way. that is the reason i asked for. thank u again.
 

Attachments

  • Untitled.png
    Untitled.png
    57.2 KB · Views: 4
Upvote 0
If you normally use semi-colons as the separator in a formula, then you will need to change the commas to semi-colons.
 
Upvote 0
Fluff said:
If you normally use semi-colons as the separator in a formula, then you will need to change the commas to semi-colons.
Click to expand...
Its shows function is in valid. never mind thank u all for trying to make me understand.
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Question
Need help on Permutation & Occurance Count
Replies
3
Views
276
DigitNoob
D
T
  • Solved
Weighted lottery help?
2
Replies
14
Views
2K
bebo021999
B
M
  • Solved
Need help modify to pick any first number of my choice
Replies
6
Views
285
motilulla
M
A
  • Question
Lookup Help! Data table is dynamically dependent on data present in a row against which lookup is needed.
Replies
1
Views
392
asadriaz
A
J
  • Question
Need some help with permutation code, convert from one cell to a range
Replies
3
Views
266
johnnyL
J

Forum statistics

Threads
1,215,143
Messages
6,123,285
Members
449,094
Latest member
GoToLeep

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top