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 E. S. in 60 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
User
25/05/2018 - 04:23
Hello!
Excelchat Expert
25/05/2018 - 04:23
Hello
Excelchat Expert
25/05/2018 - 04:23
Welcome to got it pro.
Excelchat Expert
25/05/2018 - 04:24
Can you please show me the file?
Excelchat Expert
25/05/2018 - 04:24
I need to see data to understand the problem clearly.
User
25/05/2018 - 04:25
Thanks! I am stuck on this issue from last few days. I would be really grateful if you can provide me the working solution
Excelchat Expert
25/05/2018 - 04:25
I believe I can help.
User
25/05/2018 - 04:26
Doc file for better and clear understanding (Just read page 1)
Excelchat Expert
25/05/2018 - 04:26
Although as I have mentioned earlier, I need to see the fiel.
User
25/05/2018 - 04:26
for now read the page 1 only as thats the phase one
User
25/05/2018 - 04:26
https://docs.google.com/document/d/1fBWLYxGAECcTn5AsOoYBVrbSKq1ZaA9Sht6ZNANnUfI/edit?usp=sharing
User
25/05/2018 - 04:26
here is the excel file link:
User
25/05/2018 - 04:26
https://drive.google.com/file/d/14tOI0P2fTFVaKbAbnZJ4O-7PqV-ALSM3/view?usp=sharing
User
25/05/2018 - 04:26
Please download
Excelchat Expert
25/05/2018 - 04:27
Thanks.
User
25/05/2018 - 04:28
Welcome!
Excelchat Expert
25/05/2018 - 04:31
So, I'm looking at the file and it seems I need more info to understand the problem.
User
25/05/2018 - 04:31
please ask
Excelchat Expert
25/05/2018 - 04:31
Can you please tell me what should be the result in AI8?
User
25/05/2018 - 04:32
let me explain you please open the excel file
Excelchat Expert
25/05/2018 - 04:33
I have it opened.
User
25/05/2018 - 04:34
Follow the line number AI1
Excelchat Expert
25/05/2018 - 04:34
Hmm.
User
25/05/2018 - 04:34
you will see its written: SHOOPEE 3W->3W
Excelchat Expert
25/05/2018 - 04:35
Yeah, I can see that.
User
25/05/2018 - 04:36
that the first criteria that we need to look for and match in the sheet
User
25/05/2018 - 04:37
we need to look for 2 times 3w side by side in the cell range B to H
User
25/05/2018 - 04:38
are you clear till now?
Excelchat Expert
25/05/2018 - 04:38
all clear except I don't understand the meaning of 2 times side by side
User
25/05/2018 - 04:39
sorry the range is B to I
User
25/05/2018 - 04:39
ok look H62 and I62 cells
Excelchat Expert
25/05/2018 - 04:40
I'm there.
User
25/05/2018 - 04:40
you will see there are 3w in both the cells, so 2 times 3w was repeated
User
25/05/2018 - 04:41
as asked in cell AI1 cell
User
25/05/2018 - 04:41
got it?
Excelchat Expert
25/05/2018 - 04:41
Okay, got it. Now going back to AI8, what should be result?
User
25/05/2018 - 04:42
yes, we will get the result after matching the second criteria. Now keep this in mind and lets move to next stage
Excelchat Expert
25/05/2018 - 04:42
listening....
User
25/05/2018 - 04:43
ok below cell AI1, you will find "1 / L+1W+2W" is written in cell AI2..that our next criteria
Excelchat Expert
25/05/2018 - 04:43
listening...
User
25/05/2018 - 04:44
Like in SHOOPEE 3W->3W : 3w->3w means 3wins...w=win and L=Lose
Excelchat Expert
25/05/2018 - 04:45
okay.
User
25/05/2018 - 04:45
1 / L+1W+2W = 1/ is shooter ID
User
25/05/2018 - 04:45
from cell range J to AH, you will find shooter1 to shooter25
User
25/05/2018 - 04:46
but in this particular column AI, we will collect the data for shooter1 only
Excelchat Expert
25/05/2018 - 04:47
May I know why so?
User
25/05/2018 - 04:48
so shooter1 in column J / either look for L or 1w or 2w (this 3 value) after 3w 3w (first creteria is match) look for this criteria in column J
User
25/05/2018 - 04:49
sorry "May I know why so?"
User
25/05/2018 - 04:49
I am not clear about your query
Excelchat Expert
25/05/2018 - 04:49
In this particular column AI .....
Excelchat Expert
25/05/2018 - 04:49
that was my query.
User
25/05/2018 - 04:50
listening...
User
25/05/2018 - 04:50
AI is assigned for shooter1 only
Excelchat Expert
25/05/2018 - 04:50
that kind of answers my question.
User
25/05/2018 - 04:51
like if you look in column AK
User
25/05/2018 - 04:51
cell AK2 you will find its written 2/ that's shows that here we will match the data of shooter numer 2
Excelchat Expert
25/05/2018 - 04:52
Got it.
Excelchat Expert
25/05/2018 - 04:54
Next?
User
25/05/2018 - 04:54
ok now combine both the criteria match 3w-3w in column range B to I once matched look whether shooter1 (column J) have got any of the mentioned 3 values ( L or 1w or 2w)
User
25/05/2018 - 04:57
like all this is matched in cell H62 (3w), I62 (3w) and shooter1 (look in column J straight line) cells J62 (1w is written) one of the 3 value is there
Excelchat Expert
25/05/2018 - 05:00
Hmm, I see that now.
User
25/05/2018 - 05:01
so we got 1 our match - that will make AI62 cell answer= 1 (after applying the formula). one will be there only if you find 1st criteria + any of those 3 values for shooter1. for instance, if suppose you have found 3w or anything above 3w like 4w, 5w etc the it should reflect the value -7 in cell AI62
Excelchat Expert
25/05/2018 - 05:02
I do understand the result should be 1 when all criteria matched.
Excelchat Expert
25/05/2018 - 05:02
I need clarification on why -7 if the value is above 3W.
User
25/05/2018 - 05:02
yes, but if only 1st is matched and 2 not then -7
User
25/05/2018 - 05:03
*2nd not
Excelchat Expert
25/05/2018 - 05:03
Okay, Understood.
User
25/05/2018 - 05:03
if nothing match keep the cell blank
User
25/05/2018 - 05:03
got it?
Excelchat Expert
25/05/2018 - 05:03
Yeah, clear as crystal.
User
25/05/2018 - 05:03
great :)
Excelchat Expert
25/05/2018 - 05:04
The only fear now is that we might run out of time. Session maximum time is 1 hour and we are in the last 20 minutes.
Excelchat Expert
25/05/2018 - 05:04
I'm trying my best to finish it in time, please stay with me.
User
25/05/2018 - 05:05
I have taken lots of your time but hoping that we will get the result and you will free me from this frustration of not knowing the solution
User
25/05/2018 - 05:05
can't we extend the chat session
User
25/05/2018 - 05:05
I have already purchased your plan
Excelchat Expert
25/05/2018 - 05:05
Unfortunately we have used the 2 available extensions.
Excelchat Expert
25/05/2018 - 05:06
Thanks, in case we run out of time, can you please post again? I will keep working on the problem.
User
25/05/2018 - 05:07
yes sure. just tell me how this issue will be directed to your pot only and not assigned to any other expert avaible
Excelchat Expert
25/05/2018 - 05:08
I will bid minimum credits to claim the question. Since my rating is high and also I would bid low, the winning chance is 100%.
Excelchat Expert
25/05/2018 - 05:10
Can we use two helper row at the top of the sheet?
User
25/05/2018 - 05:10
I don't know what you will do on the back end or how your system works. But I don't want to repeat all this from scratch to any other advisor
Excelchat Expert
25/05/2018 - 05:10
No worries, I will finish the work.
User
25/05/2018 - 05:10
yes sure, you can add any row. we can hide that afterward, right?
Excelchat Expert
25/05/2018 - 05:11
Yeah, this would make the task a lot easier.
User
25/05/2018 - 05:11
great
User
25/05/2018 - 05:13
ok when I should re-post this query right away after this session or should I wait for few mints and post. and may I know your name I will write your name at the top of my query description
Excelchat Expert
25/05/2018 - 05:13
It would be great if you post a few minutes later, so I would get enough time to finish the work.
User
25/05/2018 - 05:14
hope that will help to identify which query is mine... I don't know how you will track my query :)
Excelchat Expert
25/05/2018 - 05:14
And unfortunately sharing personal identification information is against the rule of got it.
Excelchat Expert
25/05/2018 - 05:15
Please use [REPOST] tag and the same problem description.
User
25/05/2018 - 05:15
ok cool :)
User
25/05/2018 - 05:15
thanks for undertsnding my concern
User
25/05/2018 - 05:16
ok I will re-post after 30 mints, is that fine?
Excelchat Expert
25/05/2018 - 05:16
I'm really sorry that it took almost 40 minutes to fully understand the problem.
Excelchat Expert
25/05/2018 - 05:16
Yeah, that would be ideal.
User
25/05/2018 - 05:17
that's no problem. I am stuck in this for last 4 days, so 40 mints is still reasionable if I got the solution and will provide you the best review for that
Excelchat Expert
25/05/2018 - 05:18
Thank you.
User
25/05/2018 - 05:18
here I should say sorry as your 40 mints were invested in 1 query only
Excelchat Expert
25/05/2018 - 05:19
No No, Its really my fault. I was slow to understand. :'(
Excelchat Expert
25/05/2018 - 05:19
And hence it took a lots of time.
User
25/05/2018 - 05:21
ok once this session end I will re-post it after 30 mints. Please be there... I am scared, feeling like playing a blind game...hoping that everything works and I get you only
User
25/05/2018 - 05:21
:)
Excelchat Expert
25/05/2018 - 05:22
Thanks for your patience.
User
25/05/2018 - 05:22
Welcome & thanks :)
User
25/05/2018 - 05:23
please do read the doc files to see the different cases
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.
