Hi All,

I am using a conditional Formula to meet various criteria:

The formula does return the correct and expected results. However, I would
like the row positioning of the results changed. At the moment, the TRUE
result 0 is returned against the first instance of matched pattern "B"; I
would like the TRUE result 0 returned to the row of the second instance of
matched pattern "B". The FALSE result is the value in the row above +1
(sequential count until criteria is met).

Sample of data (text letters will vary):
B
##
A
B
##

Expected Results
B
##
A
B Would like 0 (zero) here. At the moment it returns to the first instance
of "B".
##

Thanks
Sam

--
Message posted via http://www.officekb.com

Hi Sam,

Assuming your data are in column A1:A10, then the following formula in, say, B1 & copied down will cause your main formula's result
to appear against the first occurrence only:
=IF(COUNTIF($A$1:A1,A1)1,0,"Rest of your formula")
and the following formula will cause your main formula's result to appear against the last occurrence only:
=IF(COUNTIF($A$1:A1,A1)<COUNTIF(A$1:A$10,A1),0,"R est of your formula")

Cheers
--
macropod
[MVP - Microsoft Word]
-------------------------

"Sam via OfficeKB.com" <u4102@uwe wrote in message news:77eb624dc18aa@uwe...
Hi All,

I am using a conditional Formula to meet various criteria:

The formula does return the correct and expected results. However, I would
like the row positioning of the results changed. At the moment, the TRUE
result 0 is returned against the first instance of matched pattern "B"; I
would like the TRUE result 0 returned to the row of the second instance of
matched pattern "B". The FALSE result is the value in the row above +1
(sequential count until criteria is met).

Sample of data (text letters will vary):
B
##
A
B
##

Expected Results
B
##
A
B Would like 0 (zero) here. At the moment it returns to the first instance
of "B".
##

Thanks
Sam

--
Message posted via http://www.officekb.com

Hi macropod,

Thank you for reply and assistance. Apologies for delay in returning to Post.
My data range is dynamic.

Further help appreciated.

macropod wrote:
Hi Sam,

Assuming your data are in column A1:A10, then the following formula in, say, B1 & copied down will cause your main formula's result
to appear against the first occurrence only:
=IF(COUNTIF($A$1:A1,A1)1,0,"Rest of your formula")

and the following formula will cause your main formula's result to appear against the last occurrence only:
=IF(COUNTIF($A$1:A1,A1)<COUNTIF(A$1:A$10,A1),0," Rest of your formula")
Can the above formula be modified to use a dynamic range, rather than fixed?

Cheers,
Sam

--
Message posted via http://www.officekb.com

hi Sam,

The first one is dynamic as is. For the second one, you could use:
=IF(COUNTIF($A$1:A1,A1)<COUNTIF(A$:A$,A1),0,"Rest of your formula")
to test the whole of column A. Not strictly dynamic, but it works much the same.

Cheers
--
macropod
[MVP - Microsoft Word]
-------------------------

"Sam via OfficeKB.com" <u4102@uwe wrote in message news:780333be1325a@uwe...
Hi macropod,

Thank you for reply and assistance. Apologies for delay in returning to Post.
My data range is dynamic.

Further help appreciated.

macropod wrote:
Hi Sam,

Assuming your data are in column A1:A10, then the following formula in, say, B1 & copied down will cause your main formula's
result
to appear against the first occurrence only:
=IF(COUNTIF($A$1:A1,A1)1,0,"Rest of your formula")

and the following formula will cause your main formula's result to appear against the last occurrence only:
=IF(COUNTIF($A$1:A1,A1)<COUNTIF(A$1:A$10,A1),0, "Rest of your formula")
Can the above formula be modified to use a dynamic range, rather than fixed?

Cheers,
Sam

--
Message posted via http://www.officekb.com