MACRO help: where to find prewritten macro or formula

[MAXCAT2007]

Need MACRO or formula to:

1. find repeating numbers in long string of data
2. assign attributes to numbers example red or blk; odd or even; column number or dozen number

[RudiS]

Hi,

Welcome to the forum

Have you tried conditional formatting on the Home Ribbon?
This feature does exactly what you describe.

[MAXCAT2007]

Hi,

Welcome to the forum

Have you tried conditional formatting on the Home Ribbon?
This feature does exactly what you describe.

I am very new to excel. Do I need to set a new rule for each number (the number set I am working with is from American roulette; 1-36 and 0 & 00)?

[MAXCAT2007]

I have recorded thousands of roulette spins from an Organic roulette machine using Kingsoft Office on my android phone.
I would like to analyze the numbers according to several parameters: color(red or black), odd/even, the column and dozen the number is in, any repeating sequence; and so forth.

[RudiS]

Hi,

Conditional formatting is a good tool to use and it can assign any format to words or numbers in a selected range that meets any form of criteria or condition.
I think it would be best to upload a desensitized sample workbook of your data and clarify what conditions you want highlighted and I can assist to get things up and running for you....

[MAXCAT2007]

Hi,

Conditional formatting is a good tool to use and it can assign any format to words or numbers in a selected range that meets any form of criteria or condition.
I think it would be best to upload a desensitized sample workbook of your data and clarify what conditions you want highlighted and I can assist to get things up and running for you....

Sure, How do I upload the file?

[RudiS]

Click on the Go Advanced button on the bottom right of the reply area.
Then in the advanced reply window, click the paperclip button and browse and upload your Excel file with the upload window. (See the image for guidance)

[MAXCAT2007]

Thank you SO much! As you can tell I am a newbie.

Here is the file:14x3x30 Sun.xls

The data is in column A.
Column B is only notes and or time stamp.

Is there a way to time stamp entries on the fly?

[RudiS]

Thanks...that is a random bunch of data?

How do you want to analyse this info?
Quoted: I would like to analyze the numbers according to several parameters: color(red or black), odd/even, the column and dozen the number is in, any repeating sequence; and so forth.

Is it counts of odd vs. even, do you want the numbers coloured via formatting and then tallied somehow??
Please give details regarding this.

One can time stamp data quite quickly by selecting the cell to the right and pressing CTRL+; (semi-colon). This puts the current date in the cell you selected.
A macro can also time stamp another cell the moment you leave the active cell. (Though this is overkill in my honest opinion)...but it's an option!

[RudiS]

Hope this isn't too urgent. I'm off to bed now...it's 1:30am in the morning here and I'm getting tired

I'll catch up with this thread in the morning again...
See ya!

[MAXCAT2007]

Yes, the numbers are from an automated(no dealer) but real roulette wheel. I spins/cycles approximately every 50 seconds.
I have recorded thousands of spins. There are patterns/sequences that repeat or have numbers that usually come one after another.

I would like to be able to enter the number 28 in my spreadsheet cell A1 and have the other columns (ie. columns B=black; column C=even;
column D=dozen(1st,2nd,or 3rd) filled in automatically.

Is there a place to assign a number properties? For example above the number 28 is black, even, in the 1st column, it is in the 3rd dozen.

I usually play and or record roulette spin results(the number the ball drops on in the wheel) everyday or at least 5 days a week.

There are no good programs to analyze the data. I have several apps on my android and iphone. I do not like any of them.
Sure they are easy; But they lack the parameters I want to watch.

A couple of men from Chicago beat the roulette machines in that area so well; the casinos either removed them or reduced the time between spins
down to 15 seconds to avoid any calculation.

[RudiS]

Sorry to have to ask this, but would it be possible to set up a sample workbook that illustrates how you want the outcome to look like. Sort of a before and after sample that shows the layout of the Odds/Evens, Dozens, Black and Red vales, etc in the various columns

It will help me to formulate something without veering off your needs.

TX
Attach it in your next reply if possible.

[MAXCAT2007]

Sure. Good Morning and Thanks.

[MAXCAT2007]

SAMPLE SHEET 1.xlsxSAMPLE SHEET 1.xlsx

Sorry to have to ask this, but would it be possible to set up a sample workbook that illustrates how you want the outcome to look like. Sort of a before and after sample that shows the layout of the Odds/Evens, Dozens, Black and Red vales, etc in the various columns

It will help me to formulate something without veering off your needs.

TX
Attach it in your next reply if possible.

[MAXCAT2007]

Sorry, I forgot to label column A
Here is the revised file. SAMPLE SHEET 1_1.xlsx

I would like to also track any number pairs = (repeating number sequence/occurrence).

Also the automatic time stamp for each entry would be nice. I know that you thought it
was overkill. But it is something I will have to do on my S4 android phone app. I record
all data on the phone in real time.

[RudiS]

Hi,

This seems to match your sample file...
Let me know if you can work with this.

[RudiS]

Sorry, I forgot to label column A
Here is the revised file. Attachment 309712

I would like to also track any number pairs = (repeating number sequence/occurrence).

Also the automatic time stamp for each entry would be nice. I know that you thought it
was overkill. But it is something I will have to do on my S4 android phone app. I record
all data on the phone in real time.

How do you want the tracking to show? By colour...simply highlighting the cell? (To clarify, is this repeating numbers in the calculated columns?)
Where do you want the date and when must the date be added to the sheet?

[MAXCAT2007]

Hi,

This seems to match your sample file...
Let me know if you can work with this.

Sorry for the delay, I kept refreshing the page and did not see any reply. Then, just now I noticed we now have 2 pages...
Thanks, I will look at this and the next post. Thanks AGAIN!

[MAXCAT2007]

Hi,

This seems to match your sample file...
Let me know if you can work with this.

Yes, you hit the nail on the head! Looks great! Did you use conditional formatting or VB?

[MAXCAT2007]

How do you want the tracking to show? By colour...simply highlighting the cell? (To clarify, is this repeating numbers in the calculated columns?)
Where do you want the date and when must the date be added to the sheet?

I am not sure how to properly display the repeating numbers; either in one of the calculated columns or in a pivot table would work.
In a set/group of 500-1000 numbers (numbers are from 1-36 plus the greens=0 & 0/0) there are always repeating pairs or even triples. I guess some sort of summary of the repeaters would work.

[MAXCAT2007]

How do you want the tracking to show? By colour...simply highlighting the cell? (To clarify, is this repeating numbers in the calculated columns?)
Where do you want the date and when must the date be added to the sheet?

I have to get the Kingsoft app on my phone to time stamp the entries. There is no other way to time stamp each event/spin.

[RudiS]

Not sure if this is what you mean by repeats...??
See attached.

[MAXCAT2007]

Not sure if this is what you mean by repeats...??
See attached.

Great for repeating numbers. The repeats I am looking for are pairs or triple numbers that repeat in the data string.
Example: say that 16 and 33 and or 31 appear consecutively (one immediately after or before the other in the data string).

[MAXCAT2007]

See B22,23 and B98,99 in the example you graciously provided.
Thx..

[RudiS]

This is a bit more complex than just writing formulas. I'll see if I can set up (or source) some VBA code to find and identify numerical matches on different pairings (or triples) of numbers. Will post back as soon as I can.

[RudiS]

Hi,

This is not what I think you envisioned, but it accurately picks out the pairs and identifies them with the X's on the right. Formulas, or conditional formatting would not work as pairs can overlap each other and cause output to be skewed or rendered inaccurate. For example, if you look at range I10:K13 there are multiple pairing between 23,0,31,and 19. Formulas and conditional formatting will not be able to cope with this!

I trust that it will work for you
Attached...

Please note:
The macro is with the help of an excellent MVP (HansV) in a forum here: http://www.eileenslounge.com/portal.php
Please test it carefully and verify that the results are expected.

[MAXCAT2007]

Good morning, Yes you are right the conditional formatting and formulas will not do this.

This does find pairs. The x's to the right are a bit hard to handle. If this could list the pairs in the right column/or next 2 columns as they occur that would work better than the xx's

Thank you so much!! I do not know any VB code period. When I went to college. I learned FORTRAN on punch cards??
Yes, I was born in the dark ages before schools had personal computers. Gee, had I invested in Bill and friends...

[RudiS]

Hi,

Since there can be many pairing, if the X's get listed in the column to the right (or next 2 columns), it might merge and the result might not be accurate.

See what I mean with this image, attached
>> The 1,2,3 pair runs into the 2,3,9 pair and the 2,3 pair will completely disappear in the triples if the columns had to be only 2 columns in width?


This is a tricky one to manage...and I don't know how to modify it to prevent loosing the combinations.

[RudiS]

Another warning...
The person who wrote the macro has identified a possible flaw.
Please see this thread in Eileens Lounge that documents the macro and the small problem in post #122377

http://www.eileenslounge.com/viewtop...122381#p122363

[MAXCAT2007]

Another warning...
The person who wrote the macro has identified a possible flaw.
Please see this thread in Eileens Lounge that documents the macro and the small problem in post #122377

http://www.eileenslounge.com/viewtop...122381#p122363

Thanks again for all your efforts. I truly appreciate everything! I looked at the info at eileenslounge. Can it be done
with pivot tables like PJ in FL suggested?? (POST#122406)

[RudiS]

Hi,

Here is a new version with pivots...
Much more compact!

[MAXCAT2007]

Hi,

Here is a new version with pivots...
Much more compact!

Thanks again Rudi...

I will look at this after work. I know it is already late at your place.

[RudiS]

No prob...

[MAXCAT2007]

Awesome! That will work fine...

THX!

[RudiS]

Excellent...
Glad it is satisfactory.
Cheers

[MAXCAT2007]

Hi,

Here is a new version with pivots...
Much more compact!

Hi Rudi,

I tried to save this as a macro enabled template. But, I am having issues with the pivot tables. When I cleared the data it also cleared the conditional formatting too.
Is there a way to save this without the data and not lose the conditional formatting or functional pivot tables? I tried everything.

I would like to drop new data into a template and get the results like the sample.

[RudiS]

Hi,

Try this....(attached below)

The format is an *.xltm (template format)
Either place it into the templates folder so it opens a copy each time you use it, or just simply keep a blank copy somewhere, and open/save as to avoid overwriting the blank version.

I have automated the Pivot Updating and added a bit of conditional formatting too

[MAXCAT2007]

Hi Rudi,

Thanks a million! Sorry for the delay getting back. I dozed off in my chair. This looks Wonderful and Works Great. I did a google search on the templates folder and got a lot of results. Where is the default location? Should I change the default location?

[RudiS]

LOL... No problems! Doze off's are great!

See if the instructions in this article help guide you.
http://spreadsheets.about.com/od/exc...l_template.htm

Please note that your template must be *.xltm (since I have put a macro in the file to auto update the pivot tables)

I'd recommend to open the file I uploaded into Excel, and then follow the instructions to save it as they specify
Then follow the instructions to open it further down the page)
Note: When you open the template, it should make a copy of the file, so the original does not get overwritten.