In columns C-G I have certain letters, and I want to see whether any letters in row 3 match those in rows 3-100, any in row 4 match those in 3-100 etc. I've sent up the following formula (this is from J4, which will see if any in row 3 match row 5, and I can drag this down column H so that it always compares the row with row 5):
=COUNTIF($C3:$G3,$C$5)+COUNTIF($C3:$G3,$D$5)+COUNTIF($C3:$G3,$E$5)+COUNTIF($C3:$G3,$F$5)+COUNTIF($C3:$G3,$G$5)
(There's probably a simpler way to do this!)
However, I then want to drag the equation over to column K, and so on, and make it always look at the next row down, so K always checks against 6C-G, L always against 7C-G and so on. I don't know how to make it so that it works when I drag it down, but also works when I drag it across.
If possible, I'd also love to see if sequences are the same in other rows. For example, if one row contains A B C D E, I'd like to see if others contain A B in the same position, or ABC, BCD, CDE, DE etc.
I hope that makes sense! Thank you for even getting this far!
