unable to return duplicate value

Hi expert,
how to create a formula based on below situation:

Please Login or Register  to view this content.

i would like to return E6 =1 and E7 = 2..
the logic above is like this...
1) if B only appear once (no duplicate), return E=1
2) if B appear more than once ( duplicate) , but C = value (ex: -10), return E =1, else E=2.
3)if B appear more than once ( duplicate) and C = no value (ex: NA), return the first as 1, second as 2.


from the formula in column E, logic 1 and 2 is ok.. but.. for logic 3.. is not OK.. i want to return E7 ( red font to 1), but it return 2.

please advice..

I'd first suggest inserting a blank row 1 to simplify calcs...

Then assuming value is now in B2 rather than B1

Please Login or Register  to view this content.

this would return

Please Login or Register  to view this content.

I believe the first two NA references should both be returning 1 as both are first instances of that no.

If necessary post back with a sample file.

Hi,
i had attached the exmaple..
i would like to return the red cells into 1..
if cell A appear only one, B= 1, if cell A appear more than 1 and cell C = value ( ex: -123), return B =1, else = 2, if cell A appear more than 1 and cell C= NA, return B = 1 ( ex: red cell) and else = 2

Using the sample file:

Please Login or Register  to view this content.

However, I don't understand why B3 should be 2 - it should be 1 surely given it too is the first #N/A for that OPN

Your requirements appear contradictory:

Originally Posted by celticpucca post#1

if B appear more than once ( duplicate) and C = no value (ex: NA), return the first as 1, second as 2.

Originally Posted by celticpucca post#5

B3 should be 2 because it same as B2 but C3 = NA.

Per your first quote (requirement) given B3 is the first "no value" for duplicate of "OPN" value in A3 the resulting value should be 1 not 2.

For your 2nd quote to hold true and all other results to persist the implication would be that:

Where OPN is duplicate and has >1 "no value" the first "no value" should be 1 else 2.

If that's the case please affirm as such.

Are the OPN items always listed in sequence ?

Will an OPN ever appear more than twice ?

Assuming answers to above are Yes & No respectively:

Please Login or Register  to view this content.

Originally Posted by celticpucca

sometimes it appear 3 - 5 times.. but only one of it have CMD value....

Is the CMD value always listed first ? (where exists), eg:

Please Login or Register  to view this content.

or can you have

Please Login or Register  to view this content.

Where you have > 2 OPN for which no CMD values exist what is the numbering sequence - consider:

Please Login or Register  to view this content.

should the resulting output be

Please Login or Register  to view this content.

or

Please Login or Register  to view this content.

or something else altogether ?

Please provide sample file as necessary.

Given XL version etc and the facts that

1. the CMD value does not necessarily appear first
   
2. the number of OPN instances is variable
   
3. some OPN values are stored as numbers whereas others are stored as text
   (ie not really sorted A-Z)

I'd suggest first adding a "key":

Please Login or Register  to view this content.

Then:

Please Login or Register  to view this content.

In the above the COUNTIF will only be calculated in those instances where the first instance of a given OPN has an #N/A CMD value