excel formula to combine like pairsPrev Topic Next Topic

New Topic New Poll

Hoster

United States
Member #184,210
August 30, 2017
1,140 Posts
Offline

Sep 29, 2017, 2:53 am

looked around LP and net -can't find an excel formula to combine like pairs.

01 02 09, 13, 15, 16, 18 etc. = 012 019 013 135 136 138 etc.

Any help appreciated
thamizhpayan

United States
Member #173,010
February 13, 2016
1,629 Posts
Offline

Sep 29, 2017, 12:15 pm

Quote: Originally posted by Hoster on Sep 29, 2017
looked around LP and net -can't find an excel formula to combine like pairs.

01 02 09, 13, 15, 16, 18 etc. = 012 019 013 135 136 138 etc.

Any help appreciated

Try this

https://www.lotterypost.com/thread/311426/5138629
Hoster

United States
Member #184,210
August 30, 2017
1,140 Posts
Offline

Sep 29, 2017, 12:35 pm

Quote: Originally posted by Hoster on Sep 29, 2017
looked around LP and net -can't find an excel formula to combine like pairs.

01 02 09, 13, 15, 16, 18 etc. = 012 019 013 135 136 138 etc.

Any help appreciated

here what I have. it kind of works but only if I hold each cell individually by copy the formula and drop down into a new row. I want to keep the formula in one row.

what i mean - A1:AK1 holds the pairs . A2:AK2 hold the formula. I can make cell A1 in the formula absolute and compare all in row 1. But then to get the next pair to combine I have to copy the formula into A3 then make B1 absolute in the formula. make sense?

pairs are in random order due to the way they are gathered (by different formula) Duplicate are okay. I can sort but then I can't back test unless I do it manually (trying to avoid that)

here's what I am getting when I try to do them all in one row. brain dead here. VB maybe? but dead brain gets head ache now. Thanks for ur time.

02 02 22 12 13 23 94 97 47 66 65 65 69 66 96 49 44 94 91 98 18 37 33 73 66 63 63 55 56 56 60 65 05 65 65 55 00

002 222 212 223 060 005 000

=IF(SUMPRODUCT(--(LEFT($A$1:$A$1,1)=LEFT(B$1))),LEFT($A$1,1)&B$1,IF(SUMPRODUCT(--(LEFT($A$1:$A$1,1)=RIGHT(B$1))),LEFT($A$1,1)&B$1,IF(SUMPRODUCT(--(RIGHT($A$1:$A$1,1)=LEFT(B$1))),RIGHT($A$1,1)&B$1,IF(SUMPRODUCT(--(RIGHT($A$1:$A$1,1)=RIGHT(B$1))),RIGHT($A$1,1)&B$1,""))))

With duplicates involved the results may look something like this and will most likely also stretch much farther than A1:AK1 … that is okay too.

020 020 022 022 026 026 025 025 000 221 223 123 129 128 etc...
Hoster

United States
Member #184,210
August 30, 2017
1,140 Posts
Offline

Sep 29, 2017, 1:14 pm

Quote: Originally posted by thamizhpayan on Sep 29, 2017
Try this

https://www.lotterypost.com/thread/311426/5138629

thamizhpayan thanks this is a handy dandy tool indeed -I have other pairs using long out vtracs which this works great for -because we know where position x is.

however, pairs on the prior post are in randomness -even so they are straight pairs for the most part -but trying to decide which place to put x in is unknown. We have found that by combining like pairs, we get more straight hits than trying to guess where to place them (creates too many combo's).
Hoster

United States
Member #184,210
August 30, 2017
1,140 Posts
Offline

Sep 29, 2017, 1:50 pm

Quote: Originally posted by Hoster on Sep 29, 2017
thamizhpayan thanks this is a handy dandy tool indeed -I have other pairs using long out vtracs which this works great for -because we know where position x is.

however, pairs on the prior post are in randomness -even so they are straight pairs for the most part -but trying to decide which place to put x in is unknown. We have found that by combining like pairs, we get more straight hits than trying to guess where to place them (creates too many combo's).

Still looking for excel solution to combine like pairs as in this example

Thanks for your time
Oscargrouch05$

Emporia,Kansas
United States
Member #178,898
December 22, 2016
3,855 Posts
Offline

Oct 1, 2017, 3:35 am

I made this in excel first number on the left are 1st position 4,2 Second position 7,8 i'm starting to work on that last number i use Excel 2003 any tips on that last position welcome any idea as well for reducing the amount of numbers . I used my own format of flip numbers using 666 system, 317 Rundown, mirror and 6 and 3 system, plus 21 pairs why i used them is if you use anything like =MOD(SUM(A3-8),10) you will have a random numbers from 0 throw 9

using cell A3,B3,C3 A3 (2) , B3 (8) , C3 (4)
example =CONCATENATE(A4,B4) pick3 number 28 shows up

47 48

27 28

Example of flip numbers

0-6

2-7

3-8

4-9

5-0
Oscargrouch05$

Emporia,Kansas
United States
Member #178,898
December 22, 2016
3,855 Posts
Offline

Oct 1, 2017, 4:00 am

Pairs with spaces on one line using one cell example =CONCATENATE(A3,B3," ",B3,C3) shows as 28 84
Raven62

25

New Jersey
United States
Member #17,842
June 28, 2005
180,983 Posts
Offline

Oct 1, 2017, 10:11 am

Maybe it's Time for another LP Pick 3 Tool to Join Pairs (Str8 & Boxed)!

A mind once stretched by a new idea never returns to its original dimensions!

Catch-22: A dilemma or difficult circumstance from which there is no escape because of mutually conflicting or dependent conditions.

Corruptissima re publica plurimae leges: When the republic is at its most corrupt the laws are most numerous.

The best way to learn is to never stop being an Experiential Student!
Ricklou

South Africa
Member #129,816
June 28, 2012
1,706 Posts
Offline

Oct 1, 2017, 5:15 pm

Quote: Originally posted by Oscargrouch05$ on Oct 1, 2017
Pairs with spaces on one line using one cell example =CONCATENATE(A3,B3," ",B3,C3) shows as 28 84

Thats it
thamizhpayan

United States
Member #173,010
February 13, 2016
1,629 Posts
Offline

Oct 4, 2017, 11:09 pm

Hi,

Try v1.2 of my pairs combinator tool : https://drive.google.com/open?id=0B4Qt2_2g3zs5SWdqMEk0N1dXNzQ

Just extract to a folder and then open the HTML file. I added another form to enter pairs and print combos(all possible orders) and box combos of the straights created from the pairs. There is a sample output screenshot in the same zip file.

Good Luck
Hoster

United States
Member #184,210
August 30, 2017
1,140 Posts
Offline

Oct 5, 2017, 7:32 pm
OKAY HOPE THIS HELPS SOMEONE ELSE

See the example and steps below. and remember no one said this was easy yet.

combine pair sets. It is tedious for larger data because every time you add a pair into Pairs set 1, you have to add another 4 cells to the right of D1 combined and call it E1 combined.

I split this example up into 2 pair sets so you can see what is comparing to what and how it comes out.

On the right are 4 combined areas. each area represents one for cell A1, and one for cell B1, and so on. If you look close at the formulas, you can see the changes are simple but necessary.

It is a lot of work to set up, but it gets the job done. If you want the sets in one line that is okay. just know you have to continue to add x times the number of pairs on the left, to the right side as I have done.

In other words, if you add one more pair to the left side, then you need add one more cell to each group on the right side..., plus, you need to add another group to the group of 4 to make 5 groups of five cells on the right.

So if you have 20 pairs on the left side you have to make a 400 cell stretch on the right lol. yes it's 20 x 20 = 400. I made mine 45... I know your pain

here ya go:
1. set up A1:D1 with pairs
2. Do the same for F1:I1 (notew: you can run A1:D1 together with F1:I1 in one line, but for sake of understanding leave it this way for now)
3. In N1 paste this formula and fill to right 4 cells (N1:Q1)
4. =IF(LEFT($A2,1)=LEFT(F2,1),$A2&RIGHT(F2,1),IF(LEFT($A2,1)=RIGHT(F2,1),$A2&LEFT(F2,1),IF(RIGHT($A2,1)=LEFT(F2,1),$A2&RIGHT(F2,1),IF(RIGHT($A2,1)=RIGHT(F2,1),$A2&LEFT(F2,1),IF(LEFT($A2,1)=RIGHT(F2,1),$A2&RIGHT(F2,1),"")))))
5. In R1 paste this formula and fill to right 4 cells (R1:U1)
6. =IF(LEFT($B2,1)=LEFT(F2,1),$B2&RIGHT(F2,1),IF(LEFT($B2,1)=RIGHT(F2,1),$B2&LEFT(F2,1),IF(RIGHT($B2,1)=LEFT(F2,1),$B2&RIGHT(F2,1),IF(RIGHT($B2,1)=RIGHT(F2,1),$B2&LEFT(F2,1),IF(LEFT($B2,1)=RIGHT(F2,1),$B2&RIGHT(F2,1),"")))))
7. In V1 paste this formula and fill to right 4 cells (V1:Y1)
8. =IF(LEFT($C2,1)=LEFT(F2,1),$C2&RIGHT(F2,1),IF(LEFT($C2,1)=RIGHT(F2,1),$C2&LEFT(F2,1),IF(RIGHT($C2,1)=LEFT(F2,1),$C2&RIGHT(F2,1),IF(RIGHT($C2,1)=RIGHT(F2,1),$C2&LEFT(F2,1),IF(LEFT($C2,1)=RIGHT(F2,1),$C2&RIGHT(F2,1),"")))))
9. In Z1 paste this formula and fill to right 4 cells (Z1:AC1)
10. =IF(LEFT($D2,1)=LEFT(F2,1),$D2&RIGHT(F2,1),IF(LEFT($D2,1)=RIGHT(F2,1),$D2&LEFT(F2,1),IF(RIGHT($D2,1)=LEFT(F2,1),$D2&RIGHT(F2,1),IF(RIGHT($D2,1)=RIGHT(F2,1),$D2&LEFT(F2,1),IF(LEFT($D2,1)=RIGHT(F2,1),$D2&RIGHT(F2,1),"")))))
thamizhpayan

United States
Member #173,010
February 13, 2016
1,629 Posts
Offline

Oct 11, 2017, 11:54 am
Quote: Originally posted by Hoster on Oct 5, 2017
OKAY HOPE THIS HELPS SOMEONE ELSE

See the example and steps below. and remember no one said this was easy yet.

combine pair sets. It is tedious for larger data because every time you add a pair into Pairs set 1, you have to add another 4 cells to the right of D1 combined and call it E1 combined.

I split this example up into 2 pair sets so you can see what is comparing to what and how it comes out.

On the right are 4 combined areas. each area represents one for cell A1, and one for cell B1, and so on. If you look close at the formulas, you can see the changes are simple but necessary.

It is a lot of work to set up, but it gets the job done. If you want the sets in one line that is okay. just know you have to continue to add x times the number of pairs on the left, to the right side as I have done.

In other words, if you add one more pair to the left side, then you need add one more cell to each group on the right side..., plus, you need to add another group to the group of 4 to make 5 groups of five cells on the right.

So if you have 20 pairs on the left side you have to make a 400 cell stretch on the right lol. yes it's 20 x 20 = 400. I made mine 45... I know your pain

here ya go:
1. set up A1:D1 with pairs
2. Do the same for F1:I1 (notew: you can run A1:D1 together with F1:I1 in one line, but for sake of understanding leave it this way for now)
3. In N1 paste this formula and fill to right 4 cells (N1:Q1)
4. =IF(LEFT($A2,1)=LEFT(F2,1),$A2&RIGHT(F2,1),IF(LEFT($A2,1)=RIGHT(F2,1),$A2&LEFT(F2,1),IF(RIGHT($A2,1)=LEFT(F2,1),$A2&RIGHT(F2,1),IF(RIGHT($A2,1)=RIGHT(F2,1),$A2&LEFT(F2,1),IF(LEFT($A2,1)=RIGHT(F2,1),$A2&RIGHT(F2,1),"")))))
5. In R1 paste this formula and fill to right 4 cells (R1:U1)
6. =IF(LEFT($B2,1)=LEFT(F2,1),$B2&RIGHT(F2,1),IF(LEFT($B2,1)=RIGHT(F2,1),$B2&LEFT(F2,1),IF(RIGHT($B2,1)=LEFT(F2,1),$B2&RIGHT(F2,1),IF(RIGHT($B2,1)=RIGHT(F2,1),$B2&LEFT(F2,1),IF(LEFT($B2,1)=RIGHT(F2,1),$B2&RIGHT(F2,1),"")))))
7. In V1 paste this formula and fill to right 4 cells (V1:Y1)
8. =IF(LEFT($C2,1)=LEFT(F2,1),$C2&RIGHT(F2,1),IF(LEFT($C2,1)=RIGHT(F2,1),$C2&LEFT(F2,1),IF(RIGHT($C2,1)=LEFT(F2,1),$C2&RIGHT(F2,1),IF(RIGHT($C2,1)=RIGHT(F2,1),$C2&LEFT(F2,1),IF(LEFT($C2,1)=RIGHT(F2,1),$C2&RIGHT(F2,1),"")))))
9. In Z1 paste this formula and fill to right 4 cells (Z1:AC1)
10. =IF(LEFT($D2,1)=LEFT(F2,1),$D2&RIGHT(F2,1),IF(LEFT($D2,1)=RIGHT(F2,1),$D2&LEFT(F2,1),IF(RIGHT($D2,1)=LEFT(F2,1),$D2&RIGHT(F2,1),IF(RIGHT($D2,1)=RIGHT(F2,1),$D2&LEFT(F2,1),IF(LEFT($D2,1)=RIGHT(F2,1),$D2&RIGHT(F2,1),"")))))
Hi,

Try to use macros in excel. You don't have to copy paste.

You can simply specify ranges and let excel do what you want.

BTW, did you try the HTML version that I gave earlier. Version 1.2 gives you combos as you expected

Good Luck