**Question description:**

*This user has given permission to use the problem statement for this blog.*

I was talking to someone. query with the ID problem/32658. Can we resume the work, please
I need a formula written into cells to identify patterns and then count specific data. The pattern data that first needs to be identified is spread across column B through I. The formula should first recognize the pattern within these cells. THEN according to the search criteria, the formula should match one of three values and produce a value of either (1) or -(7). The Sheets labeled 'SHOOPEE PATTERNS ONE ROW' and 'SHOOPEE KEY' are the active worksheets within the file attached that need the formulas pertaining to this project.

Solved by Z. U. in 60 mins

This is the chat thread from the real Excelchat help session. It contains no private user information.

Excelchat Expert
19/05/2018 - 07:30

Hi Welcome to Gotit

User
19/05/2018 - 07:30

Hello! I was discussing the issue with someone from your end

Excelchat Expert
19/05/2018 - 07:31

But he is not in online now

User
19/05/2018 - 07:31

Can I get that advisor as we were close the the solution?

Excelchat Expert
19/05/2018 - 07:31

Can you please tell me your problem so that I can help you

Excelchat Expert
19/05/2018 - 07:31

Please share the sheet

User
19/05/2018 - 07:32

there?

Excelchat Expert
19/05/2018 - 07:32

yes

Excelchat Expert
19/05/2018 - 07:32

Hello

Excelchat Expert
19/05/2018 - 07:32

Are able to see my messages?

User
19/05/2018 - 07:34

Are you there?

Excelchat Expert
19/05/2018 - 07:34

yes

User
19/05/2018 - 07:34

yes now I can see

Excelchat Expert
19/05/2018 - 07:35

Please share your sheet and explain the problem

User
19/05/2018 - 07:35

here is the video hope that may help you

Excelchat Expert
19/05/2018 - 07:35

Video?

User
19/05/2018 - 07:35

https://drive.google.com/file/d/1iCDQUNgoZcbXlBDE1H-KDSCZ6rl6jcHQ/view?usp=sharing

User
19/05/2018 - 07:36

Yes that will work through what I am looking for

User
19/05/2018 - 07:36

one of my colleague have made it

User
19/05/2018 - 07:36

and here is the file

User
19/05/2018 - 07:36

https://drive.google.com/file/d/14tOI0P2fTFVaKbAbnZJ4O-7PqV-ALSM3/view?usp=sharing

User
19/05/2018 - 07:36

its a VBA file but I am looking for non-VBA solution

Excelchat Expert
19/05/2018 - 07:36

ok

Excelchat Expert
19/05/2018 - 07:36

Let me see

User
19/05/2018 - 07:39

ok

User
19/05/2018 - 07:42

Clear

User
19/05/2018 - 07:42

?

Excelchat Expert
19/05/2018 - 07:42

Yes

Excelchat Expert
19/05/2018 - 07:43

But with formula its not possible

Excelchat Expert
19/05/2018 - 07:43

we have to use VBA coding

User
19/05/2018 - 07:44

the last advisor was working with the formula but the chat ended and I don't got the chance to extend the chat

User
19/05/2018 - 07:44

ok so what better way you can suggest

User
19/05/2018 - 07:45

I prefer code but if VBA is the only way please go ahead

Excelchat Expert
19/05/2018 - 07:46

So you need VBA coding

User
19/05/2018 - 07:46

sorry I prefer formula

Excelchat Expert
19/05/2018 - 07:47

ok

Excelchat Expert
19/05/2018 - 07:47

I will try

Excelchat Expert
19/05/2018 - 07:48

I have few doubts can you clear them?

User
19/05/2018 - 07:49

please ask

Excelchat Expert
19/05/2018 - 07:51

shoopee patterns one row sheet

Excelchat Expert
19/05/2018 - 07:52

for shooter 1/L+1W+2W what should I find in iti

Excelchat Expert
19/05/2018 - 07:52

it*

User
19/05/2018 - 07:54

I need a formula written into cells to identify patterns and then count specific data. The pattern data that first needs to be identified is spread across column B through I. The formula should first recognize the pattern within these cells. THEN according to the search criteria, the formula should match one of three values and produce a value of either (1) or -(7). The Sheets labeled 'SHOOPEE PATTERNS ONE ROW' and 'SHOOPEE KEY' are the active worksheets within the file attached that need the formulas pertaining to this project.

User
19/05/2018 - 07:59

there ?

Excelchat Expert
19/05/2018 - 07:59

yes

User
19/05/2018 - 08:02

Can we do it with formula ?

User
19/05/2018 - 08:02

or VBA code?

Excelchat Expert
19/05/2018 - 08:03

I am trying with formula

Excelchat Expert
19/05/2018 - 08:04

I split the pattern to 1/ L + 2w+ 3w to 1 L 2W 3W

Excelchat Expert
19/05/2018 - 08:04

using mid formula

User
19/05/2018 - 08:05

no problem. please go ahead

Excelchat Expert
19/05/2018 - 08:15

I will share a file which has half work keep it with you

Excelchat Expert
19/05/2018 - 08:15

I will try the later half as well

User
19/05/2018 - 08:16

ok please share

Excelchat Expert
19/05/2018 - 08:16

Please check this

[Uploaded an Excel file]

Excelchat Expert
19/05/2018 - 08:19

Matching patterns is not possible using Formulae we have to Go with VBA only

Excelchat Expert
19/05/2018 - 08:20

Using for to loop through shooters

Excelchat Expert
19/05/2018 - 08:20

create 4 variables

User
19/05/2018 - 08:21

after splitting the pattern what you have done

Excelchat Expert
19/05/2018 - 08:21

Variable one variable as true if first pattern matches similarly make true other variable as true if consecutive matches

Excelchat Expert
19/05/2018 - 08:22

Finally make a condition such that if all the four variable becomes true

Excelchat Expert
19/05/2018 - 08:23

then use if condition to give a value like 1 or -7

Excelchat Expert
19/05/2018 - 08:23

Did you get it?

User
19/05/2018 - 08:24

nope

Excelchat Expert
19/05/2018 - 08:24

basically we have to code this logic in VBA

User
19/05/2018 - 08:25

ok, so are you working on it

Excelchat Expert
19/05/2018 - 08:25

with formula we can't do that

Excelchat Expert
19/05/2018 - 08:25

I can't work on VBA as this platform does not support VBA

User
19/05/2018 - 08:28

ok

Excelchat Expert
19/05/2018 - 08:28

please just use for loop logic in VBA it will resolve you issue

Excelchat Expert
19/05/2018 - 08:28

Nice meeting you!!... Have a Great Day!

**This is the output file from the real Excelchat help session:**

*This is an example of the expert help you can get. It contains no private user information.*