Counting numbers vertically

[larbec]

I would like to count these automatically. I have uploaded an example with the numbers how I would like them displayed. The actual position of the number is not important as long as I get the count of how many 1's are in a vertical line or 8's or 5's etc...

I am trying to add how many "liked" numbers are in each row vertically and give me the total off to the side. I have attached a sample but basically this is what I am looking for

8
8 =4
8
8
1
1 =2
2 =1
3
3 =3
3
4 =2
4


https://www.dropbox.com/s/iv66e9j3wf...0Vertical.xlsx

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

 

[patron]

Hey larbec

I want to help you man, but i cannot understand what you want to do...
Do you want to count for each line or the entire spreedsheet?

Patron

 

[larbec]

Patron,

If you look at the spreadsheet that I uploaded to dropbox

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

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 couple columns over to the right
Then you see five 9's. I want to place a 5 next to Bingo 3
Then I have four 4's, I want to place a 4 next to Bingo 7
Next two 6's place a 2 next to Bingo 10
Next two 3's …place 2 next to Bingo 11
Next five 5's….place a 5 next to Bingo12
Next three 8's …place a 3 next to bingo 16

And so on and so on. I do not need to capture the 1's like the 7 in games 2087, 2086 ( 7or 6) and 2083 (7 or 8) so do not worry about the single digits that are by themselves

The ones with C1-C2-C3-C4 are done the same way except there are no single digits. I have numbers next to all the Bingo's and if you count how many numbers there are vertically you will see they add up to the digit off to the right. Like on the bottom games 2089 has a 2 for two 7's and game 2084 has a 4 for four 1's, Game 2081 has a 8 a long side Bingo 10 for eight 3's

I hope this makes better sense.

Thank you VERY much for your help I live in Texas USA and it is 2:30 a.m. here so I need to get a couple hours sleep but will check this out again first thing when I wake up. Again Thank you!!!!

 

[Icewynd]

Hey Larbec,

I had a look at your file but I am a little foggy on the concept.

Which direction are you counting in? Do you start at the bottom (Bingo 32) and count up? Or do you start at Bingo 1 and count down?

Are you counting repeats in each column no matter what the number is? Or are you counting only certain numbers or groups of numbers (eg. count only 8's? Count 1-2, 3-4, 5-6, etc?)

I assume that you are only counting adjacent repeats within the column. That is, if the streak is interrupted and then continues (e.g. 8,8,8,8,5,8,8,8,8)you would count that as 2 streaks of 4 x 8?

Are only number 1-9 possible?

Good luck!

 

[Icewynd]

Try this:

https://www.mediafire.com/?4yoy2c2yqc3d29f

This is kinda 'rough and ready' but it gets the job done. I'll think about it some more to see if I can't come up with something slicker.

BTW, this formula counts from bottom to top. Let me know if that's not right.

Also, note that you can hide columns I thru AU to just view the summary table.

Hope it helps.

Good luck!

 

[larbec]

I am counting from the top down even though the number sequence for the games start high to low i.e....(Game 3000 to 1)

I am counting repeats of the last column from the right ONLY which are in C4. There are four columns of numbers but I am only trying to count the last digit(s) in C4 and yes only numbers 1-9. Let me look at what you just put up Ice

 

[Icewynd]

OK,

To change the tables to count from the top down, type in the hits in the top row (hit=1, no hit=0) E.g. column C=4, column I=0, J=0, K=0, L=1, M-Q=0.

Then change the formula in cell I5 to =IF(COUNTIF($C5,I$2),I4+1,0)
and copy across, then do the same for column D,E,F.

Good luck!

 

[larbec]

OMG you are amazing!!!!It really does not matter if it counts from top to bottom or bottom to top I am after the total count PERIOD. Not yelling lol

Now, is it possible to clean it up a little? Don't get me wrong, the spread sheet is gorgeous. I only want to see the total number counted not the tails or lighter cells. It makes it harder for me to see the number(s) I am looking for

If you look at column M I only need to see the 4 for game numbers 2080-2083, Then I would need to see a colored cell for the two 5's or 6's in games 2079-2080, then a 2 for Game 2078-2079, then a 5 for game 2074-2078, then a 3 for Game 2072 - 2074 and so on. Does this make sense. Also the brown cells with black font is hard to see LOL.

THANK YOU SO MUCH!!!!!!!!!!!!! You have no idea how much time this will save me plus the accuracy of me not being able to count 1+1 = 2 at times LOL

http://s867.photobucket.com/user/skyline6969/media/Icespreadsheet_zpsa8779f0a.png.html

 

[larbec]

I love the spreadsheet and wondering if you can add something to it? In columns G or H can I get the total for that specific number sequence? You give the total in the summary and would also love it in 1 of the 2 columns (G or H) just a single digit 1-2-3-4-5-6-7-8-9

My confusion comes from all the other numbers that are around the total count. Does this make sense?

 

[Icewynd]

I think I see what you are saying, that's why I used the colour coding.

Try this:

In cell H4 type the following formula and copy it down:

=MAX(AW4:BE4)

Then highlight the cells in column H and click Home>Conditional Formatting>New Rule>Format Only Cells That Contain

In the dialog box select Cell Values, Less than or Equal To, and type in the lowest value that you want to see (4? 5?). Then click the Format button below, select Font, and set the font colour to white.

Click OK and the values under the number you selected will no longer be visible (although you will be able to see them if you highlight the cell).

I don't know of any way to just leave the highest number for each 'streak', but at least this will reduce the clutter. Then you can hide the information in Columns I:BE.

Good luck!

 

[larbec]

For some reason when I put this In cell H4 =MAX(AW4:BE4)
and drag it down , it also gives me other numbers that are not a total of anything. It does count the cells like I want but also adds other numbers

2090 1 9 9 1 Bingo 1 2
2089 9 9 8 1 Bingo 2 2
2088 9 8 8 9 Bingo 3 5
2087 8 8 7 9 Bingo 4 4
2086 7 8 6 9 Bingo 5 3 ????
2085 6 8 5 9 Bingo 6 2 ????
2084 5 8 4 9 Bingo 7 4

If this can be corrected it would work wonderfully

 

[Icewynd]

For some reason when I put this In cell H4 =MAX(AW4:BE4)
and drag it down , it also gives me other numbers that are not a total of anything. It does count the cells like I want but also adds other numbers

2090 1 9 9 1 Bingo 1 2
2089 9 9 8 1 Bingo 2 2
2088 9 8 8 9 Bingo 3 5
2087 8 8 7 9 Bingo 4 4
2086 7 8 6 9 Bingo 5 3 ????
2085 6 8 5 9 Bingo 6 2 ????
2084 5 8 4 9 Bingo 7 4

If this can be corrected it would work wonderfully

You need to apply Conditional Formatting to deal with this -- as outlined below:

Then highlight the cells in column H and click Home>Conditional Formatting>New Rule>Format Only Cells That Contain

In the dialog box select Cell Values, Less than or Equal To, and type in the lowest value that you want to see (4? 5?). Then click the Format button below, select Font, and set the font colour to white.

Click OK and the values under the number you selected will no longer be visible (although you will be able to see them if you highlight the cell).

I don't know of any way to just leave the highest number for each 'streak', but at least this will reduce the clutter. Then you can hide the information in Columns I:BE.

Let me know if this works.

Good luck!

 

[larbec]

Hey Ice, thanks for your patience. I did the CF and placed 2 as the smallest number I wanted to see but it is leaving me with a 2 in areas and once in a while a 3 I only have a single digit. It did take away all the 1's. Man, I pray this will work. The time this will save me is unreal

Game
2090 3 9 2 1 Bingo 1 2
2089 2 9 1 1 Bingo 2 2
2088 2 8 1 9 Bingo 3 3
2087 1 8 9 9 Bingo 4 2
2086 9 8 8 9 Bingo 5 3
2085 9 7 8 8 Bingo 6 2????
2084 9 6 8 7 Bingo 7 3
2083 8 6 7 7 Bingo 8 2????
2082 7 6 6 7 Bingo 9 3
2081 7 5 6 6 Bingo 10 2????
2080 7 4 6 5 Bingo 11 3
2079 6 4 5 5 Bingo 12 2????
2078 5 4 4 5 Bingo 13 3
2077 5 3 4 4 Bingo 14 2????
2076 5 2 4 3 Bingo 15 4
2075 4 2 3 3 Bingo 16 3?????
2074 3 2 2 3 Bingo 17 2
2073 2 2 1 3 Bingo 18 2
2072 2 1 1 2 Bingo 19 3
2071 1 1 9 2 Bingo 20 2

 

[larbec]

Here is the file after I loaded more data and the formatting as instructed. At least I hope I did it correctly LOL

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

 

[Icewynd]

Glad its working for you Larbec. It seems like there are always 2, so you could perhaps reduce the clutter in column H by making the 2's invisible.

What is this anyway? Is this a new lotto game, or some take on your Texas Two Step 4/35? Its quite interesting to see the pronounced diagonal pattern in the summary table.

Good luck!

 

[larbec]

I only need a 2 when two digits are present like in game 2089 and 2090 Other than that I don't want anything displayed so if there is a single digit I want a blank cell in H. I ONLY want to place a number in H when doubles, triples, quads and up appear

I'm working on pattern recognition like robots recognize objects. This will be used as a tool to search for liked patterns How can I change the formatting to only count like I want If I have to count the singles as. "1" that's okay but here I have like a 3 by itself and it's recorded in
H as a 3 which is not good for what I need.

So, its working but not 100% because its placing digits I do not want to see. I will have to go line by line and delete them which will be time consuming

 

[larbec]

maybe this will help

I marked the cells that are incorrect. YOU will notice that there is a single digit (1-2-3-4-5-6-7-8-9) and in the H is displayed a 2. I do not want anything in H for a single digit. If I have to have a single digit that will be fine but it will have to be a 1 not a 7 or 9 etc...

http://s867.photobucket.com/user/skyline6969/media/wrongcounts_zpsf8e5438a.png.html

 

[Icewynd]

I marked the cells that are incorrect. YOU will notice that there is a single digit (1-2-3-4-5-6-7-8-9) and in the H is displayed a 2. I do not want anything in H for a single digit. If I have to have a single digit that will be fine but it will have to be a 1 not a 7 or 9 etc...

http://s867.photobucket.com/user/skyline6969/media/wrongcounts_zpsf8e5438a.png.html

Sorry, Larbec, but I don't know what you are talking about. I don't see any 1's, 7's or 9's in column H on your image. I can't tell much from your image, but it seems as if the data is different from the last version of the spreadsheet that I have seen. If you want me to look into it you will have to post your excel spreadsheet so that I can see the formulas, etc.

However, I suspect that you are asking for something that I cannot do. My formulas will produce a 2 even if 2 is an intermediate step in a run up to 4 or 5. So, if you have a streak of 5 8's, for example, with no other duplicates, the formula in column H will give you 1,2,3,4,5.

Good luck!

 

[larbec]

okay , if you look at this attachment. Forget the other attachments LOL

http://s867.photobucket.com/user/skyline6969/media/wrongcounts_zpsf8e5438a.png.html

Rows 34,35,36 all have 9's and there are a total of three 9's so a "3" is placed in H34
Rows 38,37,39 have two "7"s or "8"s so a "2" goes in H38

Rows 39,40 has either two "3"s or two "4"s (doesn't matter which set we count) so a "2" gets placed in H39

Rows 42,43,44,45 has either four "1"s or "2"s so a "4" goes in H42

In row 37 there is a "8" (its a single digit) so the cell in 37H should be blank or a "1" I prefer it to be empty
In row 41 is a "3" (its a single digit) so the cell in 41H should be blank or a "1" I prefer it to be empty


There is nothing else to this. Hopefully this will clear things up

 

[larbec]

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 digit (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 9 there are four "9"s so put a "4" in the next column over from the 9s 4

123 9

154 9

458 9

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

123 4

985 4