Need some help with excel counting vertical

Reply #32

SergeM — Thu, 10 Apr 2014 23:13:28 GMT

- UP DOWN 0 1 2 3 4 5 6 7 8 9 0 1 2 3 4 5 6 7 8 9

2090 1 9 9 1 0 2 0 0 0 0 0 0 0 2 - 2 - - - - - - - 7

2089 9 9 8 1 0 1 0 0 0 0 0 0 1 2 - - - - - - - - 7 -

2088 9 8 8 9 0 0 0 0 0 0 0 0 2 2 - - - - - - - - - -

2087 8 8 7 9 0 0 0 0 0 0 0 1 2 1 - - - - - - - 2 - -

2086 7 8 6 9 0 0 0 0 0 0 1 1 1 1 - - - - - - 2 - - -

2085 6 8 5 9 0 0 0 0 0 1 1 0 1 1 - - - - - 11 - - - -

2084 5 8 4 9 0 0 0 0 1 1 0 0 1 1 - - - - 11 - - - - -

2083 5 7 4 8 0 0 0 0 1 1 0 1 1 0

Reply #31

SergeM — Thu, 10 Apr 2014 22:56:41 GMT

- - - - 0 1 2 3 4 5 6 7 8 9 0 1 2 3 4 5 6 7 8 9 0 1 2 3 4 5 6 7 8 9

2/13/2010 5 5 4 - - - - - 3 - - - - - - - - - 1 - - - - - - - - 1 - - - - -

2/12/2010 5 3 3 - - - - - - - - - - - - - 1 - - - - - - - - - 1 - - - - - -

2/11/2010 5 5 5 - - - - - - - - - - - - - - - 1 - - - - - - - - - 1 - - - -

2/10/2010 1 2 1 - 1 - - - - - - - - - - 1 - - - - - - - - 1 - - - - - - - -

2/09/2010 0 3 6 2 - - - - - - - - - - - - 1 - - - - - - - - - - - - 1 - - -

2/08/2010 0 6 5

Reply #30

SergeM — Thu, 10 Apr 2014 22:00:43 GMT

Try this?

2/13/2010 5 5 4 2 1

2/12/2010 5 3 3 1 2

2/11/2010 5 5 5 3

2/10/2010 1 2 1 1 1 1

2/09/2010 0 3 6 1 1 1

2/08/2010 0 6 5 1 1 1

2/07/2010 2 0 9 1 1 1

2/06/2010 2 6 7 1 1 1

2/05/2010 2 4 4 1 2

2/04/2010 9 9 4 2 1

2/03/2010 0 0 3 2 1

2/02/2010 6 3 6 1 1 1

2/01/2010 1 6 6 1 2

2/12/2009 1 1 1 3

Reply #29

DrMiracle — Thu, 10 Apr 2014 21:30:16 GMT

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

just joking.

Reply #28

phileight — Thu, 10 Apr 2014 08:57:06 GMT

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 mor... [ More ]

Reply #27

GoogilyMoogily — Thu, 10 Apr 2014 00:47:38 GMT

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

Reply #26

SkyLine69 — Thu, 10 Apr 2014 00:29:12 GMT

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

ABCD

2229

Reply #25

GoogilyMoogily — Wed, 09 Apr 2014 22:36:46 GMT

No macro required.

Reply #24

SergeM — Wed, 09 Apr 2014 08:43:25 GMT

Test it

Reply #23

SkyLine69 — Wed, 09 Apr 2014 02:37:41 GMT

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... [ More ]

Reply #22

SergeM — Tue, 08 Apr 2014 08:42:18 GMT

It took me five minutes.

Reply #21

phileight — Tue, 08 Apr 2014 00:57:23 GMT

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 c... [ More ]

Reply #20

SkyLine69 — Sun, 06 Apr 2014 23:58:28 GMT

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)

2229 there ar... [ More ]

Reply #19

SergeM — Sun, 06 Apr 2014 22:04:03 GMT

Try typing

Reply #18

SkyLine69 — Sun, 06 Apr 2014 21:17:17 GMT

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 ...

Reply #17

SkyLine69 — Sun, 06 Apr 2014 14:53:59 GMT

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

Reply #16

SkyLine69 — Sun, 06 Apr 2014 14:49:13 GMT

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

Reply #15

SkyLine69 — Sun, 06 Apr 2014 14:26:32 GMT

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

Reply #14

SkyLine69 — Sun, 06 Apr 2014 14:23:33 GMT

Thanks Carbob

om on my iPad at the moment but as soon as I get home I can't wait yo try this. It will save me a tremendous amount of time

Reply #13

CARBOB — Sun, 06 Apr 2014 13:21:27 GMT

Dropbox https://app.box.com/s/r3exkav1wxf0d752epk2

Reply #12

SergeM — Sun, 06 Apr 2014 12:30:25 GMT

2/13/2010 5 9 4 5*3 9*1 4*1

2/12/2010 5 3 7 3*1 7*1

2/11/2010 5 4 5 4*1 5*1

2/10/2010 0 9 8 0*3 9*1 8*1

2/09/2010 0 3 6 3*1 6*1

2/08/2010 0 6 5 6*1 5*1

2/07/2010 2 0 9 2*3 0*1 9*1

2/06/2010 2 6 7 6*1 7*1

2/05/2010 2 4 4 4*1 4*2

2/04/2010 9 9 4 9*1 9*1

2/03/2010 0 0 3 0*1 0*1 3*1

2/02/2010 6 3 6 6*1 3*1 6*1

Coded

Reply #11

CARBOB — Sun, 06 Apr 2014 12:17:19 GMT

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/10 5 9 4 P1 0 1 2 3 4 5 6 7 8 9

02/12/10 5 3 7 0 2 0 0 0 0 1 0 0 0 1

02/11/10 5 4 5 1 0 0 0 0 0 0 0 0 0 0

02/10/10 0 9 8 2 1 0 2 0 0 0 0 0 0 0

02/09/10 0 3 6 3 0 0 0 0 0 0 0 0 0 0

02/08/10 0 6 5 4 0 0 0 0 0 0 0 0 0 0

02/07/10 2 0 9 5 0 0 0 0 0 2 0 0 0 0

02/06/10

Reply #10

SergeM — Sun, 06 Apr 2014 11:57:22 GMT

First thing is that you need to step a bit of the I want this just like that! . Next thing is that you have to build little one row matrices that do little things. You get the information you are looking for out of the columns there. You continue building. Finally you get a result and you show or hide the steps to get there by hiding the columns or not. - The one cell solution is only possible by programming on the backside or you are a wonderboy with matrix functions. - What I showed above is j... [ More ]

Reply #9

SkyLine69 — Sun, 06 Apr 2014 11:36:46 GMT

Patron,

If you look at the spreadsheet that I uploaded to dropbox

[url]https://www.dropbox.com/s/iv66e9j3wfszn08/Count%20Vertical.xlsx[/url]

you will see the following:

Game 2089 and 2088 have two 8's or 9's (pick either one as it does not matter) there will always be two sets of digits behind each other. I only want to capture one set of them and it does NOT matter which one

Anyways ..Game 2089 and 2088 I have two 8's. I want to write a 2 say next to Bingo 2 a coup... [ More ]

Reply #8

SergeM — Sat, 05 Apr 2014 10:41:26 GMT

I have that you are looking for lengths of series, not frequencies.

You can do it!

Example of a summary for one number:

SERIES SIZE:COUNT.SERIES

2,1,1,1,1,1,1,1,1,2,1,1,1,1,2,2,1,1,2,1,2,1,1,3,1,1,1,1,1,1,1,1,1 1:26 2:6 3:1

Reply #7

SergeM — Sat, 05 Apr 2014 10:08:29 GMT

You used four rows and four columns. You wanted to order the frequency descending.

2090 1 9 9 1 Bingo 1 F.Rc4Cc4: 7 9 5 8 3 1 1 7

2089 9 9 8 1 Bingo 2 F.Rc4Cc4: 6 8 9 2 7 1 1 6

2088 9 8 8 9 Bingo 3 F.Rc4Cc4: 6 8 5 9 2 6 7 1 5

2087 8 8 7 9 Bingo 4 F.Rc4Cc4: 5 8 4 9 2 5 6 7 1 4

2086 7 8 6 9 Bingo 5 F.Rc4Cc4: 4 8 3 5 9 2 4 6 7

2085 6 8 5 9 Bingo 6 F.Rc4Cc4: 4 5 3 4 8 2 6 7 9

2084 5 8 4 9 Bingo 7 F.Rc4Cc4: 5 5 4 4 2 6 7 8 1 9

2083 5 7 4 8 Bingo 8 F.Rc4Cc4: 5 5

Reply #6

SergeM — Sat, 05 Apr 2014 08:23:47 GMT

You are not looking for the kind of display like 4x2.

Reply #5

basilio — Sat, 05 Apr 2014 03:23:00 GMT

Hope this help,

how many 2s

=COUNTIF(A2:A13,2) x2

how many 1s

=COUNTIF(A2:A13,1) x1

how many 5s

=COUNTIF(A2:A13,5) x5

Reply #4

SkyLine69 — Sat, 05 Apr 2014 02:37:29 GMT

All I am tryig to do is count how many times a specific number appears in a row and get the total count out to the side for example as in my attachment and below

2

2 =4x

2

2

1

1 =2x

5

5

5 =6x

5

5

5

and so on

Reply #3

basilio — Sat, 05 Apr 2014 02:14:44 GMT

sorry i did not look at your file, but i just did

what your looking for is if you have a double or triples ( you don t care what the numbers are ) right ?

if so just replace cell I4 with the following =SUM(IF(FREQUENCY(C4:F4,C4:F4) 0,1)) then you can drag it down

this returns how many unique numbers you have, so 3 means you have 1 double, 2 you have a triple

hope this help

Reply #2

SkyLine69 — Sat, 05 Apr 2014 02:02:03 GMT

for the excel challenged folks like me but trying to learn. how do i set this up? Where would I place the COUNTIF(A1:A10,8)? I am not sure if you looked at my file in dropbox but I will have well over 1000 rows of numbers to count Where would I place the countif and where do I place my actual file (numbers to be counted) Thanks for your help

Reply #1

basilio — Sat, 05 Apr 2014 01:49:02 GMT

excel count does not care if the range is vertical or horizontal

COUNTIF(range,criteria)

range is your range

criteria is 8 in your case

example

COUNTIF(A1:A10,8) or COUNTIF(A1:F1,8

Need some help with excel counting vertical

SkyLine69 — Sat, 05 Apr 2014 00:21:12 GMT

I am looking for a guru in excel to help me count numbers vertically and give me a total out to the side. For example

8

8 = 4 There are 4 8's

8

8

https://www.dropbox.com/s/iv66e9j3wfszn08/Count%20Vertical.xlsx