**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.*