Count matching data

[Barny]

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

is this possible

 

[Lars-Åke Aspelin]

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

 

[Barny]

;949033']On Mon, 26 Apr 2010 15:58:59 +0100, Barny

I have a spreadsheet with 14 columns and 20 rows - and what I woul like
is a formula that will look in a particlular row i.e B1:B14 an return
and X in cell B15 if consequtive cells of 4 or more initials ar found
e.g. if B1:B2:B3:B4 = TT in each cell then the result in B15 would b 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 subsequentl

realised that whilst I need to look for consecutive occurancies, I nee
to ignore blanks e.g if b1=tt b2=tt b3=blank b4=tt b5=blank b6=tt the
b15 =X

is this possible

 

[Lars-Åke Aspelin]

;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<>""),--(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



..

 

[Barny]

;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 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 woul 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 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 jus below
your data.

Try the following formula in cell A15:

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 hav

subsequently-
realised that whilst I need to look for consecutive occurancies, need
to ignore blanks e.g if b1=tt b2=tt b3=blank b4=tt b5=blank b6=t 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



..

thanks for your response on this however, I think my lack of exce
ability means i am getting a bit lost. I wondered if we could work o
the following basis (assuming the formula is possible) -aim is t
identify where there is 4 consecutive rows of initials between A1:A1
but ignoring blank cells:

rows A1 to A14 which will either contain initials or be blank
example - if
row A1=tt, A2=tt, A3=MS, A4=tt A5toA14 all =blank then the result i blank
row A1=tt, A2=tt, A3=tt, A4=tt, A5to14=bank result is X

row A1=tt A2=blank A3=MS A4=tt A5=blank A6=tt A7=tt A8=tt

A9toA14=blank then result=X because in this sequence (A1:A8) there i
still a row of consecutive initials A4:A8 (ignoring blank A5)

sorry if this isnt clea

 

[Lars-Åke Aspelin]

;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:

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



..

thanks for your response on this however, I think my lack of excel
ability means i am getting a bit lost. I wondered if we could work on
the following basis (assuming the formula is possible) -aim is to
identify where there is 4 consecutive rows of initials between A1:A14
but ignoring blank cells:

rows A1 to A14 which will either contain initials or be blank
example - if
row A1=tt, A2=tt, A3=MS, A4=tt A5toA14 all =blank then the result is blank
row A1=tt, A2=tt, A3=tt, A4=tt, A5to14=bank result is X

row A1=tt A2=blank A3=MS A4=tt A5=blank A6=tt A7=tt A8=tt,

A9toA14=blank then result=X because in this sequence (A1:A8) there is
still a row of consecutive initials A4:A8 (ignoring blank A5)

sorry if this isnt clear

This is very clear.
I can't do this with a single formula. It is probably possible, but I
guess the formula will be very complex, but someone else may come up
with something.

The formulas that I proposed make use of some helper rows.
Have you tried these formulas? Do they give you the result you expect?

If it is not, for some reason, possible to have the helper rows on
rows 15 to 28 you may have a copy of your original data somewhere else
in the sheet, e.g. below all other data. And you can hide these rows
as well as the helper rows. Then you can have the final formula on row
15 if you want.

There is always the possibility to have a User Defined Function to
return the expected result if you can allow macro execution in your
workbook.

If you do want to try a UDF, here is a proposal

Function consecutive(r As Range, m) As Boolean
Application.Volatile
consecutive = False
For i = 1 To r.Rows.Count - m + 1
If r(i).Value <> "" Then
found = True
n = 1
For j = i + 1 To r.Rows.Count
If (r(j).Value <> "") And (r(j).Value <> r(i).Value) Then
found = False
Exit For
End If
If found And (r(j).Value = r(i).Value) Then
n = n + 1
End If
If n = m Then
consecutive = True
Exit Function
End If
Next j
End If
Next i
End Function

in cell A15 you now put the formula

=IF(consecutive(A1:A14,4),"X","")

Copy the formula to the right to cell B15:T15

Hope this helps / Lars-Åke