+ Reply to Thread
Results 1 to 3 of 3

Trying to filter out duplicates when counting

  1. #1
    Registered User
    Join Date
    04-25-2017
    Location
    NYC
    MS-Off Ver
    2013
    Posts
    14

    Trying to filter out duplicates when counting

    Hi Everyone, I'm trying to make a fairly straightforward game. I plan to separate a bunch of people into 8 different groups. I will give each group 5 minutes to write down a list of things they need to bring with them on an upcoming trip. After the 5 minutes are up, I will ask them to read out loud what they wrote down for their packing list. Whoever has the highest number of unique items wins.

    As they read them out loud, I will be typing them into the spreadsheet I've attached to this post. Here's where it gets tricky - I don't know how to have each group's "unique total" number equal the amount of unique items in their packing list. I need it to compare what I've typed into a "Packing List" column against all of the other columns to check for the same item in other columns. If it's in 1 other column, the count would be "2" indicating 2 groups mentioned it. If it's in 2 other columns, the count would be 3 indicating 2 other groups mentioned it.

    - For the first group, the count for each item would be 1 (because no other group mentioned that item... yet) and the unique total would simply be the total of all the 1's in that group's "Count" column.
    - For the second group, the count for each item would be 1 unless another group already listed that same item. If another group did list that same item, the count would be 2 (indicating another group mentioned it) and the cells would ideally be highlighted in red to indicate that it was a mentioned more than once.
    - The same logic would follow for each of the additional groups.

    I've manually entered numbers in a couple of the "Count" columns and changed some of them to red as an example to illustrate what I'm trying to automate.

    If anyone could help me enter the appropriate formulas into my spreadsheet, I would sincerely appreciate it!
    Attached Files Attached Files

  2. #2
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Trying to filter out duplicates when counting

    In B1: =COUNTIF(B3:B1000,1)

    Copy that formula across row1 into the COUNT cells.

    In B3: =COUNTIF($A:$A,A3)+COUNTIF($C:$C,A3)+COUNTIF($E:$E,A3)+COUNTIF($G:$G,A3)+COUNTIF($I:$I,A3)+COUNTIF($K:$K,A3)+COUNTIF($M:$M,A3)+COUNTIF($O:$O,A3)

    Copy that formula down as far as desired. Then copy that group of cells across and paste into the count columns as needed. The formula will update itself for each pasted column.

    Lastly, you will have to create individual Conditional Formatting rules for each pair of columns. For instance, highlight A3:B100 and create a coloring rule using the formula =$B3>1
    Then highlight C3:D100 and use the formula =$D3>1
    Repeat ad nauseum.
    Last edited by JBeaucaire; 05-31-2017 at 05:42 PM.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  3. #3
    Registered User
    Join Date
    04-25-2017
    Location
    NYC
    MS-Off Ver
    2013
    Posts
    14

    Re: Trying to filter out duplicates when counting

    Thanks so much! This is exactly what I needed.

    I've marked the thread as solved and attached the final and working version for anyone else who many need this.
    Attached Files Attached Files

+ 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] Counting duplicates only
    By Sekars in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 07-20-2016, 01:46 AM
  2. Using filter to highlight/filter duplicates in multiple columns but within 1 day
    By DaveBre in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-25-2014, 04:07 AM
  3. [SOLVED] Counting Matching values in two separate ranges without counting duplicates
    By Rhall6310 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-01-2014, 03:35 PM
  4. Replies: 5
    Last Post: 12-19-2013, 06:58 AM
  5. [SOLVED] Filter - Hiding duplicates but displaying 1 of the duplicates item
    By blacky1 in forum Excel General
    Replies: 3
    Last Post: 04-19-2012, 07:43 AM
  6. counting cells with data without counting duplicates
    By labettis in forum Excel General
    Replies: 2
    Last Post: 11-05-2007, 12:10 PM
  7. Counting Duplicates
    By kteicher in forum Excel General
    Replies: 4
    Last Post: 03-23-2005, 05:44 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