Combine two lists by removing duplicates in ExcelPrev Topic Next Topic

Mayte

Mexico
Member #193,539
November 3, 2018
369 Posts
Offline

Mar 25, 2022, 12:10 am

hello friends I want to combine the lists of the total combinations of a lottery 6/39 minus the total combinations already eliminating the numbers that came out in the last draw but excel only supports a million combinations in its cells does anyone know another method to do this elimination of these two lists of combinations, greetings....
ethanl

Australia
Member #218,237
October 24, 2021
137 Posts
Offline

Mar 25, 2022, 1:50 am

lol million combinations excell will brake , not a good idea

garyo1954

Texas
United States
Member #4,549
May 2, 2004
4,468 Posts
Offline

Mar 25, 2022, 11:10 am

Quote: Originally posted by Mayte on Mar 25, 2022

hello friends I want to combine the lists of the total combinations of a lottery 6/39 minus the total combinations already eliminating the numbers that came out in the last draw but excel only supports a million combinations in its cells does anyone know another method to do this elimination of these two lists of combinations, greetings....

Once you've gone to the trouble of making a list of total combinations, why destroy it when there are several options to show a combination has hit.

1. Change the color of the text.
2. Highlight it in a different color.
3. Make a HIT column. (Each time a combination hits go in and add 1)
4. Instead of HIT use DATE. And enter the DATE it hit.

DR	TX$	N1	N2	N3	N4	HIT
4671	TX0113	1	15	16	33	4
12704	TX0022	3	6	26	29	4
20777	TX0223	4	26	27	29	4
23233	TX0112	5	13	16	24	4
48990	TX1223	17	22	28	32	4
2217	TX0022	1	6	20	27	3
3377	TX0033	1	9	31	34	3

You're aware of the limits of Excel so you know you will need three to four files anyway. And knowing that, no sense in destroying the work you've done.

G

I'm probably here unless I'm not.
Dreaming would be a perfectly useless function if it's only purpose was to entertain.

Mayte

Mexico
Member #193,539
November 3, 2018
369 Posts
Offline

Mar 25, 2022, 12:05 pm

thanks garyo1954 I try to reduce and eliminate combinations of a total of combinations of a lottery, since many times the numbers of a lottery 6/39 are not repeated, greetings...
Speler

Belgium
Member #214,850
April 17, 2021
2,445 Posts
Offline

Mar 25, 2022, 5:46 pm

There is a shortcut to it, you don't write those that you want to eliminate. You must eat more smarties. You can order them online. 15 kg box! Order now! You may need 3 boxes.
Mayte

Mexico
Member #193,539
November 3, 2018
369 Posts
Offline

Mar 25, 2022, 6:10 pm

What are you talking about??????
Speler

Belgium
Member #214,850
April 17, 2021
2,445 Posts
Offline

Mar 25, 2022, 6:20 pm

Quote: Originally posted by Mayte on Mar 25, 2022
hello friends I want to combine the lists of the total combinations of a lottery 6/39 minus the total combinations already eliminating the numbers that came out in the last draw but excel only supports a million combinations in its cells does anyone know another method to do this elimination of these two lists of combinations, greetings....

39-6=33

combin ( 39 - 6 , 6 )
= 1107568
combin ( 39 , 6 )
= 3262623
Speler

Belgium
Member #214,850
April 17, 2021
2,445 Posts
Offline

Mar 25, 2022, 6:22 pm

How many do you want to play and are you able to play? 10? 5? 2? 1? 0?
garyo1954

Texas
United States
Member #4,549
May 2, 2004
4,468 Posts
Offline

Mar 25, 2022, 6:40 pm

Quote: Originally posted by Mayte on Mar 25, 2022
What are you talking about??????

He's experiencing a sugar rush from too many Smarties.

G

I'm probably here unless I'm not.
Dreaming would be a perfectly useless function if it's only purpose was to entertain.
Speler

Belgium
Member #214,850
April 17, 2021
2,445 Posts
Offline

Mar 25, 2022, 6:42 pm

Quote: Originally posted by garyo1954 on Mar 25, 2022
He's experiencing a sugar rush from too many Smarties.

G

... and a kind of minestrone.
timmsy

Australia
Member #154,619
April 23, 2014
228 Posts
Offline

Mar 26, 2022, 5:49 am

just use a VBA in excell

https://www.educba.com/vba-remove-duplicates/
cottoneyedjoe

United States
Member #197,022
March 28, 2019
1,749 Posts
Offline

Mar 26, 2022, 2:38 pm

Quote: Originally posted by Mayte on Mar 25, 2022
hello friends I want to combine the lists of the total combinations of a lottery 6/39 minus the total combinations already eliminating the numbers that came out in the last draw but excel only supports a million combinations in its cells does anyone know another method to do this elimination of these two lists of combinations, greetings....

A friendly suggestion would be to use Google Sheets for your project rather than Excel. Google Sheets supports up to 5 million rows and is less prone to crashing than Excel. Your 6/39 game has 3262623 combinations, which is less than 5 million (and a palindrome!)

If you really can't part with Excel, this project will be more manageable if you divide the 3262623 combos into 10 sheets of 320000 or so.

Best of luck to you, Mayte.
Speler

Belgium
Member #214,850
April 17, 2021
2,445 Posts
Offline

Mar 26, 2022, 2:48 pm

When you're done with that, I want you to print that on paper. All you printed on your sheets must be on your computer. Check that! You mark matching lines with a sharpened pencil! PM me when you have finished!

garyo1954

Texas
United States
Member #4,549
May 2, 2004
4,468 Posts
Offline

Mar 26, 2022, 4:32 pm

It can be done in one Excel sheet, but not a continuous list from 1 to 3262623.

You may only have 1,000,000 down but you have 16,000+ across.
So it would look something like this....

1	1	2	3	4	5	6	1041536	3	6	8	9	10	11	2045630	6	15	16	17	18	19	3071732	16	17	18	19	20	21
2	1	2	3	4	5	7	1041537	3	6	8	9	10	12	2045631	6	15	16	17	18	20	3071733	16	17	18	19	20	22
3	1	2	3	4	5	8	1041538	3	6	8	9	10	13	2045632	6	15	16	17	18	21	3071734	16	17	18	19	20	23
4	1	2	3	4	5	9	1041539	3	6	8	9	10	14	2045633	6	15	16	17	18	22	3071735	16	17	18	19	20	24
5	1	2	3	4	5	10	1041540	3	6	8	9	10	15	2045634	6	15	16	17	18	23	3071736	16	17	18	19	20	25
6	1	2	3	4	5	11	1041541	3	6	8	9	10	16	2045635	6	15	16	17	18	24	3071737	16	17	18	19	20	26
7	1	2	3	4	5	12	1041542	3	6	8	9	10	17	2045636	6	15	16	17	18	25	3071738	16	17	18	19	20	27
8	1	2	3	4	5	13	1041543	3	6	8	9	10	18	2045637	6	15	16	17	18	26	3071739	16	17	18	19	20	28
9	1	2	3	4	5	14	1041544	3	6	8	9	10	19	2045638	6	15	16	17	18	27	3071740	16	17	18	19	20	29
10	1	2	3	4	5	15	1041545	3	6	8	9	10	20	2045639	6	15	16	17	18	28	3071741	16	17	18	19	20	30
11	1	2	3	4	5	16	1041546	3	6	8	9	10	21	2045640	6	15	16	17	18	29	3071742	16	17	18	19	20	31
12	1	2	3	4	5	17	1041547	3	6	8	9	10	22	2045641	6	15	16	17	18	30	3071743	16	17	18	19	20	32
13	1	2	3	4	5	18	1041548	3	6	8	9	10	23	2045642	6	15	16	17	18	31	3071744	16	17	18	19	20	33
14	1	2	3	4	5	19	1041549	3	6	8	9	10	24	2045643	6	15	16	17	18	32	3071745	16	17	18	19	20	34
15	1	2	3	4	5	20	1041550	3	6	8	9	10	25	2045644	6	15	16	17	18	33	3071746	16	17	18	19	20	35
16	1	2	3	4	5	21	1041551	3	6	8	9	10	26	2045645	6	15	16	17	18	34	3071747	16	17	18	19	20	36
17	1	2	3	4	5	22	1041552	3	6	8	9	10	27	2045646	6	15	16	17	18	35	3071748	16	17	18	19	20	37
18	1	2	3	4	5	23	1041553	3	6	8	9	10	28	2045647	6	15	16	17	18	36	3071749	16	17	18	19	20	38
19	1	2	3	4	5	24	1041554	3	6	8	9	10	29	2045648	6	15	16	17	18	37	3071750	16	17	18	19	20	39
20	1	2	3	4	5	25	1041555	3	6	8	9	10	30	2045649	6	15	16	17	18	38	3071751	16	17	18	19	21	22
21	1	2	3	4	5	26	1041556	3	6	8	9	10	31	2045650	6	15	16	17	18	39	3071752	16	17	18	19	21	23
22	1	2	3	4	5	27	1041557	3	6	8	9	10	32	2045651	6	15	16	17	19	20	3071753	16	17	18	19	21	24
23	1	2	3	4	5	28	1041558	3	6	8	9	10	33	2045652	6	15	16	17	19	21	3071754	16	17	18	19	21	25
24	1	2	3	4	5	29	1041559	3	6	8	9	10	34	2045653	6	15	16	17	19	22	3071755	16	17	18	19	21	26
25	1	2	3	4	5	30	1041560	3	6	8	9	10	35	2045654	6	15	16	17	19	23	3071756	16	17	18	19	21	27

If all you're looking for is a listing, there you go.
For just eliminating combinations this would work, but after doing myself, I'd use 3 to 4 files at about 820,000 combinations each.

G

I'm probably here unless I'm not.
Dreaming would be a perfectly useless function if it's only purpose was to entertain.

Mayte

Mexico
Member #193,539
November 3, 2018
369 Posts
Offline

Mar 26, 2022, 10:23 pm

thanks garyo1954

New Topic New Poll

Subscribe to this topic

Combine two lists by removing duplicates in ExcelPrev TopicNext Topic

Combine two lists by removing duplicates in ExcelPrev Topic Next Topic