Find Formulas

  • Thread starter Thread starter Gary Thomson
  • Start date Start date
G

Gary Thomson

I have a set of cells which can have several characters in
each cell

I.e.

A B C D E F G H
1 1-Feb 2-Feb 3-Feb 4-Feb 5-Feb 6-Feb 7-Feb 8-Feb
2 a b bc bd be bcd bd d

Columns J through Q look as follows:

J K L M N O P Q
1 1-Feb 2-Feb 3-Feb 4-Feb 5-Feb 6-Feb 7-Feb 8-Feb
2

Thus column M will consider all data in the range A1:D1
(up to the 4th February).

Now, in cells J2:Q2, a "1" will be displayed if:

a letter has appeared in greater than or equal to 6
consecutive cells at the corresponding point in the range
A2:H2,

otherwise a "0" will be displayed.



i.e. in cell J2 (which corresponds to cell A2), since "a"
has only appeared in cell A2 and no previous, which is
less than 6 consecutive cells, diplay "0".

in cell K2 (which corresponds to cell B2), since "b" has
only appeared in cell B2 and not in cell A2, which is
less than 6 consecutive cells, display "0".

in cell L2 (which corresponds to cell C2), since "b" has
appeared in 2 consecutive cells ("c" has appeared in 1
consecutive cell), which is less than 6 consecutive
cells, display "0".

in cell M2 (which corresponds to cell D2), since "b" has
appeared in 3 consecutive cells ("d" has appeared in 1
consecutive cell), which is less than 6 consecutive cells,
display "0".

..
..
..

in cell P2 (which corresponds to cell G2), since "b" has
appeared in 6 consecutive cells ("d" has appeared in 2
consecutive cells), which is greater than or equal to 6
consecutive cells, display "1".

Do I use a FIND formula for this?
 
Perhaps this example is easier to understand:

I have the following strings in the following cells:

A B C D E F G H
1 1-Feb 2-Feb 3-Feb 4-Feb 5-Feb 6-Feb 7-Feb 8-Feb
2 a b bc bd be bcd bd d


For each day, I want to know how many consecutive days one
of the letters has appeared in that cell (where two
letters appear, we take the maximum duration of the two).

The answers would be displayed in the range J2:Q2 as
follows:

A B C D E F G H
1 1-Feb 2-Feb 3-Feb 4-Feb 5-Feb 6-Feb 7-Feb 8-Feb
2 1 1 2 3 4 5 6 3

Since:

On the 1st feb, "a" has appeared in 1 consecutive cell

On the 2nd Feb, "b" has appeared in one consecutive cell

On the 3rd feb, "b" has appeared in 2 consecutive
cells, "c" has appeared in 1 consecutive cell, so we take
the maximum

and so on.....
 
Back
Top