'Lars-Åke Aspelin[_4_ Wrote:
;949033']On Mon, 26 Apr 2010 15:58:59 +0100, Barny
wrote:
-

I have a spreadsheet with 14 columns and 20 rows - and what I would
like
is a formula that will look in a particlular row i.e B1:B14 and
return
and X in cell B15 if consequtive cells of 4 or more initials are
found
e.g. if B1:B2:B3:B4 = TT in each cell then the result in B15 would be
X
and if c3:c4:c5:c6 = MM the result in B16 would be X. If however
B1:B2:B3 = TT and B4 = NN then no X is returned (cells will either
contain initials or be blank)

is this possible?-

I think you are mixing columns and rows. B1:B14 is part of a column,
not a row.

Assuming that you have data i 14 rows and 20 columns, in the range
A1:T14 and that you what the X's on row 15, i.e. on the row just below
your data.

Try the following formula in cell A15:

=IF(SUMPRODUCT(--(A1:A11""),--(A1:A11=A2:A12),--(A2:A12""),--(A2:A12=A3:A13),--(A3:A13""),--(A3:A13=A4:A14))0,"X","")

Copy the formula to the right to cover A15:T15.

The result is that you will have an "X" on row 15 if there are at
least one occurance of consecutive cells in rows 1 to 14 in the
corresponding column that have the same non-blank value.

Hope this helps / Lars-Åke

have been using this formula and it works well but I have subsequently
realised that whilst I need to look for consecutive occurancies, I need
to ignore blanks e.g if b1=tt b2=tt b3=blank b4=tt b5=blank b6=tt then
b15 =X

is this possible?




--
Barny