On Thu, 6 May 2010 12:22:49 +0100, Barny
wrote:


'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?

To ignore blank cells I suggest the following solution that makes use
of some helper rows.

Your original data are still in cells A1:T14

Make sure that all cells in A15:T28 are blank.

Put the following formula in cell A29:

=IF(ROW()-ROW(A$28)COUNTA(A$1:A$14),"",INDEX(A$1:A$14,SMALL (IF(A$1:A$14"",ROW(A$1:A$14)),ROW()-ROW(A$28))))

Note: This is an array formula that has to be confirmed with
CTRL+SHIFT+ENTER rather than just ENTER.

Copy the formula in cell A29 across B29, C29 all the way to T29.
Copy cells A29:T29 down all the way to row 42.

Finally, put the same formula that you have used before in cell A43,
just with modified ranges, like this:

=IF(SUMPRODUCT(--(A29:A39""),--(A29:A39=A30:A40),--(A30:A40""),--(A30:A40=A31:A41),--(A31:A41""),--(A31:A41=A32:A42))0,"X","")

Copy cell A43 across B43, C43 all the way to T43.

Rows 15 to 42 are the helper rows and you can hide them if you like.

Hope this helps. / Lars-Åke



..