Excel QuestionPrev Topic Next Topic

New Topic New Poll

TasBob

Bowling Green ,Florida
United States
Member #108,728
March 30, 2011
6,547 Posts
Offline

Jan 15, 2020, 4:39 pm

Lets say I want to look at last months numbers,and find the numbers with 5 that hit.

It would like this.

714

079

765 765

983

869

815 TRUE

IF It comes out with (815) TRUE could True be moved to the next cell over with 815 in it?

Thank You Tas
phileight

United States
Member #41,845
June 23, 2006
717 Posts
Offline

Jan 15, 2020, 5:51 pm

assuming you are willing to use a macro easy enough. see code below. you will need to make some adjustments to fit your layout , but the comments should tell you what to adjust

Sub find_num()

' rw=row to start clm= column of data nm= number to find
nm = 5
rw = 3' starting row of data. adjust as needed
clm = 3 ' where your combination list is stored. adjust as desired
For r = rw To rw + 50 ' assumes you want to check 50 lines. adjust as desired
a = Cells(r, clm) ' a is the combination to check
Ln = Len(a) 'how many digits in the target number 001=1 023=2 123=3
For y = 1 To Ln ' start a loop to break down the combination being checked into individual digits
d = Val(Mid(a, y, 1)) ' get the value of the digit at position y
If d = nm Then Cells(r, 6) = a ' if digit matches target number place combination in col 6. adjust as desired
Next y
Next r

End Sub
TasBob

Bowling Green ,Florida
United States
Member #108,728
March 30, 2011
6,547 Posts
Offline

Jan 16, 2020, 7:58 am

Quote: Originally posted by phileight on Jan 15, 2020
assuming you are willing to use a macro easy enough. see code below. you will need to make some adjustments to fit your layout , but the comments should tell you what to adjust

Sub find_num()

' rw=row to start clm= column of data nm= number to find
nm = 5
rw = 3' starting row of data. adjust as needed
clm = 3 ' where your combination list is stored. adjust as desired
For r = rw To rw + 50 ' assumes you want to check 50 lines. adjust as desired
a = Cells(r, clm) ' a is the combination to check
Ln = Len(a) 'how many digits in the target number 001=1 023=2 123=3
For y = 1 To Ln ' start a loop to break down the combination being checked into individual digits
d = Val(Mid(a, y, 1)) ' get the value of the digit at position y
If d = nm Then Cells(r, 6) = a ' if digit matches target number place combination in col 6. adjust as desired
Next y
Next r

End Sub

Thank You

I never used macro , maybe someone else has some ideas.

A few years back when I was more active a lot of your post help me a lot ..Thanks Again
ram

South Texas
United States
Member #11
December 6, 2001
307 Posts
Offline

Jan 17, 2020, 7:55 am

Quote: Originally posted by phileight on Jan 15, 2020
assuming you are willing to use a macro easy enough. see code below. you will need to make some adjustments to fit your layout , but the comments should tell you what to adjust

Sub find_num()

' rw=row to start clm= column of data nm= number to find
nm = 5
rw = 3' starting row of data. adjust as needed
clm = 3 ' where your combination list is stored. adjust as desired
For r = rw To rw + 50 ' assumes you want to check 50 lines. adjust as desired
a = Cells(r, clm) ' a is the combination to check
Ln = Len(a) 'how many digits in the target number 001=1 023=2 123=3
For y = 1 To Ln ' start a loop to break down the combination being checked into individual digits
d = Val(Mid(a, y, 1)) ' get the value of the digit at position y
If d = nm Then Cells(r, 6) = a ' if digit matches target number place combination in col 6. adjust as desired
Next y
Next r

End Sub

Hello phileight. I can use this macro and it is much appreciated. I am still using the macro

you made for me years ago. Thank you for both macros. Have a great day.

ram.
nibor200

United States
Member #203,631
January 11, 2020
19 Posts
Offline

Jan 17, 2020, 12:11 pm

@TasBob,

To clarify your question,

Do you want the word TRUE to be moved into the occupied cell immediately to its left while keeping the 815 in that cell such as "815 TRUE"?

or do want TRUE moved into the blank cell to the right and include a copy of 815 in such as "TRUE 815"?

Thanks,

Ed S.
TasBob

Bowling Green ,Florida
United States
Member #108,728
March 30, 2011
6,547 Posts
Offline

Jan 17, 2020, 5:27 pm

Quote: Originally posted by nibor200 on Jan 17, 2020
@TasBob,

To clarify your question,

Do you want the word TRUE to be moved into the occupied cell immediately to its left while keeping the 815 in that cell such as "815 TRUE"?

or do want TRUE moved into the blank cell to the right and include a copy of 815 in such as "TRUE 815"?

Thanks,

Ed S.

Tks for your reply

Yes I would like "TRUE 815

TRUE moved into the blank cell to the right and include a copy of 815 in such as "TRUE 815

TAS
phileight

United States
Member #41,845
June 23, 2006
717 Posts
Offline

Jan 18, 2020, 3:56 am

Quote: Originally posted by ram on Jan 17, 2020
Hello phileight. I can use this macro and it is much appreciated. I am still using the macro

you made for me years ago. Thank you for both macros. Have a great day.

ram.

thank you Ram. glad you can use it. my good fortune smile upon you
nibor200

United States
Member #203,631
January 11, 2020
19 Posts
Offline

Jan 18, 2020, 3:34 pm

@TasBob,

this approach is not very elegant but I believe it gets you what you wanted:

All of the formulas below are expecting your data to be in column A and the digit you are looking for to be in cell K1

In cell J1 paste the following formula

=TEXT(A1,"000")

then copy it and paste it as far down as your column A data goes

In cell B1 paste the following formula

=IFERROR(TRUE&" "&VLOOKUP("*"&$K$1&"*",$J1:$J1,1,FALSE),"")

then copy it and paste it as far down as your column A data goes

and you should see TRUE 765 and TRUE 815 if you enter the number 5 in cell K1, based on the earlier numbers you provided

Any questions or problems let me know,

Thanks,

Ed S.
nibor200

United States
Member #203,631
January 11, 2020
19 Posts
Offline

Jan 18, 2020, 6:45 pm

@TasBob,

Here's an easier fix

the single formula below is expecting your data to be in column A and the digit you are looking for, to be in cell K1

In cell b1 paste the following formula

=IF(LEFT(TEXT(A1,"000"),1)=TEXT($K$1,"0"),"TRUE "&TEXT(A1,"000"),IF(MID(TEXT(A1,"000"),2,1)=TEXT($K$1,"0"),"TRUE "&TEXT(A1,"000"),IF(RIGHT(TEXT(A1,"000"),1)=TEXT($K$1,"0"),"TRUE "&TEXT(A1,"000"),"")))

then copy it and paste it as far down as your column A data goes

Hope this is what you wanted,

Ed S.
TasBob

Bowling Green ,Florida
United States
Member #108,728
March 30, 2011
6,547 Posts
Offline

Jan 19, 2020, 3:17 pm

Quote: Originally posted by nibor200 on Jan 18, 2020
@TasBob,

Here's an easier fix

the single formula below is expecting your data to be in column A and the digit you are looking for, to be in cell K1

In cell b1 paste the following formula

=IF(LEFT(TEXT(A1,"000"),1)=TEXT($K$1,"0"),"TRUE "&TEXT(A1,"000"),IF(MID(TEXT(A1,"000"),2,1)=TEXT($K$1,"0"),"TRUE "&TEXT(A1,"000"),IF(RIGHT(TEXT(A1,"000"),1)=TEXT($K$1,"0"),"TRUE "&TEXT(A1,"000"),"")))

then copy it and paste it as far down as your column A data goes

Hope this is what you wanted,

Ed S.

Thank You so much.. The above works great just what I needed.

Tks Again
TasBob

Bowling Green ,Florida
United States
Member #108,728
March 30, 2011
6,547 Posts
Offline

Jan 23, 2020, 4:53 pm

Quote: Originally posted by nibor200 on Jan 18, 2020
@TasBob,

Here's an easier fix

the single formula below is expecting your data to be in column A and the digit you are looking for, to be in cell K1

In cell b1 paste the following formula

=IF(LEFT(TEXT(A1,"000"),1)=TEXT($K$1,"0"),"TRUE "&TEXT(A1,"000"),IF(MID(TEXT(A1,"000"),2,1)=TEXT($K$1,"0"),"TRUE "&TEXT(A1,"000"),IF(RIGHT(TEXT(A1,"000"),1)=TEXT($K$1,"0"),"TRUE "&TEXT(A1,"000"),"")))

then copy it and paste it as far down as your column A data goes

Hope this is what you wanted,

Ed S.

Hi nibor 200

Can you use formula above and find 2 digits and also all 3 in box position ?

Tks TAS
nibor200

United States
Member #203,631
January 11, 2020
19 Posts
Offline

Jan 24, 2020, 9:10 am

@TasBob,

1. Another user had contacted me for a somewhat similar request for finding 2 digits and this is how I modified that formula for him:

"put the 2 digit number in cell K1 and the following formula in cell B1 and copy and paste as far down in the B column as your data in the A column goes.

=IF(LEFT(TEXT(A1,"000"),2)=TEXT($K$1,"00"),"TRUE "&TEXT(A1,"000"),IF(LEFT(TEXT(A1,"000"),1)&RIGHT(TEXT(A1,"000"),1)=TEXT($K$1,"00"),"TRUE "&TEXT(A1,"000"),IF(RIGHT(TEXT(A1,"000"),2)=TEXT($K$1,"00"),"TRUE "&TEXT(A1,"000"),"")))

This will match 2 digit value in cell K1 with the leftmost 2 digits, or the rightmost 2 digits, or the leftmost single digit and the right most single digit of the 3 digit number in any of the A cells that have matches. I wasn't sure if you wanted a combined first and third digit match or not.

if you have 33 in K1 it will match A cell values of 330, 033, 303, etc. (and 333 which seems to be a triple match)"

2. Please clarify what you mean by "all 3 in box position" with an actual example.

Thanks,

Ed S.
hrstrader

bristol,fl
United States
Member #203,965
January 24, 2020
1 Post
Offline

Jan 24, 2020, 9:40 am

Quote: Originally posted by TasBob on Jan 15, 2020
Lets say I want to look at last months numbers,and find the numbers with 5 that hit.

It would like this.

714

079

765 765

983

869

815 TRUE

IF It comes out with (815) TRUE could True be moved to the next cell over with 815 in it?

Thank You Tas

morning I was looking at your post and it make sense if you add up any 3 digit drawing and reduce it to a single digit going to be between 1-9 so if we were to pick one number and see how many combinations there are which add up to the single digit based on prediction past games my guess would this work and do you know of a program that would be usefull to all,thanks
nibor200

United States
Member #203,631
January 11, 2020
19 Posts
Offline

Jan 24, 2020, 9:57 am

@hrstrader,

I believe Lottery Post has that capability already built in.

Up in the left corner you'll see a large gold star with Premium Memberships to its right.

Click on Premium Memberships and you'll get a list of Lottery Post program capabilities for the different tiers of membership and free links to try any of them out.

Ed S.
TasBob

Bowling Green ,Florida
United States
Member #108,728
March 30, 2011
6,547 Posts
Offline

Jan 24, 2020, 2:20 pm

Quote: Originally posted by nibor200 on Jan 24, 2020
@TasBob,

1. Another user had contacted me for a somewhat similar request for finding 2 digits and this is how I modified that formula for him:

"put the 2 digit number in cell K1 and the following formula in cell B1 and copy and paste as far down in the B column as your data in the A column goes.

=IF(LEFT(TEXT(A1,"000"),2)=TEXT($K$1,"00"),"TRUE "&TEXT(A1,"000"),IF(LEFT(TEXT(A1,"000"),1)&RIGHT(TEXT(A1,"000"),1)=TEXT($K$1,"00"),"TRUE "&TEXT(A1,"000"),IF(RIGHT(TEXT(A1,"000"),2)=TEXT($K$1,"00"),"TRUE "&TEXT(A1,"000"),"")))

This will match 2 digit value in cell K1 with the leftmost 2 digits, or the rightmost 2 digits, or the leftmost single digit and the right most single digit of the 3 digit number in any of the A cells that have matches. I wasn't sure if you wanted a combined first and third digit match or not.

if you have 33 in K1 it will match A cell values of 330, 033, 303, etc. (and 333 which seems to be a triple match)"

2. Please clarify what you mean by "all 3 in box position" with an actual example.

Thanks,

Ed S.

With the above I'm using k l and m It's everything I need

Thank You So Much

TAS

New Topic New Poll

Subscribe to this topic

Excel QuestionPrev TopicNext Topic

Excel QuestionPrev Topic Next Topic