;952052']On Thu, 6 May 2010 12:22:49 +0100, Barny
'Lars-Åke Aspelin[_4_ Wrote: -
;949033']On Mon, 26 Apr 2010 15:58:59 +0100, Barny
--
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 said:
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
..