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.
