Question description:
This user has given permission to use the problem statement for this
blog.
I need a formula written into cells (AI8 to EK8) to identify patterns and then count specific data. sheet name: SHOPEE PATTERNS ONE ROW have data of different shooter (wins and losses from cells B to H) that I need identify. There were 2 rows (AI4:EK4 and AI2:EK2) that will determine which cells to count and which to keep blank after applying the formula
Solved by V. H. in 60 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
User
22/05/2018 - 06:27
hello!
Excelchat Expert
22/05/2018 - 06:28
hi boss
Excelchat Expert
22/05/2018 - 06:28
:)
User
22/05/2018 - 06:29
please read it
User
22/05/2018 - 06:29
I need a formula written into cells (AI8 to EK8) to identify patterns and then count specific data. sheet name: SHOPEE PATTERNS ONE ROW have data of different shooter (wins and losses from cells B to H) that I need identify. There were 2 rows (AI4:EK4 and AI2:EK2) that will determine which cells to count and which to keep blank after applying the formula. For instance in cell AI1 written "SHOOPEE 3W->3W" that means I am looking for 2 consecutive wins in cells range from B to H. AI2 determine the second criteria "1 / L+1W+2W" 1 here is to address shooter number one those (L+1W+2W) tells us what I am looking in the column listed under shooter number 1
Excelchat Expert
22/05/2018 - 06:29
Ok Dear
User
22/05/2018 - 06:29
here is the file link
User
22/05/2018 - 06:29
https://drive.google.com/file/d/14tOI0P2fTFVaKbAbnZJ4O-7PqV-ALSM3/view?usp=sharing
User
22/05/2018 - 06:34
there?
Excelchat Expert
22/05/2018 - 06:35
Yeah i am working
User
22/05/2018 - 06:37
hello!
User
22/05/2018 - 06:37
are you still there?
User
22/05/2018 - 06:38
ok are you clear what I am looking for?
Excelchat Expert
22/05/2018 - 06:38
Yes Dear
Excelchat Expert
22/05/2018 - 06:38
I am Doing for you :)
User
22/05/2018 - 06:38
Great :)
User
22/05/2018 - 06:43
you can refer to the doc file (page 1 only for 1st stage) to know what value I want after the formula is applied in cells AI8 to EK8
User
22/05/2018 - 06:43
https://docs.google.com/document/d/1fBWLYxGAECcTn5AsOoYBVrbSKq1ZaA9Sht6ZNANnUfI/edit?usp=sharing
User
22/05/2018 - 06:54
any progress ?
User
22/05/2018 - 06:54
time is running out
Excelchat Expert
22/05/2018 - 06:54
yes i understand
Excelchat Expert
22/05/2018 - 06:55
sorry for being late now i am starting
User
22/05/2018 - 06:55
ok
Excelchat Expert
22/05/2018 - 07:03
[Uploaded an Excel file]
Excelchat Expert
22/05/2018 - 07:04
[Uploaded an Excel file]
User
22/05/2018 - 07:05
which one to downlaod
Excelchat Expert
22/05/2018 - 07:05
In last sheet i filled some cells could you please tell me is this ok
Excelchat Expert
22/05/2018 - 07:05
last one
User
22/05/2018 - 07:05
ok let me check
Excelchat Expert
22/05/2018 - 07:07
col al8:al18
User
22/05/2018 - 07:10
Sorry, its not what I wanted. Can you please refer to doc file that I have shared. that will give you better idea
User
22/05/2018 - 07:10
or I can tell you what i am exactly looking for
Excelchat Expert
22/05/2018 - 07:10
yeah sure
User
22/05/2018 - 07:11
go to cell AI1
Excelchat Expert
22/05/2018 - 07:11
now
User
22/05/2018 - 07:12
3W->3W , meaning I am looking for 2 times wins in a row
Excelchat Expert
22/05/2018 - 07:12
tell me range area
Excelchat Expert
22/05/2018 - 07:13
2 time wins mean 2W or like 2W + 2W
Excelchat Expert
22/05/2018 - 07:13
or 3W + 3W
User
22/05/2018 - 07:14
like check cell H77 and I77
Excelchat Expert
22/05/2018 - 07:14
oh ok
Excelchat Expert
22/05/2018 - 07:15
what is meant by
Excelchat Expert
22/05/2018 - 07:15
numbering giving before w
Excelchat Expert
22/05/2018 - 07:15
like 3 2 4 1
User
22/05/2018 - 07:15
you will find 3W 3W written side by side that means this cell meets the creteria for the search
Excelchat Expert
22/05/2018 - 07:15
ok
User
22/05/2018 - 07:16
suppose in shooting match he won 3 times and again 3 times in a row. Am I making any sense
User
22/05/2018 - 07:16
?
Excelchat Expert
22/05/2018 - 07:16
yes :)
User
22/05/2018 - 07:18
now if you check AI 2 it written "1 / L+1W+2W". here 1 / is to identity which shooter. its shooter number 1 (Shooter1 column J)
Excelchat Expert
22/05/2018 - 07:18
ok
User
22/05/2018 - 07:20
so in J column first look for 2 times 3w 3 win in different cell then see if in the straight J cell any of this 3 values are there L+1w+2w
User
22/05/2018 - 07:21
like in H77 and I77, you will see J77 (shooter1) its written 2w
User
22/05/2018 - 07:21
so its match the looking criteria. so the value here AI77 should be 1
User
22/05/2018 - 07:22
but if suppose we got any different vaule in cell J77 like 3w or anything above 2w then it should be -7
Excelchat Expert
22/05/2018 - 07:23
ok
User
22/05/2018 - 07:23
clear?
User
22/05/2018 - 07:24
is it possible to acheive ?
User
22/05/2018 - 07:24
* acheive
User
22/05/2018 - 07:25
there?
Excelchat Expert
22/05/2018 - 07:25
yes
Excelchat Expert
22/05/2018 - 07:26
m showing you sample as you told me
Excelchat Expert
22/05/2018 - 07:26
just give me 3 to 4 mins
User
22/05/2018 - 07:26
time is running out
User
22/05/2018 - 07:26
what should I do?
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.
