Welcome Guest
Log In | Register )
You last visited January 17, 2017, 6:20 pm
All times shown are
Eastern Time (GMT-5:00)

excel formula help

Topic closed. 13 replies. Last post 11 years ago by lottaloot.

Page 1 of 1
PrintE-mailLink
lottaloot's avatar - AvatarZ56
Redford/MI
United States
Member #3396
January 18, 2004
4867 Posts
Offline
Posted: January 7, 2006, 11:32 pm - IP Logged

I am looking for a formula that will highlight all the double digit combos that have listed below. 

 

066336227557508734116
735284879486389622139
981094909371683244250
134015317912093870790

 

Don't know if I would need a vlookup here or if it has to be set up in another format.

Any help is appreciated

L ttaL   T

    hypersoniq's avatar - 8ball
    Pennsylvania
    United States
    Member #1340
    April 6, 2003
    2450 Posts
    Offline
    Posted: January 8, 2006, 12:03 am - IP Logged

    I thought it would be easy to do with conditional formatting, but I am having a tough time with it... tried using right(cell,index) (example  Right(A1,1)=Right(A1,2)) but no luck...

    you may need to put wach digit in a separate cell... I'll keep trying tho...

    gotta sleep now, however

    Playing more than one ticket per game is betting against yourself.

      time*treat's avatar - radar

      United States
      Member #13130
      March 30, 2005
      2171 Posts
      Offline
      Posted: January 8, 2006, 5:02 pm - IP Logged

      Are you looking for a spreadsheet formula or macro?

      In neo-conned Amerika, bank robs you.
      Alcohol, Tobacco, and Firearms should be the name of a convenience store, not a govnoment agency.

        lottaloot's avatar - AvatarZ56
        Redford/MI
        United States
        Member #3396
        January 18, 2004
        4867 Posts
        Offline
        Posted: January 8, 2006, 10:28 pm - IP Logged

        Are you looking for a spreadsheet formula or macro?

        Whatever way is quickest & easiest. Didn't know twhere to start at using a macro.

        L ttaL   T

          Avatar
          Anna, TX
          United States
          Member #26720
          November 21, 2005
          263 Posts
          Offline
          Posted: January 9, 2006, 4:54 am - IP Logged

          you are going to have to seperate them in seperate cells and then use conditional formating

           

           


            lottaloot's avatar - AvatarZ56
            Redford/MI
            United States
            Member #3396
            January 18, 2004
            4867 Posts
            Offline
            Posted: January 9, 2006, 5:05 am - IP Logged

            I do not think you can use a conditional format with that many doubles. 

            I have them listed as above in one sheet & separated in another. 

             

            L ttaL   T

              hypersoniq's avatar - 8ball
              Pennsylvania
              United States
              Member #1340
              April 6, 2003
              2450 Posts
              Offline
              Posted: January 9, 2006, 5:49 am - IP Logged

              let's take the first number only.. 066

              assume it starts in A1, so

              A1=0

              B1=6

              C1=6

              conditional formats (by cell)

              A1
              conditional format 1 "if cell value is equal to B1"
              conditional format 2 "if cell value is equal to C1"

              B1
              conditional format 1 "if cell value is equal to A1"
              conditional format 2 "if cell value is equal to C1"

              C1
              conditional format 1 "if cell value is equal to A1"
              conditional format 2 "if cell value is equal to B1"


              to keep the relative addressing select all 3 cells when painting the format to D1/E1/F1 etc...
              also select the 3 cells when format painting down An/Bn/Cn etc...

              Playing more than one ticket per game is betting against yourself.

                lottaloot's avatar - AvatarZ56
                Redford/MI
                United States
                Member #3396
                January 18, 2004
                4867 Posts
                Offline
                Posted: January 9, 2006, 5:58 am - IP Logged

                Thanks Hyper.  I'll try this out tonight.  Wink

                L ttaL   T

                  lottaloot's avatar - AvatarZ56
                  Redford/MI
                  United States
                  Member #3396
                  January 18, 2004
                  4867 Posts
                  Offline
                  Posted: January 9, 2006, 6:11 am - IP Logged

                  At the moment, it works enough for it to be correct. 

                  Working on 3 hrs of sleep here & have to go in for another 12hr shift. 

                  Had a mother of a shift last night--(not breaking pt confidentiality)  Had the pt reported on yesterday's news at Grace Hospital (supposedly he shot a cop who shot him).  Worked non-stop without breakfast or lunch (and very minimal LP time Big Smile) for literally 11hrs straight on my feet & another 1hr for paperwork.   

                  And I get to do it all over again today Mad  BUT I WILL get my LP TIME today.  (I think)

                  L ttaL   T

                    Avatar
                    Anna, TX
                    United States
                    Member #26720
                    November 21, 2005
                    263 Posts
                    Offline
                    Posted: January 9, 2006, 6:44 am - IP Logged

                    oh, well, I got it to work for me. I highlighted column by itself and then conditioned it by saying if equal to the other 2 columns as 2 seperate conditions.

                    then I took out the $ sign between the number and the letter and choose the background

                    works like a charm!

                      Avatar
                      Anna, TX
                      United States
                      Member #26720
                      November 21, 2005
                      263 Posts
                      Offline
                      Posted: January 9, 2006, 6:45 am - IP Logged

                      oh, what hyper said

                        time*treat's avatar - radar

                        United States
                        Member #13130
                        March 30, 2005
                        2171 Posts
                        Offline
                        Posted: January 9, 2006, 2:44 pm - IP Logged

                        step 1) make a file called P3color.xls

                        step 2) name a tab Main 

                        step 3) make sure Main is the active sheet

                        step 4) C & P, then run this

                        '------------------' 

                              Sub sheet__build()
                                'colors doubles & triples'
                                'from 000 - 999'
                             
                                Dim M As Object
                                Set M = Workbooks("P3Color.xls").Sheets("Main")
                               
                                Dim row As Integer, col As Integer
                                Dim x As Integer, y As Integer
                                Dim num As Integer
                                Const light__green As Integer = 35
                                Const light__blue As Integer = 37
                                Dim good__color As Integer
                               
                                Dim digits() As Integer, tall As Integer, t As Integer
                                Dim c0, c1, c2, c3
                               
                                M.Cells.Clear
                                ReDim digits(9)
                               
                                For x = 0 To 9
                                  For y = 0 To 99
                                    num = 100 * x + y
                                    M.Cells(y + 1, x + 1).Value = num
                                    M.Cells(y + 1, x + 1).NumberFormat = "000"
                                  Next y
                                Next x
                             
                                For row = 10 To 50 ' really 1 to 100'
                                  For col = 2 To 8 ' really 1 to 10'
                                    For t = 0 To 9
                                      digits(t) = 0
                                    Next t
                                    c0 = M.Cells(row, col).Value
                                    c1 = Int(c0 / 100)
                                    c2 = Int((c0 Mod 100) / 10)
                                    c3 = c0 Mod 10
                                    digits(c1) = digits(c1) + 1
                                    digits(c2) = digits(c2) + 1
                                    digits(c3) = digits(c3) + 1
                                    tall = 0
                                    For t = 0 To 9
                                      If digits(t) > tall Then tall = digits(t)
                                    Next t
                                    Select Case tall
                                      Case Is = 1
                                        good__color = 0
                                      Case Is = 2
                                        good__color = light__green
                                      Case Is = 3
                                        good__color = light__blue
                                    End Select
                                    M.Cells(row, col).Interior.ColorIndex = good__color
                                  Next col
                                Next row
                                M.Cells.EntireColumn.AutoFit
                              End Sub
                        '--------------------------'

                         step 5) remove whatever you consider 'overkill' Wink

                        In neo-conned Amerika, bank robs you.
                        Alcohol, Tobacco, and Firearms should be the name of a convenience store, not a govnoment agency.

                          lottaloot's avatar - AvatarZ56
                          Redford/MI
                          United States
                          Member #3396
                          January 18, 2004
                          4867 Posts
                          Offline
                          Posted: January 9, 2006, 7:15 pm - IP Logged

                          Thanks a bunch. :=)

                           

                          Never an "overkill" when dealing with my money & the lottery Wink

                          L ttaL   T

                            lottaloot's avatar - AvatarZ56
                            Redford/MI
                            United States
                            Member #3396
                            January 18, 2004
                            4867 Posts
                            Offline
                            Posted: January 9, 2006, 7:25 pm - IP Logged

                            You're a genius Time-Treat  Wink

                            L ttaL   T