+ Reply to Thread
Results 1 to 5 of 5

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

  1. #1
    Forum Contributor
    Join Date
    02-13-2018
    Location
    USA
    MS-Off Ver
    Office 365
    Posts
    173

    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. #2
    Spammer
    Join Date
    01-08-2015
    Location
    Cali, Colombia
    MS-Off Ver
    365
    Posts
    302

    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. #3
    Forum Expert ben_hensel's Avatar
    Join Date
    01-26-2012
    Location
    Northeast USA
    MS-Off Ver
    Office 365
    Posts
    2,043

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

    Use this formula instead:

    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.
    Click the [* Add Reputation] Button to thank people for help!
    Post Attachment: Click [Go Advanced] under the reply box; then click [Manage Attachments] to launch the attachments dialogue.
    Mark Thread Solved: Go to the top of the first post; Select [Thread Tools] dropdown; Pick the [Mark thread as Solved] option.

  4. #4
    Spammer
    Join Date
    01-08-2015
    Location
    Cali, Colombia
    MS-Off Ver
    365
    Posts
    302

    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!
    Last edited by johnmpl; 02-27-2018 at 04:22 PM.

  5. #5
    Forum Contributor
    Join Date
    02-13-2018
    Location
    USA
    MS-Off Ver
    Office 365
    Posts
    173

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

    both work like a charm
    thanks all !

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Column X-Ref list - Sheet1 Col A "pages", Col B:FL "Req" to Sheet2 ColA "req", ColB "page"
    By excel-card-pulled in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 04-07-2017, 09:30 AM
  2. [SOLVED] Prevent Duplicate Receipt Number in Three Column [ e.g. Column "A", "B", "C" ]
    By MrsCharmy in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 03-31-2017, 08:40 AM
  3. Replies: 35
    Last Post: 01-13-2016, 02:16 AM
  4. Replies: 3
    Last Post: 04-14-2013, 11:53 PM
  5. [SOLVED] Formula needed to display "Pass" or "Fail" if a column contains any values other than "yes
    By andreindy in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 03-26-2013, 05:49 PM
  6. Replies: 3
    Last Post: 02-16-2011, 02:55 PM
  7. Replies: 1
    Last Post: 07-16-2010, 02:44 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1