See attached.
Defined (K1 , K2 ......K5) are in column Ki (B3:B7)
Range where Ki appear is Range of Ki (E3:G11)
Result is in Check column (C3:C7)
If Ki, appear more then ONCE, then Crush for that Ki, if only ONCE then Clear
Even K1K1 is Crush but only K1 row will show Crush
Last edited by ionelz; 01-20-2019 at 05:26 PM.
Sorry - you have lost me on this one. Can you explain in more specific detail what we are looking at here?
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
Ki, should appear only ONCE in any cell in range, and that mean Clear in Check Column for that Ki
But for example in F3, is K1 and K5
In Check column, K1 and K5 (rows) show Crush
So if Ki defined in (B3:B7) from appears only ONCE in range (E3:G11) then say Clear for that Ki in C3:C7
If Ki appear more then ONCE then say Crush for that Ki's (should be at least 2) and show in C3:C7 who Crush (in this case K1 and K5)
So need to check Ki in any cell, if is ONCE then Clear (in Check column), else show Crush between Ki's
This was done by Bo_Ry in my preview Post but now the question is a little different
Last edited by ionelz; 01-20-2019 at 06:54 PM.
Nope. Absolutely no clearer. Where is the previous thread?
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.
Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
See attached.
I want formula in C3
C3 and C7 show Crush because K1 and K5 are in same cell in range
C5 show Crush because K3 and K3 are in same cell
The rest are single in cell so, Clear
I've lost count of the contradictions and am more confused than before.
Are we now counting Kis in range E3:G11 ? Over the whole range or just those in the same row as respective Ki ?
Dave
Like I said :
- I am looking in every single cell in Range
- I can have only ONE Ki in cell.
- If I have 2 or more , I need to make a correction
- but the range is so big so I need a warning in Ki column
- Who is in cell F3 for example? Is K1 and K5
- that is why K1 show Crush and K5 show Crush
- in another cell is K3 and K3 that is why K3 show Crush
- the rest are Single in cell, no need to worry so Clear for them
Please try at C3 with CSE and drag down
=IF(COUNT(FIND(B3,IF(LEN($E$3:$G$11)>2,$E$3:$G$11))),"Crush","Clear")
Or normal Enter
=IF(COUNTIFS($E$3:$G$11,"*"&B3&"*",$E$3:$G$11,"???*"),"Crush","Clear")
Last edited by Bo_Ry; 01-21-2019 at 12:43 AM. Reason: Add formula
Super, like always !
I have an issue , because I did not explain well
It works for all Ki (i<=9)
But if i>9 than for example, K1 "crush" with K14 or with K12 ..... (even if they are not in same cell)
Probably because K1 is "root" of K14 ...
So "string" was a bad name, I am looking for EXACT Ki
Last edited by ionelz; 01-21-2019 at 09:57 AM.
Please use K01 instead of K1
and change the formula to
CSE
=IF(COUNT(FIND(B3,IF(LEN($E$3:$G$11)>3,$E$3:$G$11))),"Crush","Clear")
Or normal Enter
=IF(COUNTIFS($E$3:$G$11,"*"&B3&"*",$E$3:$G$11,"????*"),"Crush","Clear")
Thank you again
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks