+ Reply to Thread
Results 1 to 9 of 9

Check if Teams pick a name as representative & agree on a name

  1. #1
    Registered User
    Join Date
    09-21-2021
    Location
    France
    MS-Off Ver
    365
    Posts
    1

    Unhappy Check if Teams pick a name as representative & agree on a name

    Hello

    Can anyone help me with a formula or a VBA script ?

    There are 4 columns with many possible choices (Alice,Bob,Clarisse,Denis,Ernest...). The whole idea is to let 4 teams (columns) to pick a name as representative and check if they do agree on a name or not.

    I would like to count the number of name and return a color:
    - For instance, if in a row, there are 4 "Alice" or 3 "Alice", it should return green color cell
    - IF there are only 2 "Alice", it returns orange color cell
    - To finish with, if a row contains less that one "Alice", it will return a red color cell.

    I tried some formulas with "IF" and "CountIF", but cannot apply it to the whole sheet since the names are all different (mostly names).



    The colored cell goes to the corresponding row from the table "Result"

    Thanks in advance
    Attached Files Attached Files
    Last edited by VinceDra; 09-21-2021 at 04:38 AM.

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Check if Teams pick a name as representative & agree on a name

    Administrative Note:

    Welcome to the forum.

    There are instructions at the top of the page explaining how to attach your sample workbook.

    We would very much like to help you with your query, however the thread title does not really convey what your request is about. Tell us what you are trying to do, not how you think it should be done.

    Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).

    Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.

    (Note: this change is not optional. As you are new here I have done it for you today.)
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  3. #3
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Check if Teams pick a name as representative & agree on a name

    You've posted an IMAGE. I asked for a sample WORKBOOK. We cannot manipulate an image. Please try again.

  4. #4
    Forum Expert
    Join Date
    08-17-2007
    Location
    Poland
    Posts
    2,161

    Re: Check if Teams pick a name as representative & agree on a name

    In the attachment there are two simple ways with formulas and conditional formatting.

    Artik
    Attached Files Attached Files

  5. #5
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Check if Teams pick a name as representative & agree on a name

    Sorry for off-topic interjection!

    @Artik


    Although there is no official rule regarding this behaviour, we request that wherever possible both the question AND the answer be provided in substantive detail here within the thread. An attached workbook is an excellent aid for posing a question and offering a solution, but solely doing that with no in thread explanation makes it difficult for researchers to understand or consider the Q & A of this thread without downloading what may be a pointless doc to them, if they can do that at all. Doing that also hides the content from search engines so others may never benefit from this.

    I'm sure you understand, and we look forward to seeing you post your formulas/macros in your posts for the searching benefit of all.

  6. #6
    Forum Expert
    Join Date
    08-17-2007
    Location
    Poland
    Posts
    2,161

    Re: Check if Teams pick a name as representative & agree on a name

    AliGW, in Polish, I would be able to explain everything down to the last detail as best I can. Unfortunately, due to language problems, I don't always have the courage to explain something in English. I use Google Translator all the time, which doesn't always translate my thoughts as expected. Besides, I don't know what level of knowledge the OP is. When he has more, a detailed description seems unnecessary - he only needs to show the direction. In addition, self-investigation to a solution gives more satisfaction and causes that the acquired knowledge stays with us for longer. If the OP has a problem with something, he can always ask a question. Me or someone else will explain it to him then.

    Artik

    Here are the details for the benefit of anyone unable to access the attachment (added by AliGW):

    In K6: =IF(COUNTA(Table24[@])=4,COUNTIF(Table24[@],$K$3),"") - K3 contains the letter (name) required - Artik added a drop-down selection list for this)

    CF Rules:

    Red =AND(ISNUMBER($K6),$K6<2)
    Amber =AND(ISNUMBER($K6),$K6=2)
    Green =AND(ISNUMBER($K6),$K6>=3)

    Alternatively:

    AliGW on MS365 Insider (Windows) 32 bit
    K
    L
    M
    N
    20
    A
    B
    C
    D
    21
    4
    0
    0
    0
    22
    3
    0
    1
    0
    23
    2
    1
    0
    1
    24
    1
    1
    1
    1
    Sheet: Sheet2

    AliGW on MS365 Insider (Windows) 32 bit
    K
    21
    =IF(COUNTA(Table242[@])=4,COUNTIF(Table242[@],K$20),"")
    Sheet: Sheet2
    Last edited by AliGW; 09-21-2021 at 09:46 AM.

  7. #7
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Check if Teams pick a name as representative & agree on a name

    OK - you've managed to translate that quite long message using Google Translate, so try translating the instructions. At the very least, please post FORMULAE and CODE within your post. This you should be able to do easily, but this website might be of help to you: https://en.excel-translator.de/translator/

    There are members who will NOT be able to download and open attachments and who WILL need more detail - we want the thread to be accessible to ALL, which is why we ask for this.

    Instead of spending time explaining in detail why you think you CAN'T or SHOULDN'T do something, just have a go and try doing as you have been asked, please. Thanks

    EDIT:
    I have added the details myself as Artik was unwilling to comply with my request. I hope this will be of help to those looking at this thread for help either now or in the future.
    Last edited by AliGW; 09-21-2021 at 09:48 AM.

  8. #8
    Forum Expert
    Join Date
    08-17-2007
    Location
    Poland
    Posts
    2,161

    Re: Check if Teams pick a name as representative & agree on a name

    Quote Originally Posted by AliGW View Post
    I have added the details myself as Artik was unwilling to comply with my request.
    It would be more appropriate to say that "he could not because he had to go out".

    Artik

  9. #9
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Check if Teams pick a name as representative & agree on a name

    Not according to post #6, where you spent a considerable time explaining why you were not going to provide the extra detail. No mention of being short of time!

    Please make sure that formulae and/or code are shared in your posts in future for the benefit of all. Thank you.

+ 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. Check occurences of how many times a value repeat itself between sheets.
    By robertoleonel in forum Hello..Introduce yourself
    Replies: 1
    Last Post: 01-20-2020, 06:47 AM
  2. How to check if excel file is Checked Out in SharePoing Check Out, run code, Check In
    By jrtraylor in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-08-2016, 04:36 PM
  3. Automatically check one or more check boxes when a parent check box is manually checked
    By Steverizer in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-28-2013, 01:56 PM
  4. Check the number of occurences of a word in a list
    By Askalian in forum Excel General
    Replies: 0
    Last Post: 02-05-2013, 09:19 PM
  5. Sum x most recent occurences
    By mrbreeze in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 03-31-2011, 05:09 AM
  6. Number of occurences
    By n0kx in forum Excel General
    Replies: 2
    Last Post: 02-24-2009, 01:02 PM
  7. Occurences
    By johnshute in forum Excel General
    Replies: 1
    Last Post: 12-14-2008, 12:57 PM

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