Question description:
This user has given permission to use the problem statement for this
blog.
Repost:
the user to whom I connected earlier should pick up. others please don't pick it
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 O. C. in 52 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
25/05/2018 - 06:28
Hi welcome to Gotit
Excelchat Expert
25/05/2018 - 06:29
The other expert seems to be offline can you please share your sheet and explain
User
25/05/2018 - 06:29
hello! I am talking to the same advisor with whom I have already discussed the work?
Excelchat Expert
25/05/2018 - 06:29
no
User
25/05/2018 - 06:30
guys its not help. I thinks I have wasted my money please tell me how I can cancel the plan, so that the amount is not taken from my account from next month
User
25/05/2018 - 06:31
its a big & complicated task, so 1 hour is not enough and dealing with new advisor everytime with no result is not going to help me
Excelchat Expert
25/05/2018 - 06:31
For any payment queries drop a mail to sheets@gotitapp.co
Excelchat Expert
25/05/2018 - 06:32
Please share the sheet and let me see
User
25/05/2018 - 06:32
I have explained this to 3 advisor all promising that it can be done. I am giving you guys good rating think that after repost it will get resolve but no you all are just fooling me
User
25/05/2018 - 06:33
you guys never response to emails. I have seen that
Excelchat Expert
25/05/2018 - 06:33
When you post a question
User
25/05/2018 - 06:33
please tell me before hand what if the issue is not resolved in 1 hours
Excelchat Expert
25/05/2018 - 06:33
it will be displayed to team of experts who are online and one can claim a question
User
25/05/2018 - 06:34
yes, but that person claim that if I mentioned all that like my first 2 lines then he will pick it
Excelchat Expert
25/05/2018 - 06:34
I will solve it as I will ask Gotit team to send you the mail if session ends
Excelchat Expert
25/05/2018 - 06:35
he seems to be offline or question didn't appear to him
User
25/05/2018 - 06:35
is this a genuine promise or again I am hearing fake one?
Excelchat Expert
25/05/2018 - 06:36
genuine one and one more this as you are saying its very complex is it solvable through Excel formula or should we use VBA?
Excelchat Expert
25/05/2018 - 06:37
thing*
User
25/05/2018 - 06:37
here is my email ID, if anyone asked why the contract is shared just let them know that the user was unhappy and wanted the solution for once and all. anita19june@gmail.com, just sent the file here or in the same mail ID from which the account is created
User
25/05/2018 - 06:37
I need a formula not VBA
Excelchat Expert
25/05/2018 - 06:37
I got a message like sensitive content
User
25/05/2018 - 06:38
my id: anita19june its gmail account, please share the file once its done
Excelchat Expert
25/05/2018 - 06:38
ok
User
25/05/2018 - 06:39
Doc file for better and clear understanding (Just read page 1)
User
25/05/2018 - 06:39
https://docs.google.com/document/d/1fBWLYxGAECcTn5AsOoYBVrbSKq1ZaA9Sht6ZNANnUfI/edit?usp=sharing
Excelchat Expert
25/05/2018 - 06:40
Can you share the sheet
User
25/05/2018 - 06:40
read it afterwards, I will explain everything personally to you. just download the excel file/open and follow my instructions
User
25/05/2018 - 06:41
excel
User
25/05/2018 - 06:41
https://drive.google.com/file/d/14tOI0P2fTFVaKbAbnZJ4O-7PqV-ALSM3/view?usp=sharing
User
25/05/2018 - 06:41
please download
Excelchat Expert
25/05/2018 - 06:41
ok
User
25/05/2018 - 06:42
once done let me know
Excelchat Expert
25/05/2018 - 06:43
done
User
25/05/2018 - 06:45
ok I need formula to be put starting from AI8 downward cells to EK8
User
25/05/2018 - 06:45
go to the cell number AI1
User
25/05/2018 - 06:46
you will see its written: SHOOPEE 3W->3W
Excelchat Expert
25/05/2018 - 06:46
yes
Excelchat Expert
25/05/2018 - 06:47
then
User
25/05/2018 - 06:48
that the first criteria that we need to look for and match in the sheet
User
25/05/2018 - 06:48
we need to look for 2 times 3w side by side in the cell range B to I
User
25/05/2018 - 06:49
look H62 and I62 cells
User
25/05/2018 - 06:50
you will see there are 3w in both the cells, so 2 times 3w was repeated. As asked in 1st criteria cell AI1
User
25/05/2018 - 06:50
Am I clear till now?
Excelchat Expert
25/05/2018 - 06:50
1 min
User
25/05/2018 - 06:51
ok
Excelchat Expert
25/05/2018 - 06:52
yes clear
User
25/05/2018 - 06:53
great! Now keep this in mind and lets move to next stage
User
25/05/2018 - 06:53
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 - 06:53
yes
User
25/05/2018 - 06:53
Like in SHOOPEE 3W->3W : 3w->3w means 3wins...w=win and L=Lose
Excelchat Expert
25/05/2018 - 06:54
ok
User
25/05/2018 - 06:54
1 / L+1W+2W = 1/ is shooter ID
User
25/05/2018 - 06:54
from cell range J to AH, you will find shooter1 to shooter25 but in this particular column AI, we will collect the data for shooter1 only
Excelchat Expert
25/05/2018 - 06:54
and L is loss 1w is win 2w means 2 wins
Excelchat Expert
25/05/2018 - 06:55
ok
User
25/05/2018 - 06:56
yes, you will find either L or 1w or 2w or 3w or 4w anything with number plus w or L
User
25/05/2018 - 06:56
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
Excelchat Expert
25/05/2018 - 06:57
ok
User
25/05/2018 - 06:57
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 - 06: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 - 06:58
l 1w and 2w can be in any order?
Excelchat Expert
25/05/2018 - 07:00
in column J?
User
25/05/2018 - 07:00
yes just need to see whether after matching 3w 3w (2 times 3 sets of consecutive wins) in the cell range B to I
User
25/05/2018 - 07:00
you find any of this 3 value in J column
Excelchat Expert
25/05/2018 - 07:01
ok then?
User
25/05/2018 - 07:01
its can be in any order or can be repeat multiple time in J column
Excelchat Expert
25/05/2018 - 07:01
ok
User
25/05/2018 - 07: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 - 07:03
its base on first criteria only right?
Excelchat Expert
25/05/2018 - 07:03
the value in AI62 should be displayed based on first criteria only
Excelchat Expert
25/05/2018 - 07:04
if it matches 3w 3w it will be 1 if 3w and >3w then -7
Excelchat Expert
25/05/2018 - 07:04
I am I correct?
User
25/05/2018 - 07:06
if only 1st is matched (like 3w->3w) and 2nd not (3 values mentioned) then -7 because you will find somthing will be there J column cells like 3w, 4w, or 2L, 3L which is not asked in AI2 cels
User
25/05/2018 - 07:06
yes
User
25/05/2018 - 07:06
if nothing match keep the cell blank
User
25/05/2018 - 07:07
got it?
Excelchat Expert
25/05/2018 - 07:07
wait I have few questions
User
25/05/2018 - 07:07
ok ask
Excelchat Expert
25/05/2018 - 07:08
First criteria satisfied 3W 3W and 2nd criteria failed then? -7
User
25/05/2018 - 07:08
yes correct
Excelchat Expert
25/05/2018 - 07:08
if first criteria failed then?
Excelchat Expert
25/05/2018 - 07:08
blank
User
25/05/2018 - 07:09
right it should be blank
Excelchat Expert
25/05/2018 - 07:09
if both the criteria's are satisfied then 1
User
25/05/2018 - 07:09
yes
Excelchat Expert
25/05/2018 - 07:09
Can I use VBA to solve this or only formula?
Excelchat Expert
25/05/2018 - 07:10
because to solve this in formula it consumes lot of time
User
25/05/2018 - 07:10
same goes for columns like AJ, AK and so on
Excelchat Expert
25/05/2018 - 07:11
Yes but I asked a question
User
25/05/2018 - 07:11
I prefer formula
User
25/05/2018 - 07:11
as I may need to reapply it or change few things which I can't to in VBA
User
25/05/2018 - 07:12
*can't do
Excelchat Expert
25/05/2018 - 07:12
I per my knowledge formula may sometimes give inaccurate result with this much data
Excelchat Expert
25/05/2018 - 07:12
As per*
User
25/05/2018 - 07:12
can you do this with VBA?
Excelchat Expert
25/05/2018 - 07:12
yes
User
25/05/2018 - 07:13
please try for formula but if not possible go for VBA. I don't want to deal with this issue any working solution is now fine by me
Excelchat Expert
25/05/2018 - 07:14
ok
Excelchat Expert
25/05/2018 - 07:14
Can I create some new rows in the sheet
User
25/05/2018 - 07:14
sure, but just hide those
Excelchat Expert
25/05/2018 - 07:15
Once this is done I will ask my team to share the sheet to you
Excelchat Expert
25/05/2018 - 07:15
ok
Excelchat Expert
25/05/2018 - 07:15
can you wait for 1 to 2 days?
User
25/05/2018 - 07:16
yes, I can wait but need the solution to this
Excelchat Expert
25/05/2018 - 07:16
ok
User
25/05/2018 - 07:16
please don't let me down after 2 days
Excelchat Expert
25/05/2018 - 07:17
Sure
User
25/05/2018 - 07:17
please do read the doc file to see different scenarios or for reference
Excelchat Expert
25/05/2018 - 07:17
ok
Excelchat Expert
25/05/2018 - 07:17
I will start that
Excelchat Expert
25/05/2018 - 07:17
Nice meeting you have a nice day
User
25/05/2018 - 07:18
what should I do if no respose or mail was there after 2 days
User
25/05/2018 - 07:18
there?
Excelchat Expert
25/05/2018 - 07:18
yes
Excelchat Expert
25/05/2018 - 07:19
Keep hope in me
Excelchat Expert
25/05/2018 - 07:19
If I was not able to I will let you know through My teams mail i.e. sheets@gotitapp.co
User
25/05/2018 - 07:19
ok I am trusting you
Excelchat Expert
25/05/2018 - 07:20
Thank you
User
25/05/2018 - 07:20
ok :)
User
25/05/2018 - 07:20
\thanks for your time
User
25/05/2018 - 07:20
have nice day..bye
Excelchat Expert
25/05/2018 - 07:20
Its my pleasure
User
25/05/2018 - 07:20
:)
User
25/05/2018 - 07:20
I am closing the chat now
Excelchat Expert
25/05/2018 - 07:21
ok bye
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.
