Welcome Guest
Log In | Register )
You last visited December 6, 2016, 9:00 am
All times shown are
Eastern Time (GMT-5:00)

Need some help with excel counting vertical

Topic closed. 32 replies. Last post 3 years ago by SergeM.

Page 2 of 3
PrintE-mailLink
Avatar
Krypton
United States
Member #140102
March 11, 2013
891 Posts
Offline
Posted: April 6, 2014, 10:26 am - IP Logged

This formula =SUMPRODUCT(--($B$2:$B$12=F$2),(--($B$3:$B$13=$E3))) goes in the second row,secon column. This is position 1 only, you have to create the other 2 positions.

I am no guru.

 

02/13/10594P10123456789
02/12/1053702000010001
02/11/1054510000000000
02/10/1009821020000000
02/09/1003630000000000
02/08/1006540000000000
02/07/1020950000020000
02/06/1026761000000000
02/05/1024470000000000
02/04/1099480000000000
02/03/1000390010000000
02/02/10636                     

More of a guru than I. Lol. I get ALOT of help

    Avatar
    Krypton
    United States
    Member #140102
    March 11, 2013
    891 Posts
    Offline
    Posted: April 6, 2014, 10:49 am - IP Logged

    Texas gives out free food all the time Serge but I do love to learn and work for my meals

      Avatar
      Krypton
      United States
      Member #140102
      March 11, 2013
      891 Posts
      Offline
      Posted: April 6, 2014, 10:53 am - IP Logged

      okay folks, I am not an excel huru by far and build my spreadsheets by trial an error and a lot of reading and help.  With either one of these I do not see how it gives me a total of the numbers off to the side.  What am i missing and I do need the 101 verison

        Avatar
        Krypton
        United States
        Member #140102
        March 11, 2013
        891 Posts
        Offline
        Posted: April 6, 2014, 5:17 pm - IP Logged

        Let me try to explain this a differnt way.  First thing forget the lottery.  This actually has nothing to do with frequency, skips, even , odd , up down nothing at all.  I am sinplmy trying to count how many numbers are in a verticle line.  If there are four 8's I want a 4 placed in the column.  If there are three 5's I want a 3 placed next to the 5's.  That's it ...

          SergeM's avatar - slow icon.png
          Economy class
          Belgium
          Member #123700
          February 27, 2012
          4035 Posts
          Offline
          Posted: April 6, 2014, 6:04 pm - IP Logged

          Try typing! I Agree!

            Avatar
            Krypton
            United States
            Member #140102
            March 11, 2013
            891 Posts
            Offline
            Posted: April 6, 2014, 7:58 pm - IP Logged

            It really is pretty simple what I want to do.  I have a feeing everyone is trying to read into this more LOL.  All I want to do is count down the rows at the last digit.  If you look below, the last digt (2229 last digit is a "9") (as39 last digit is a "9") and so on.  okay, if you count down at the last digit you will see 4 "9"s.  I want to put a "4" in the next column next to the top 9.   Then you have "3" "4"s counting down.  I want to put a "3" in the next column next to the top 4 (1234)

             

            222 there are four "9"s so put a "4" in the next column over from the 9s                    4

            1239

            1549

            4589

            123 there are three"4"s so put a"3" in the next column over                                   3

            123

            985 4

              Avatar

              United States
              Member #41846
              June 23, 2006
              459 Posts
              Offline
              Posted: April 7, 2014, 8:57 pm - IP Logged

              It really is pretty simple what I want to do.  I have a feeing everyone is trying to read into this more LOL.  All I want to do is count down the rows at the last digit.  If you look below, the last digt (2229 last digit is a "9") (as39 last digit is a "9") and so on.  okay, if you count down at the last digit you will see 4 "9"s.  I want to put a "4" in the next column next to the top 9.   Then you have "3" "4"s counting down.  I want to put a "3" in the next column next to the top 4 (1234)

               

              222 there are four "9"s so put a "4" in the next column over from the 9s                    4

              1239

              1549

              4589

              123 there are three"4"s so put a"3" in the next column over                                   3

              123

              985 4

              skyline69  you may have guessed by now, this is not a simple formula for Excel. too many things to evaluate.  you will need a macro (programming) to do this. most people are not interested in macro's as they are very specific, you can't just copy and paste them wherever you want.  They do tremendously expand the power of Excel once you learn to use them.

              if interested tell me how many lines or rows to check?  what col d,e,f, etc is the numbers being checked located in?  I would assume D but this could easily be wrong.  is there a minimum # of occurences?   if it shows one time do you want a 1?

                SergeM's avatar - slow icon.png
                Economy class
                Belgium
                Member #123700
                February 27, 2012
                4035 Posts
                Offline
                Posted: April 8, 2014, 4:42 am - IP Logged

                skyline69  you may have guessed by now, this is not a simple formula for Excel. too many things to evaluate.  you will need a macro (programming) to do this. most people are not interested in macro's as they are very specific, you can't just copy and paste them wherever you want.  They do tremendously expand the power of Excel once you learn to use them.

                if interested tell me how many lines or rows to check?  what col d,e,f, etc is the numbers being checked located in?  I would assume D but this could easily be wrong.  is there a minimum # of occurences?   if it shows one time do you want a 1?

                It took me five minutes.

                  Avatar
                  Krypton
                  United States
                  Member #140102
                  March 11, 2013
                  891 Posts
                  Offline
                  Posted: April 8, 2014, 10:37 pm - IP Logged

                  skyline69  you may have guessed by now, this is not a simple formula for Excel. too many things to evaluate.  you will need a macro (programming) to do this. most people are not interested in macro's as they are very specific, you can't just copy and paste them wherever you want.  They do tremendously expand the power of Excel once you learn to use them.

                  if interested tell me how many lines or rows to check?  what col d,e,f, etc is the numbers being checked located in?  I would assume D but this could easily be wrong.  is there a minimum # of occurences?   if it shows one time do you want a 1?

                  Thanks Phileight

                   

                  i will search between 1500 and 2500 rows each time. Each occur acne will have 6 Columns. 

                  A - Game #

                  B - 1st digit 

                  C - 2nd digit

                  D - 3rd digit

                  E - 4th Digit

                  F -the count

                   

                  So far I've got this but it's slower than I wish it to be and THANK YOU . It's nice to see someone wanting to help for a change 



                  Sub oCount()
                      Dim Rng As Range
                      Dim Dn As Range
                      Dim Ac As Integer
                      Application.ScreenUpdating = False
                      Columns(7).Clear
                      With CreateObject("scripting.dictionary")
                          .CompareMode = vbTextCompare
                          For Ac = 2 To 5
                              Set Rng = Range(Cells(3, Ac), Cells(Rows.Count, Ac).End(xlUp))
                              For Each Dn In Rng
                                  If Not .Exists(Dn.Value) Then
                                      .Add Dn.Value, Dn
                                  Else
                                      Set .Item(Dn.Value) = Union(.Item(Dn.Value), Dn)
                                  End If
                              Next
                          Next Ac
                          Dim k As Variant
                          Dim A As Range
                          For Each k In .keys
                              For Each A In .Item(k).Areas
                                  If A.Count > 1 Then
                                      Range("g" & A(1).Row) = A.Count
                                  End If
                              Next A
                          Next k
                      End With
                      With Range(Cells(3, 7), Cells(Rows.Count, 7).End(xlUp))
                          .Font.Bold = True
                          .HorizontalAlignment = xlCenter
                      End With
                      Application.ScreenUpdating = True
                  End Sub
                    SergeM's avatar - slow icon.png
                    Economy class
                    Belgium
                    Member #123700
                    February 27, 2012
                    4035 Posts
                    Offline
                    Posted: April 9, 2014, 4:43 am - IP Logged

                    Test it!

                      Avatar

                      United States
                      Member #35335
                      March 16, 2006
                      116 Posts
                      Offline
                      Posted: April 9, 2014, 6:36 pm - IP Logged

                       

                      No macro required.

                        Avatar
                        Krypton
                        United States
                        Member #140102
                        March 11, 2013
                        891 Posts
                        Offline
                        Posted: April 9, 2014, 8:29 pm - IP Logged

                         

                        No macro required.

                        Hey GM, the counters are in 4 seaparate columns.  How can I adapt that to see if it will work?

                         

                        ABCD

                        2229

                          Avatar

                          United States
                          Member #35335
                          March 16, 2006
                          116 Posts
                          Offline
                          Posted: April 9, 2014, 8:47 pm - IP Logged

                          The formula =right() requires two fields, the source field, and the number of characters from the right.

                           

                          Cell A1 contains "GoogilyMoogily"

                          Formula example =right(A1,3)

                          Results = i      because i is third from the right

                           

                           

                          There are also formulas =left() and =mid()

                           

                           

                          You just need one column for finding the character in a particular position, and another column for the =countif()

                            Avatar

                            United States
                            Member #41846
                            June 23, 2006
                            459 Posts
                            Offline
                            Posted: April 10, 2014, 4:57 am - IP Logged

                            Skyline69

                            This code is easier for me to understand and is quick.  you can remove all the comments

                             

                            Sub oCount2()
                                 
                                 Range("f1:f6000").Select
                                Selection.ClearContents ' remove any existing info col f
                                Range("g3").Select ' move cursor to this location
                                 
                                 For x = 1 To 5000 ' adjust the 1 to match where your data starts
                                    A = Cells(x, 5) ' only interested in column 5 aka col 'E'  or digit 4 change to 4 for 3rd digit etc
                                    If A = "" Then x = 5000 ' out of data so quit  if you have more than 5000 line increase this #
                                    For y = 1 To 9 ' check max of 9 next games for same value
                                    b = Cells(x + y, 5)
                                    If b <> A Then cnt = y: GoSub show 'when values don't match end counting show result
                                    Next y
                            Next x

                                  Exit Sub
                                 
                            show:
                                  Cells(x, 6) = cnt 'show result in col 6
                                  cnt = 0  'reset counter
                                  x = x + y - 1 'skip to the next value
                                  y = 9 'terminate the y loop
                                  Return
                               
                            End Sub

                              DrMiracle's avatar - images q=tbn:ANd9GcSZfr3g-6xRNhDA2-tgWdb5fVaacJgb_ybXvQWgy-qL1s6p
                              MARYLAND
                              United States
                              Member #119884
                              December 5, 2011
                              1636 Posts
                              Offline
                              Posted: April 10, 2014, 5:30 pm - IP Logged

                              sorry to say, BUT it seems everyone is helping skyline69 to do his semester project. 

                              just joking. Approve