Counting numbers vertically

[Icewynd]

OK Larbec,

I had an epiphany in the middle of the night! Your string of 5 8's is actually 0,8,8,8,8,8,0 which would be counted in the spreadsheet as 0,1,2,3,4,5,0. So I could probably write some sort of IF statement that would only put a digit in coloumn H IF >1 AND the next digit is 0, yes?

Trouble is I am just starting a helluva week with travel to Memphis, Houston, Louisville and Detroit. I probably won't have time to look at this again until next weekend, so feel free to follow up on this option by yourself in my absence.

If anyone reading this has Excel skills, I would appreciate it if you could help Larbec out in my absence.

Good luck!

 

[larbec]

I think that's correct ice. Hmmm I need to get better skills with excel. 30+ years programming robots and automating facilities I believe I can do this taking the time to just learn I will mess around with some IF statements I'm not sure how excel programming works but IF is IF LOL. Have a safe trip. I'm in East Texas (Tyler) always good to meet folks from forums you share interest in Houston is a bit if a haul though lol

 

[patron]

Hi larbec, ice...

Ice you are on fire with excel programming...
Excellent job...
Larbec if you are programming robot, it will be piece of cake for you.
Sorry that i didn't reply to you earlier, but i was away for the weekend...
Take care ice...

Patron

 

[larbec]

Messed around with this for a while and excel is kicking my butt LOL, I will get better. Patron can you lend a hand and do up spreadsheet for this? Robots, ladder logic quite different than excel LOL

 

[Frank]

IN response to Icewynds request for others to chip in, I downloaded your sheet larbec to take a look. I like Icewynds solution, but I thought I'd go back to your original request to see if there was an 'in line' solution which did not need so many construction tables to get the result. I did look on line for inspiration and found what could be a more elegant solution and modified to suit your requirements.

I believe this web page:- http://stackoverflow.com/questions/20625287/count-instances-of-consecutive-duplicate-values

is what you were looking for as descrobed in your first post.

This spreadsheet is my interpretation of how to use it for your numbers :-

https://www.mediafire.com/?7wpmfjyo8sq29og

If you first turn to sheet 3 you will see I have copied the example shown on the above web page just to show whats going on. I have added an extra column which tests for the top of a repeating vertical string, so only one counter is shown, not one counter on each line.

Back to your problem on sheet 1, I have added some columns to make room for the workings, you will see three columns are needed e.g (HIJ) to end up with a column of counters for each of your original vertical lists (cols CDEF). Note that the two extra grey columns per set could be hidden (hide HI KL NO QR) so you would end up with a four column table of counters corresponding to the answers you want for cols (CDEF). Is this format any use to you.

Best regards,
Frank

 

[larbec]

Thanks Frank. I'm on my iPad which as you know isn't any good fir anything besides checking email and playing games lol but will d/l tonight and report back. Thank you!

You guys are far more advanced than I ... What's the different between writing a Macro and writing a formula? Is one better than the other?

 

[larbec]

Ice,

I know we don't have a way to PM so how can I get a private message to you? If not possible let's see if we can meet up next time your in Texas. I have something to show and tell but not on an open forum but need some higher intelligence to help me crack all games. (-: I refuse to be like the idiots that go bragging to the LC "look what I can do". Dummies IMHO.

 

[Frank]

Formulas and macros are different ways of solving problems. There are some questions you can ask Excel which formulas would not be able to solve immediately. Excel formulas perform a one time calculation and give you the one time answer. What if you needed to change a value , check the new answer not just once, not just twice, but 100000 times? That's when you need a macro, or VB program to give it it's correct title. You can program repeated changes of values, store all the results of each cycle of calculations, and then write out all the answers to all the calculations into Excel for your perusal.
You could for example generate all the possible combinations for a Lotto result using VB , it would take at the most a few minutes to generate them all. You could ask questions of the combinations , like what do they add up to, and how many add up to the same number, within the VB loop, and then have it tell you the answer at the end. Doing this using Excel formulas requires specialist statistics knowledge as well as Excel skills and would take a lot longer to do.

So some things are quicker and easier with macros, others are quicker with formulas, depending on what functions are available and how familiar with them the user is.

 

[Icewynd]

This spreadsheet is my interpretation of how to use it for your numbers :-
https://www.mediafire.com/?7wpmfjyo8sq29og

Frank,

Thanks for doing this! Much better solution than I was able to produce.

Larbec,

I have modified Frank's spreadsheet very slightly, so I think that it meets your original request.

https://www.mediafire.com/?s37xyz15w0gx1y4
Note that columns H thru S are hidden, so you only see what you need.

Hope this provides what you needed.

Good luck!

 

[Icewynd]

Ice,

I know we don't have a way to PM so how can I get a private message to you? If not possible let's see if we can meet up next time your in Texas. I have something to show and tell but not on an open forum but need some higher intelligence to help me crack all games. (-: I refuse to be like the idiots that go bragging to the LC "look what I can do". Dummies IMHO.

Hey, Larbec!

You can reach me by email by using my forum name spelled with an "e" added to the end. I am on gmail.

Good luck!

 

[larbec]

This does EXACTLY what I was looking for THANK YOU THANK YOU THANK YOU!!!!

 

[larbec]

Hey this may challenge you a bit ... is it possible to have a button to search for a group of numbers in column U? Lets say I put in the counters in CDEF and it gives me numbers all the way down U like it does. Some rows will be blank while others have numbers. What I would like to do is do a search for example: I want to find a pattern group 4-3-3-4
row 10 4
row 11
row 12
row 13 3
row 14
row 15 3
row 16
row 17 4
row 18
row 19
row 20 2
'
'
'
'
a vertical group of numbers

https://www.dropbox.com/s/eyfo10h1iro2x5a/Count Vertical SEARCH.xlsx

 

[Icewynd]

This does EXACTLY what I was looking for THANK YOU THANK YOU THANK YOU!!!!

Frank is the one who solved the problem. I just added a bit of formatting.

Glad you have your solution.

Good luck!

 

[Icewynd]

Hey this may challenge you a bit ... is it possible to have a button to search for a group of numbers in column U? Lets say I put in the counters in CDEF and it gives me numbers all the way down U like it does. Some rows will be blank while others have numbers. What I would like to do is do a search for example: I want to find a pattern group 4-3-3-4
row 10 4
row 11
row 12
row 13 3
row 14
row 15 3
row 16
row 17 4
row 18
row 19
row 20 2
'
'
'
'
a vertical group of numbers

https://www.dropbox.com/s/eyfo10h1iro2x5a/Count Vertical SEARCH.xlsx

Can you be a bit more specific? In your example 4-3-3-4 spans 8 rows: 4-sp-sp-3-sp-3-sp-4. Will this always be the case? Or could the pattern span any number of rows, with more or less spaces?

Good luck!

 

[larbec]

Scratch the last request, I figured it out (-:

 

[larbec]

Not to drive yall crazy(ier) lol, I wrote a macro to do the search like I want but it is slow as you know what so if a formula can be done I would appreciate it TREMENDOUSLY!! AGAIN THANK YOU!!!!