• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Is it possible to find "patterns" in excel?

JAucamp

New Member
Please advice,
I need to identify 4 different patterns in excel. I am not sure if it is possible using VBA, IF functions or something similar. Can somebody please guide me in the right direction?
I have tried to get help on another site, but have not received any answers yet.

See attached example.
The patterns can overlap each other and can be in any order. It would be nice if the different patterns can be highlighted in different colors. Once the 4 patterns have been identified the section closes and start all over again.
This is Baccarat score cards and I need to test several 1000. Each sheet has about 80 hands (inputs)
Currently I am doing it manually and as you can imagine it takes forever.
It also involves showing with win loss result and graph.

Data
upload_2016-8-29_16-20-59.png

4 Patterns identified from A2:H2
upload_2016-8-29_16-22-10.png


The 4 patterns
upload_2016-8-29_16-24-1.png
 

NARAYANK991

Excel Ninja
Hi ,

To start with , members can only work with data ; please upload a workbook which has enough data in it.

Second , can you explain what is meant by a pattern ?

Third , what is the point from where we should start ? In the screenshot you have shown , row 1 probably has patterns in it. So should we go row wise ? If we go row-wise , when we come to the second row , why is the pattern formed by grouping cells C2 , D2 and D1 ? Why not C2 , D2 and D3 ?

The more information you give , the faster you will get a solution.

The ideal would be if you can give a detailed algorithm of how you go about identifying these patterns by hand , so that the code which will be developed can faithfully reproduce this algorithm.

Narayan
 

Hui

Excel Ninja
Staff member
Also what are the rules that determine which pattern takes precedence when two or more outcomes are possible?
 

JAucamp

New Member
Thanks for the reply.

I am trying to upload a file but keeps getting an error message. See attached screen shot from data of 20 shoes. I have 25,000 of these baccarat shoes. If interested I can forward the sheet to your email address.
If you are not familiar with Baccarat, it is a Casino card game where the outcome is either Player (P) or Banker (B) thus all the “P” and “B” values. Each shoe has on average of about 80 hands.
upload_2016-8-30_10-44-58.png

The “Patterns” I am referring to only occurs in rows 1 and 2.

There are 4 patterns:


1 - P,B,P or B,P,B example A1:C1 (1st message)


2 – The next pattern is P,P,B,B or B,B,P,P example C1, C2, D1, D2

P B
P B

3 – The 3rd pattern is P,B,B or B,P,P example E1, F1,F2. This pattern can only occur after a column with at least 2 rows of data example D1, D2

P B
B



4 – The 4th pattern is P,B or B,P example G1,H1. This pattern can only occur after a single column with at least 2 rows. Examle can be after F1, F2, but cannot be after D1, D2 as D1, D2 has data before it of more than 2 rows C1, C2, C3.

P B


When I am doing it manually I convert sheet 1 to the shoe format in sheet 2.

I start at A1 and see if I have any of the 1st three patterns.

Example:

If I have data in A1, A2, B1, B2 then the pattern in the 2nd example is made.

P B
P B

Now I only have 3 more patterns to look for and ignore the pattern I have just made should it occur again. If the next data is C1, D1, D2 the pattern in the 3rd example is made

P B
B

If this pattern occurs again I will ignore it, at this point I am only looking for 2 more patterns P B P and B P


When I have found all 4 patterns, the cycle starts all over again. Normally I would look for patterns up to 45 hands into the shoe (the total of all the “P” and “B”)

Also if 25 hands pass without making at least 3 patterns then the shoe will be rejected and I will move on to the next shoe.


I hope this will give you some idea of what I am looking for. Unfortunately without being able to upload the spread sheets it is very hard to explain.

Is it possible to do in excel or is it better to go the C++ route?

[File attached by Admin]
 

Attachments

Last edited by a moderator:

JAucamp

New Member
The file I am trying to upload is only 70kb.
This is the message I get every time I am trying to attach a file
upload_2016-8-30_13-46-52.png
 

NARAYANK991

Excel Ninja
Hi ,

When you ask whether this can be done in Excel , I doubt that it can be done using Excel formulae.

If it can be done using VBA , then there is no difference between doing it using VBA and doing it using C++ ; in fact , since C++ is a more powerful programming language than VBA , it might be easier to code this application.

Narayan
 

JAucamp

New Member
Can you put it in a Dropbox or equivalent area and post a link ?
Hui, See file attached here:

Narayank991 I have seen the ebay link, but it does not search the patterns I am looking for. I will be checking out the link from sourceforge shortly.

Thank you for the help so far

[Edit by Admin]
 

Attachments

Last edited by a moderator:

JAucamp

New Member
Thanks Hui,
Narayank991 I had a look at the Baccarat BP, it cannot search the patterns I am looking for. Thanks for the help.
 

JAucamp

New Member
A "Shoe" is the device/box/container they are using on the Baccarat table where the cards are dealt from. There are 8 decks of cards in a shoe.
Thus when all the cards are dealt from the shoe we call it the end of the shoe.
In my worksheet example I have 20 columns with "shoes" (All the results from the 8 decks)
As the Baccarat game is dealt the winning hand is recorded as "B" Banker, "P" Player or "T" Tie ("T", Ties are ignored)
There is on average 80 hands or rounds of play per shoe.
 

NARAYANK991

Excel Ninja
Hi ,

Things are becoming clearer , but I am still a long way from complete understanding. I will put down my observations ; please confirm or clarify.

1. You want to look for patterns in only the tab named 20 Shoes ; right ?

2. Each cell in this worksheet is the result of a round of play ; there are 3 results possible in any round of play , T , B or P.

You mention that T ( Ties ) can be ignored ; what does this mean ? Does it mean that if a pattern such as P , B , P is being looked for , any of the following patterns will be a match ?

P , T , B , P

P , B , T , P

Of course , any number of Ts can occur within the pattern , and the resulting pattern will still be a match for P , B , P ; right or wrong ? If this is wrong , please explain in more detail.

3. You mention that there are 80 rounds of play in a shoe ; does this mean that we go down a column or do we go from left to right in a row ? Please explain.

Narayan

P.S. I will have more doubts once you have clarified the above.
 

JAucamp

New Member
Narayan,
Thanks for the help, I know it sounds very confusing in the beginning, but it should become clear soon.
Let me try and answer your questions:
1 - I want to look for the patterns, using the data from the tab named 20 shoes. PS: I have 25,000 shoes in total.
I am using below code to sort the data from tab named 20 shoes into the next tab. The "T" ties are removed during this process and I convert the data to X (P)and O (B). The X and O part is not necessary. P and B also works for me.

Code:
Option Explicit
Dim InRw As Long, OutRw As Long, OutCol As Long
Sub MoveData()
    InRw = 1
    OutRw = 1
    OutCol = 2
    Cells(OutRw, OutCol).Value = Cells(InRw, 1).Value
    InRw = InRw + 1
    Do Until Cells(InRw, 1).Value = ""
        If Cells(OutRw, OutCol).Value = Cells(InRw, 1).Value Then
            OutRw = OutRw + 1
        Else
            OutRw = 1
            OutCol = OutCol + 1
        End If
        Cells(OutRw, OutCol).Value = Cells(InRw, 1).Value
        InRw = InRw + 1
    Loop
    Columns(1).Delete
End Sub
2 - Correct each cell is a round of play. As you can see with above example I delete the "T" ties

3 - The data from each shoe is from the top to the bottom, go down the column. The data gets sorted in a "shoe" from left to right as per the code I am using. Once I have the shoe I start looking for the patterns.

I know it is a headache at this point and I really do appreciate the help.
 

NARAYANK991

Excel Ninja
Hi ,

Thanks for the clarifications.

If you say that the tab 20 Shoes is the raw input tab , and you will always run the posted macro on this data , can we take it that the code which needs to be written can use the tabs Shoe format or Patterns instead ? Which should it be ?

If we take the tab named Shoe format , and if we look at the data labelled Shoe 1 , are we to look for patterns only in the rows 2 and 3 ? What about the data in rows 4 through 10 ? Is this data to be ignored ?

In the case of the data for Shoe 2 , are we to look for patterns only in the rows 13 and 14 ? What about the data in rows 15 through 22 ? Is this data to be ignored ?

The data in Shoe 1 is only 37 columns wide , and that in Shoe 2 is only 41 columns wide ; I assume this is because the ties have been eliminated. Can I assume that in the absence of even a single tie , the data would have been 80 columns wide ?

Narayan
 

JAucamp

New Member
Hello NARAYANK991,
What I was doing manually was to create the shoe format with the code. I then copy the shoe to Patterns tab and manually look for the patterns. I would then clear the shoe from Shoe format and then run the code for the 2nd shoe. Copy the shoe to patterns tab underneath the previous shoe and look for the patterns.
I am not sure which will be easier to code. Basically I need all the shoes underneath each other like on the Shoe Format tab and to look for the patterns on each shoe. Not sure if it is easier to create the shoe, look for the patterns and then to copy the shoe to the next tab and start all over again.

Correct if you look at the data of Shoe 1 we only use rows 2 and 3 and for Shoe 2 only rows 13 and 14. We ignore the rest of the data, but still display the data.

Yes the amount of ties does affect the amount of columns, but the real reason is the amount of "P" or "B"s. Sometimes you can get say 18 "P"s in a row and the 10 "B"s etc. you will end up using less columns.
If the entire shoe is B, P, B, P, B, P, B, P then you should have about 80 columns.
Normally 8 rows and 45 columns is enough.
What happens if there more then 8 "B"'s
We arrange it this way. 8 DOWN and 8 ACROSS
B
B
B
B
B
B
B
B B B B B B B B B

Thanks,
Johan
 

Techtrend

Member
Hi

Want to develop baccarat score board in excel ,The details are attached,
I had tried Sequence formula but was not successful to capture the Sequence of Gains,

Can you please help me to fix these
 

Attachments

Peter Bartholomew

Well-Known Member
This is well beyond my understanding but I had some success in recognising a pattern of "B"s and "P"s using a named formula Test1 which refers to
= AND( INDEX( Shoe1, {1,1;2,2}, hand+{0,1;0,1} ) = {"B","P";"B","P"} )
where 'hand' is a relative reference to the column number that may be a hand. This only recognises the top-left cell of a block of 4.
 

p45cal

Well-Known Member
Want to develop baccarat score board in excel ,The details are attached,
I had tried Sequence formula but was not successful to capture the Sequence of Gains
See attached. Columns E:F are helper columns, you can hide them. Button to add scores at the bottom of the list.

to moderators: Posted here as TechTrend's other thread was closed…
 

Attachments

vletm

Excel Ninja
Raju17785
You should open a new tread
as You could known
based Forum Rules
 
Top