Identify Duplicates and Non-Duplicates within an Array (Match)

Hi,

I am trying to identify a specific non-duplicate value in a list with a variable range within the list. What I need the formula to do is look for "Y", until it finds a "N", then identify the duplicate(s) "Y" in that identified range. Following a "N", when another "Y" has been identified, I need the formula to again look for duplicates in then next range.

I tried the array formula below, but it counts the number of "Y" and I need the very first "Y" in each "range within a range" to be identified as the first "Y" (or identified as a non-duplicate).

=IF(COUNTIF(A1:A30,"N"),COUNTIF(A1:INDEX(A1:A30,MATCH(TRUE,A1:A30="N",0)),"Y"),COUNTIF(A1:A30,"Y"))

In the example below, I would like the first "Y" to be identified as a non-duplicate, the next two "Y" can be either counted or can show as a single number (ex. 1 = duplicate within the range, 0 = non-duplicate). "N" does not necessarily need to be distinguished from "Y", as I believe I can use an if(and statement to separate the "Y" and "N" in another column.

Y 0
Y 1
Y 2
N -
Y 0
N -

Thank you in advance,

Jared

Jared,

Not entirely sure this is what you want, but if I follow you correctly, you can identify the first instance of "Y", and any "N" values in Column A with:

Please Login or Register  to view this content.

in C3, copied down (assuming columns in your post are A & B; just enter "=A2" in C2)

Then in D2, copied down:

Please Login or Register  to view this content.

will identify the "duplicate values" within each range from Column B.

Is this along the lines of what you had in mind?

Jared,

If you want to upload a sample workbook containing an accurate sample of the data you're dealing with, I'll have another look.

Jared,

Please see attached. I almost lost the will to live, trying to solve this - Excel doesn't generally play nice with gaps in data, or perhaps: it's better to avoid gaps in data where possible, is a better way to say that. However, just as I was about to give up, I had another look at my original post to you, and had a eureka moment. My solution requires the use of 2 helper columns, and a third to return the results you're looking for - provided you can live with that, you're good to go.

Hope this helps.

Jared,

Glad I could help, and thanks for the rep.

Could you please use the thread tools to mark this thread as "Solved", provided of course that you're happy it is?

Thanks,