# countif to return "x" for unique & "xx" for duplicate values in a column

1. ## countif to return "x" for unique & "xx" for duplicate values in a column

I am looking for a solution to my countif formula that is marking duplicates values in a column with an 'xx" and unique values with a "x"

But it is working subjectively as it wants to and not consistently.

Example in below screenshot

Column H is the SUMIFS off multiple criteria- ok that is working fine.

``Please Login or Register  to view this content.``
Then I want return an "x" or "xx" in column (I) based off the SUMIFS results in column (H)

so I use this formula to get the "x" value
``Please Login or Register  to view this content.``
COUNTIF TO GET DUPLICATES IN A COLUMN.PNG

But as you can see, the formula is not putting an "xx" to all the duplicates in column (H)

You can see (J75677) is duplicated 7 times in column (H). In column (I), I only want that count if formula above to return the first unique value with a "x" or the duplicate with "xx"

2. ## Re: countif to return "x" for unique & "xx" for duplicate values in a column

Hi, Shruder!

Try this formula:
[I2] : =IF(COUNTIF(H\$2:H2,H2)>1,"xx","x")

And drag it down. Blessings!

3. ## Re: countif to return "x" for unique & "xx" for duplicate values in a column

``Please Login or Register  to view this content.``
MATCH will return the row of the first position that contains the value, and then compare it to the current ROW.
If it's the same, it's the first occurrence, and returns "x".
If it's different, then it's a duplicate, and returns "xx".

There might be other error-cases to handle.

@johnmpl, the "marching COUNTIF" appears to have worse performance than the "MATCH=ROW" construction (this is anecdotal only, we don't really know why). Usually it doesn't really matter, but since there are appear to be over 100,000 rows of data, it could very well be noticeable.

@Shruder, you may want to check performance.

And if possible, use range references like H1:H500000 instead of H:H; forcing the function to assess the entire column will require significantly more calculations, and it actually matters more when you have tens or hundreds of thousands of rows of data, because you're performing relatively large calculations so many times.

4. ## Re: countif to return "x" for unique & "xx" for duplicate values in a column

ben_hensel... you're right about performance. Is better MATCH than COUNTIF.

Maybe this new option could be faster:

1. Select all range I2:I104730 (assume 104730 your last row)
2. Apply this formula in active cell I2, and press Ctrl + Shift + Enter instead of Enter:
=IF(FREQUENCY(H2:H104730,H2:H104730),"x","xx")

Blessings!

5. ## Re: countif to return "x" for unique & "xx" for duplicate values in a column

both work like a charm
thanks all !

There are currently 1 users browsing this thread. (0 members and 1 guests)