find first occurance of certain value in grid
 

I have a worksheet that is 7 columns by 5 rows...in any one of those cells
the user can input a certain text value, say "X". I need to find which cell
that value is in and then test the next two cells, going across and then down
to see if the same value exists in order to assign points based on a
predefined points system. Any ideas on how to do that ?

find first occurance of certain value in grid
 

If I understand you correctly; the formula needs to check whether values are
found in the below way anywere in the array.of 7*5

Col Col
Row x
Row x

Please note that this is an array formula; you press CTRL+SHIFT+ENTER to
enter the formula. If successful in 'Formula Bar' you can notice the curly
braces at both ends like "{=<formula}". I have tried this in A1:G5..You can
modify the range to suit..

=IF(MIN(IF(A1:G5="X",ROW(A1:G5))),IF(INDEX(A1:G5,M IN(IF(A1:G5="X",ROW(A1:G5)))+1,MIN(IF(A1:G5="X",CO LUMN(A1:G5)))+1)="X","Values
found in order","First value one"),"No values found")

If this post helps click Yes
---------------
Jacob Skaria


"Eric_in_EVV" wrote:

I have a worksheet that is 7 columns by 5 rows...in any one of those cells
the user can input a certain text value, say "X". I need to find which cell
that value is in and then test the next two cells, going across and then down
to see if the same value exists in order to assign points based on a
predefined points system. Any ideas on how to do that ?

find first occurance of certain value in grid
 

On Sat, 3 Oct 2009 23:38:02 -0700, Jacob Skaria
wrote:

If I understand you correctly; the formula needs to check whether values are
found in the below way anywere in the array.of 7*5

Col Col
Row x
Row x

Please note that this is an array formula; you press CTRL+SHIFT+ENTER to
enter the formula. If successful in 'Formula Bar' you can notice the curly
braces at both ends like "{=<formula}". I have tried this in A1:G5..You can
modify the range to suit..

=IF(MIN(IF(A1:G5="X",ROW(A1:G5))),IF(INDEX(A1:G5, MIN(IF(A1:G5="X",ROW(A1:G5)))+1,MIN(IF(A1:G5="X",C OLUMN(A1:G5)))+1)="X","Values
found in order","First value one"),"No values found")

If this post helps click Yes
---------------
Jacob Skaria



I think a problem with your proposed solution would be a pattern such as:



| | | | | | | |
| | | | | | | |
| | | | X | | X | |
| | | | | | | X |
| | | | | | | |

In other words, if the grid is A1:G5, with "X" in D3, F3, G4

I would expect the OP would want a Pattern Found since F3 and G4 meet the
criteria.

I also would have thought the OP would want the following to be differentiated:


| | | | | | | |
| | | | | | | |
| | | | X | | X | X |
| | | | | | | X |
| | | | | | | |

--ron

find first occurance of certain value in grid
 

Clarification please... your subject line as well as your request indicates
that multiple cells can contain the text you are looking for... ignoring the
text "after" them, if C4 and B5 both contained this text, which of them
would be considered the "first occurance" as your subject line indicates you
are looking for? That is, are you looking row by row or column by column to
find the first occurance?

As for the "the next two cells, going across and then down" part of your
question, did you mean to look in the 3 cells that Ron's answer is based on,
or were you describing a 2x2 grid of cells?

--
Rick (MVP - Excel)


"Eric_in_EVV" wrote in message
...
I have a worksheet that is 7 columns by 5 rows...in any one of those cells
the user can input a certain text value, say "X". I need to find which
cell
that value is in and then test the next two cells, going across and then
down
to see if the same value exists in order to assign points based on a
predefined points system. Any ideas on how to do that ?

find first occurance of certain value in grid
 

Let me try to clarify with by way of an example spreadsheet:

A B C D E
1 x would equal 1 point
2 x x would equal 2 points
3 x x x would equal 1 point
4 x x x would equal 2 points (1 for A4 and 1
for D4 & E4 combined)

In other words, any single occurance gets one point, whereas any consecutive
multiple occurance ALSO gets one point. Does that help to clarify ?


"Rick Rothstein" wrote:

Clarification please... your subject line as well as your request indicates
that multiple cells can contain the text you are looking for... ignoring the
text "after" them, if C4 and B5 both contained this text, which of them
would be considered the "first occurance" as your subject line indicates you
are looking for? That is, are you looking row by row or column by column to
find the first occurance?

As for the "the next two cells, going across and then down" part of your
question, did you mean to look in the 3 cells that Ron's answer is based on,
or were you describing a 2x2 grid of cells?

--
Rick (MVP - Excel)


"Eric_in_EVV" wrote in message
...
I have a worksheet that is 7 columns by 5 rows...in any one of those cells
the user can input a certain text value, say "X". I need to find which
cell
that value is in and then test the next two cells, going across and then
down
to see if the same value exists in order to assign points based on a
predefined points system. Any ideas on how to do that ?

find first occurance of certain value in grid
 

One more thing I failed to mention...the grid is essentially a calendar.
This spreadsheet is going to be used for attendance tracking...to log
absences, where a multi-day absence with a single cause is considered to be
the same as a single day absence.

"Rick Rothstein" wrote:

Clarification please... your subject line as well as your request indicates
that multiple cells can contain the text you are looking for... ignoring the
text "after" them, if C4 and B5 both contained this text, which of them
would be considered the "first occurance" as your subject line indicates you
are looking for? That is, are you looking row by row or column by column to
find the first occurance?

As for the "the next two cells, going across and then down" part of your
question, did you mean to look in the 3 cells that Ron's answer is based on,
or were you describing a 2x2 grid of cells?

--
Rick (MVP - Excel)


"Eric_in_EVV" wrote in message
...
I have a worksheet that is 7 columns by 5 rows...in any one of those cells
the user can input a certain text value, say "X". I need to find which
cell
that value is in and then test the next two cells, going across and then
down
to see if the same value exists in order to assign points based on a
predefined points system. Any ideas on how to do that ?

find first occurance of certain value in grid
 

Try...

F1, confirmed with CONTROL+SHIFT+ENTER, and copied down:

=SUM(IF(FREQUENCY(IF(A1:E1="x",COLUMN(A1:E1)),IF(A 1:E1<"x",COLUMN(A1:E1)
)),1))

--
Domenic
Microsoft Excel MVP
www.xl-central.com
Your Quick Reference to Excel Solutions

In article ,
Eric_in_EVV wrote:

Let me try to clarify with by way of an example spreadsheet:

A B C D E
1 x would equal 1 point
2 x x would equal 2 points
3 x x x would equal 1 point
4 x x x would equal 2 points (1 for A4 and 1
for D4 & E4 combined)

In other words, any single occurance gets one point, whereas any consecutive
multiple occurance ALSO gets one point. Does that help to clarify ?


"Rick Rothstein" wrote:

Clarification please... your subject line as well as your request indicates
that multiple cells can contain the text you are looking for... ignoring
the
text "after" them, if C4 and B5 both contained this text, which of them
would be considered the "first occurance" as your subject line indicates
you
are looking for? That is, are you looking row by row or column by column to
find the first occurance?

As for the "the next two cells, going across and then down" part of your
question, did you mean to look in the 3 cells that Ron's answer is based
on,
or were you describing a 2x2 grid of cells?

--
Rick (MVP - Excel)


"Eric_in_EVV" wrote in message
...
I have a worksheet that is 7 columns by 5 rows...in any one of those cells
the user can input a certain text value, say "X". I need to find which
cell
that value is in and then test the next two cells, going across and then
down
to see if the same value exists in order to assign points based on a
predefined points system. Any ideas on how to do that ?

find first occurance of certain value in grid
 

This should work...

=1+SUMPRODUCT(--(MID(TRIM(IF(A1=""," ","X")&IF(B1=""," ","X")&IF(
C1=""," ","X")&IF(D1=""," ","X")&IF(E1=""," ","X")),ROW($1:$5),1)=" "))

--
Rick (MVP - Excel)


"Eric_in_EVV" wrote in message
...
Let me try to clarify with by way of an example spreadsheet:

A B C D E
1 x would equal 1 point
2 x x would equal 2 points
3 x x x would equal 1 point
4 x x x would equal 2 points (1 for A4 and
1
for D4 & E4 combined)

In other words, any single occurance gets one point, whereas any
consecutive
multiple occurance ALSO gets one point. Does that help to clarify ?


"Rick Rothstein" wrote:

Clarification please... your subject line as well as your request
indicates
that multiple cells can contain the text you are looking for... ignoring
the
text "after" them, if C4 and B5 both contained this text, which of them
would be considered the "first occurance" as your subject line indicates
you
are looking for? That is, are you looking row by row or column by column
to
find the first occurance?

As for the "the next two cells, going across and then down" part of your
question, did you mean to look in the 3 cells that Ron's answer is based
on,
or were you describing a 2x2 grid of cells?

--
Rick (MVP - Excel)


"Eric_in_EVV" wrote in message
...
I have a worksheet that is 7 columns by 5 rows...in any one of those
cells
the user can input a certain text value, say "X". I need to find which
cell
that value is in and then test the next two cells, going across and
then
down
to see if the same value exists in order to assign points based on a
predefined points system. Any ideas on how to do that ?