A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » New Users
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Count matching data



 
 
Thread Tools Display Modes
  #1  
Old April 26th, 2010, 03:58 PM posted to microsoft.public.excel.newusers
Barny[_7_]
external usenet poster
 
Posts: 1
Default Count matching data


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?




--
Barny
  #2  
Old April 26th, 2010, 08:27 PM posted to microsoft.public.excel.newusers
Lars-Åke Aspelin[_4_]
external usenet poster
 
Posts: 74
Default Count matching data

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



  #3  
Old May 6th, 2010, 12:22 PM posted to microsoft.public.excel.newusers
Barny[_8_]
external usenet poster
 
Posts: 1
Default Count matching data


'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
  #4  
Old May 7th, 2010, 06:05 AM posted to microsoft.public.excel.newusers
Lars-Åke Aspelin[_4_]
external usenet poster
 
Posts: 74
Default Count matching data

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



..
  #5  
Old May 10th, 2010, 01:42 PM posted to microsoft.public.excel.newusers
Barny[_9_]
external usenet poster
 
Posts: 1
Default Count matching data


'Lars-Åke Aspelin[_4_ Wrote:
;952052']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



..

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




--
Barny
  #6  
Old May 10th, 2010, 09:26 PM posted to microsoft.public.excel.newusers
Lars-Åke Aspelin[_4_]
external usenet poster
 
Posts: 74
Default Count matching data

On Mon, 10 May 2010 13:42:40 +0100, Barny
wrote:


'Lars-Åke Aspelin[_4_ Wrote:
;952052']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



..

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


 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 11:58 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.