+ Reply to Thread
Results 1 to 4 of 4

Count Cells that have more than 1 unique combination

  1. #1
    Forum Contributor
    Join Date
    05-07-2007
    Location
    Singapore
    MS-Off Ver
    2006/2016
    Posts
    174

    Count Cells that have more than 1 unique combination

    Hi All

    I have a simple spreadsheet with 2 columns

    What i want do is to somehow flag out rows where the concatenate of column A and B (column C) has 2 or more unique values

    As you can see from my example

    Item A is disregarded since there is only 1 possible such combination
    Item B is flagged because it has more than 1

    How do I do this? I'd prefer not to use any advanced autofilter
    Attached Images Attached Images
    Last edited by lordfa9; 02-14-2019 at 01:01 AM.

  2. #2
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,222

    Re: Count Cells that have more than 1 unique combination

    Please try at D1 Press Ctrl+Shift+Enter and drag down

    =SUM(--(FREQUENCY(IF($A$1:$A$90=A1,MATCH($B$1:$B$90,$B$1:$B$90,)),ROW($B$1:$B$90))>0))>1

  3. #3
    Forum Contributor
    Join Date
    05-07-2007
    Location
    Singapore
    MS-Off Ver
    2006/2016
    Posts
    174

    Re: Count Cells that have more than 1 unique combination

    Quote Originally Posted by Bo_Ry View Post
    Please try at D1 Press Ctrl+Shift+Enter and drag down

    =SUM(--(FREQUENCY(IF($A$1:$A$90=A1,MATCH($B$1:$B$90,$B$1:$B$90,)),ROW($B$1:$B$90))>0))>1
    doesnt work when I try to expand it down

    Is it something to do with arrays being 255 rows max?

  4. #4
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,222

    Re: Count Cells that have more than 1 unique combination

    Please attached your sheet, Go Advanced > Manage Attachments

+ 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. Replies: 3
    Last Post: 05-26-2016, 02:11 AM
  2. (Count Formula) Count if two seperate ranges create unique combination
    By Geekcrux in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-10-2016, 07:00 AM
  3. [SOLVED] Formula or VBA or Combination to count Number of Cells in a merged range! PLEASE HELP!
    By WaylettChris in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 11-13-2014, 11:02 AM
  4. Question: Count Unique/Duplicated 5 number Combination
    By david gonzalez in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-01-2013, 11:43 PM
  5. [SOLVED] changing a current mod (count unique visible cells ->count unique visible cells criteria
    By liranbo in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 04-30-2012, 03:58 AM
  6. How to add a unique ID by a combination of a count product selection
    By v2jtb in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 03-29-2012, 05:20 PM
  7. How assing unique number in a column based on a combination of two cells
    By galin in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-01-2006, 01:30 PM

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