Need formula to create pairs from a string number.

S

serge

Member
Hello everyone,

I'm looking for a formula that will create pairs from a row.
Open Firefox and copy this link :
http://www.box.com/s/45ec94551e7a738708a5

I would like to put a formula in R10 and drag it up to AZ10 and get all the pairs and when it run out of digits the formula return empty cells.
The reason there is 2 rows is because the string of digits can varies.

Thank you for any help.

Serge.
 
P

PAB

Member
Hi Serge,

I am unsure of your requirements.
Can you please tell me what Lotto you are playing and in which country?
I downloaded your file and it makes no sense to me at all, this is probably because it is a non UK Lotto.
If you can explain the Lotto you are playing and the fundamentals then I will be able to provide a way to extract the information you require.

Regards,
PAB
:wavey:

-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-
12:45, restate my assumptions.
Mathematics is the language of nature.
Everything around us can be represented and understood through numbers.
If you graph the numbers of any system, patterns emerge. Therefore, there are patterns everywhere in nature.
 
P

PAB

Member
Hi Serge,

Just a few questions.

(1) Will there always be 7 numbers in cells G10:O10?
(2) Will the numbers in cells G10:O10 always be the same numbers?
(3) Is there any reason why 09 has been omitted from cells R11:AZ11 in the second row under the No Duplicate section, and if so why?
(4) Does the No Duplicate principle also apply to the Duplicate Only section?
(5) I assume you want Excel formulas for these?

Regards,
PAB
:wavey:

-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-
12:45, restate my assumptions.
Mathematics is the language of nature.
Everything around us can be represented and understood through numbers.
If you graph the numbers of any system, patterns emerge. Therefore, there are patterns everywhere in nature.
 
P

PAB

Member
Hi Serge,

Firstly, please answer my questions in the previous post.
Secondly, I have put the following formulas together in such a way that they will accomodate ANY numbers in ANY order including cells that contain NO numeric value in the No Duplicate section.
You can copy and paste the formulas directly into the specified cells in your SpreadSheet.

PART 1

Put this formula in cell R10 and copy across to Y10
=IF(OR($G10="",H10=""),"",TEXT(CONCATENATE($G10,H10),"00"))

Put this formula in cell Z10 and copy across to AF10
=IF(OR($H10="",I10=""),"",TEXT(CONCATENATE($H10,I10),"00"))

Put this formula in cell AG10 and copy across to AL10
=IF(OR($I10="",J10=""),"",TEXT(CONCATENATE($I10,J10),"00"))

Put this formula in cell AM10 and copy across to AQ10
=IF(OR($J10="",K10=""),"",TEXT(CONCATENATE($J10,K10),"00"))

Put this formula in cell AR10 and copy across to AU10
=IF(OR($K10="",L10=""),"",TEXT(CONCATENATE($K10,L10),"00"))

Put this formula in cell AV10 and copy across to AX10
=IF(OR($L10="",M10=""),"",TEXT(CONCATENATE($L10,M10),"00"))

Put this formula in cell AY10 and copy across to AZ10
=IF(OR($M10="",N10=""),"",TEXT(CONCATENATE($M10,N10),"00"))

Put this formula in cell BA10
=IF(OR($N10="",O10=""),"",TEXT(CONCATENATE($N10,O10),"00"))

PART 2

Put this formula in cell R11 and copy across to Y11
=TEXT(CONCATENATE($G11,H11),"00")

Put this formula in cell Z11 and copy across to AF11
=TEXT(CONCATENATE($H11,I11),"00")

Put this formula in cell AG11 and copy across to AL11
=TEXT(CONCATENATE($I11,J11),"00")

Put this formula in cell AM11 and copy across to AQ11
=TEXT(CONCATENATE($J11,K11),"00")

Put this formula in cell AR11 and copy across to AU11
=TEXT(CONCATENATE($K11,L11),"00")

Put this formula in cell AV11 and copy across to AX11
=TEXT(CONCATENATE($L11,M11),"00")

Put this formula in cell AY11 and copy across to AZ11
=TEXT(CONCATENATE($M11,N11),"00")

Put this formula in cell BA11
=TEXT(CONCATENATE($N11,O11),"00")

I hope this is along the lines of what you are after.
Please let me know how you get on.

Regards,
PAB
:wavey:

-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-
12:45, restate my assumptions.
Mathematics is the language of nature.
Everything around us can be represented and understood through numbers.
If you graph the numbers of any system, patterns emerge. Therefore, there are patterns everywhere in nature.
 
S

serge

Member
Hi PAB,

(1) Will there always be 7 numbers in cells G10:O10?

No, it could be 3 or it could be 10, it will be different every giving time.

(2) Will the numbers in cells G10:O10 always be the same numbers?

No , those digits will change also.

(3) Is there any reason why 09 has been omitted from cells R11:AZ11 in the second row under the No Duplicate section, and if so why?

No that was a mistake, sorry !

(4) Does the No Duplicate principle also apply to the Duplicate Only section?

Yes, I need under " Duplicate Only " a formula to create a number of 2 same digits like the example showed.

(5) I assume you want Excel formulas for these?

Yes, I would like a formula.

Thank you very much for the help.
Serge.
 
S

serge

Member
Wow, just got your new post, that's a pretty long process, and it work, Thank you,

I'm wondering if it would be possible to have only one formula to put in cell R10 and drag across to AZ10 and would produce the same pairs without space in between ?

Thank you for your hard work.

Regards,
Serge.
 
P

PAB

Member
Hi Serge,

(Q1) Will there always be 7 numbers in cells G10:O10?
(A1) No, it could be 3 or it could be 10, it will be different every giving time.

(Q2) Will the numbers in cells G10:O10 always be the same numbers?
(A2) No, those digits will change also.

(Q3) Is there any reason why 09 has been omitted from cells R11:AZ11 in the second row under the No Duplicate section, and if so why?
(A3) No, that was a mistake, sorry!

(Q4) Does the No Duplicate principle also apply to the Duplicate Only section?
(A4) Yes, I need under "Duplicate Only" a formula to create a number of 2 same digits like the example showed.

(Q5) I assume you want Excel formulas for these?
(A5) Yes, I would like a formula.
Click to expand...
Addressing the questions and answers first in POST No. 6:-

(Q1) & (Q2)
I created the formulas assuming that ALL cells within the G10:O10 & G11:O11 ranges could have anything from one number to nine numbers in them. The formulas produce the correct data regardless.

I DON'T understand why ranges G10:O10 & G11:O11 only have the facility for NINE numbers though, I would have assumed that there should be TEN numbers, but that is what you asked for so that is what I provided.

(Q3) N/A.

(Q4) Here are the requested formulas.

Put this formula in cell R15 and copy across to Z15
=TEXT(CONCATENATE(G15,G15),"00")

Put this formula in cell R16 and copy across to Z16
=TEXT(CONCATENATE(G16,G16),"00")

I DON'T understand why ranges G10:O10 & G11:O11 only have the facility for NINE numbers though, I would have assumed that there should be TEN numbers, but that is what you asked for so that is what I provided.

(Q5) Supplied.

Addressing the question in POST No. 7:-

I'm wondering if it would be possible to have only one formula to put in cell R10 and drag across to AZ10 and would produce the same pairs without space in between?
Click to expand...
It is not that simple because you are dealing with multiple ranges as opposed to one.

Let me know how you get on!

Regards,
PAB
:wavey:

-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-
12:45, restate my assumptions.
Mathematics is the language of nature.
Everything around us can be represented and understood through numbers.
If you graph the numbers of any system, patterns emerge. Therefore, there are patterns everywhere in nature.
 
P

PAB

Member
Hi Serge,

I have just been thinking about this.

In your SpreadSheet you have the facility for 9 numbers. The total combinations of 2 numbers (pairs) from 9 numbers is 36 combinations.
The Excel formula for this is:-
=COMBIN(9,2)

In you were to use the FULL 10 numbers (0,1,2,3,4,5,6,7,8,9) the total combinations of 2 numbers (pairs) from 10 numbers is 45 combinations.
The Excel formula for this is:-
=COMBIN(10,2)

If this is what you actually want instead of what you have asked for then please let me know and I will provide the formulas accordingly.

Regards,
PAB
:wavey:

-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-
12:45, restate my assumptions.
Mathematics is the language of nature.
Everything around us can be represented and understood through numbers.
If you graph the numbers of any system, patterns emerge. Therefore, there are patterns everywhere in nature.
 
S

serge

Member
Hi PAB,

The range from G10:O10 & G11:O11 don't have a facility of 10 because with my system that I created need a maximum of 9 or less.

Thank you very much for your help, that will make my work easier.

Regards,
Serge.
 
P

PAB

Member
Hi Serge,

I am pleased you have got what you need and your request is resolved.

Regards,
PAB
:wavey:

-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-
12:45, restate my assumptions.
Mathematics is the language of nature.
Everything around us can be represented and understood through numbers.
If you graph the numbers of any system, patterns emerge. Therefore, there are patterns everywhere in nature.
 
You must log in or register to reply here.

Sidebar

Top