Return Formula results to specific row of matched criteria

[Sam via OfficeKB.com]

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

 

[macropod]

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")

Cheers

 

[Sam via 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.

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

 

[macropod]

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