+ Reply to Thread
Results 1 to 23 of 23

count unique comma separated names in range of cells

  1. #1
    Registered User
    Join Date
    09-06-2017
    Location
    indai
    MS-Off Ver
    2013
    Posts
    38

    count unique comma separated names in range of cells

    Hi,
    I am new to VBA trying count unique comma separated names from a range of cells,
    I have range D6 to J75, each cell contains names separated by comma and space, and some times repeated for example D6 as Joes, Tony, Samuel, Joes, and E8 as Adrian, Sam, Prakash, Tony, After the macro: It should get count 6 in G8,
    thanks
    rao

  2. #2
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    15,849

    Re: count unique comma separated names in range of cells

    Hi rao,

    If you really want a vba answer, you should attach a sample file so we can use it to built a VBA answer for you.

    Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  3. #3
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    20,051

    Re: count unique comma separated names in range of cells

    UDF
    Use in cell like

    =CountUniq(D6:J75,",")

    Please Login or Register  to view this content.

  4. #4
    Registered User
    Join Date
    09-06-2017
    Location
    indai
    MS-Off Ver
    2013
    Posts
    38

    Re: count unique comma separated names in range of cells

    Hi,
    I have attached sample excel file fro your ref,
    thanks
    rao
    Attached Files Attached Files

  5. #5
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    20,051

    Re: count unique comma separated names in range of cells

    K8:
    =CountUniq(",",D6,D10)

    L8:
    =CountUniq(",",D9)

    Please Login or Register  to view this content.

  6. #6
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    Any Country
    MS-Off Ver
    farerwell
    Posts
    8,749

    Re: count unique comma separated names in range of cells

    There must be a VBA ?
    sandy
    How to create an editor for Power Query with Notepad++ (tutorial)
    How to create timeline project with vertical today marker (2010, 2013, 2016 etc...) (examples)
    Tips for Excellent Spreadsheets

    What makes learning so hard is the amount of knowledge you have to unlearn
    Why is my program not doing what I expect?
    Because you set the wrong expectations. Rewire your brain

  7. #7
    Registered User
    Join Date
    09-06-2017
    Location
    indai
    MS-Off Ver
    2013
    Posts
    38

    Re: count unique comma separated names in range of cells

    Hi,
    Thank you very much,
    sorry for delay
    i am in out of station
    once again thanks a lot
    regards,
    rao

  8. #8
    Registered User
    Join Date
    09-06-2017
    Location
    indai
    MS-Off Ver
    2013
    Posts
    38

    Re: count unique comma separated names in range of cells

    Sir,
    With countunique function, I am getting a unique count in LAB 1 perfect, but for LAB 2, two of lab tech from lab 1 were allocated in lab 2.
    I dont want to count that two names in lab 2.
    Attached Files Attached Files

  9. #9
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    20,051

    Re: count unique comma separated names in range of cells

    K8:
    =CountUniq(Q1:Q2,False,",",D6,D10)
    Where
    1) Q1:Q2 holds Tony and Sam that you want to exclude (Not necessary to be Q1:Q2)
    2) True/False True = Exclude, False = Include
    rest are the same.
    Please Login or Register  to view this content.

  10. #10
    Registered User
    Join Date
    09-06-2017
    Location
    indai
    MS-Off Ver
    2013
    Posts
    38

    Re: count unique comma separated names in range of cells

    Sir,
    sorry to say function does not work, I have attached file in K16 & L16 I used the function in K16 I got an error, L16 no change.
    please have a look.

    thanks
    rao
    Attached Files Attached Files

  11. #11
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    20,051

    Re: count unique comma separated names in range of cells

    Quote Originally Posted by jindon View Post
    K8:
    =CountUniq(Q1:Q2,False,",",D6,D10)
    Where
    1) Q1:Q2 holds Tony and Sam that you want to exclude (Not necessary to be Q1:Q2)
    2) True/False True = Exclude, False = Include
    rest are the same.
    That means
    1)Q1:Q2 should be the list of names that you want to EXCLUDE from the count and it should be consecutive cells.
    2) True/False True for Exclude, False for Include.

    Above 2 arguments are not OPTIONAL.

    Then you have no name list and more name with red font...

    Setting
    Q1 = Tony
    Q2 = Sam
    Q3 = Alfred

    Formula
    K8: =CountUniq(Q1:Q3,FALSE,",",D6,D10)
    L8: =CountUniq(Q1:Q3,FALSE,",",D9)
    K16: =CountUniq(Q1:Q3,TRUE,",",D14,D18)
    L16: =CountUniq(Q1:Q3,TRUE,",",D17)

  12. #12
    Valued Forum Contributor Maudibe's Avatar
    Join Date
    12-21-2016
    Location
    USA
    MS-Off Ver
    2010-2013
    Posts
    326

    Re: count unique comma separated names in range of cells

    Solution removed
    Last edited by Maudibe; 10-07-2017 at 08:20 AM.

  13. #13
    Registered User
    Join Date
    09-06-2017
    Location
    indai
    MS-Off Ver
    2013
    Posts
    38

    Re: count unique comma separated names in range of cells

    sir,
    jindon,
    I try to explain the problem, above lab's are just examples there were more than 5 to 6 lab's and within lab there two dept A & B and one lab asst team, 80+ lab Tech's and 15+lab asst's working in a day and all are allocated in different lab's for time slot. after their time period, they will move to another lab
    for manpower count we need to give a report each lab unique number of lab tech & lab asst are worked, that should match the total count, for each lab, unique count Exclude names who came from another lab,
    In your solution one as to write the names of those lab techs who are routed to other labs,
    we need counting take place automatically,
    Can you modify this function to look above cells match names and exclude them? ie: D6, D10 and exclude the names give result in K16, L16,

    thanks
    rao

  14. #14
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    20,051

    Re: count unique comma separated names in range of cells

    I don't understand what you are talking about.

    Just upload an EXACT workbook with 2 sheets before/after clearly showing the logic behind it.
    Otherwise jut wasting time.

  15. #15
    Registered User
    Join Date
    09-06-2017
    Location
    indai
    MS-Off Ver
    2013
    Posts
    38

    Re: count unique comma separated names in range of cells

    sir,
    pls find the attachment.

    thanks
    rao
    Attached Files Attached Files

  16. #16
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    20,051

    Re: count unique comma separated names in range of cells

    Is that a EXACT sheet layouts?

    Total Lab tech available for the day = 10 Total Lab tech allocated for the day = 18
    Total Lab asst available for the day = 5 Total Lab asst allocated for the day = 9
    Total 15 Total 27
    we are not counting trainees

    Available and Allcoted manpower are not matching

    The above are also in your original file,

    I just want to see EXACT sheet, no explanation.

  17. #17
    Registered User
    Join Date
    09-06-2017
    Location
    indai
    MS-Off Ver
    2013
    Posts
    38

    Re: count unique comma separated names in range of cells

    sir,
    sorry
    I can not upload office file, every day we are counting manually take a lot of time, we want to make automat counting to save time. this file is as similar to the original.

    thanks
    rao

  18. #18
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    20,051

    Re: count unique comma separated names in range of cells

    I'm not saying Official file, I don't care about the data, but need to see EXACT sheet layouts. No extra information.

    I don't want you to change the sheet layouts after I write the code again and again.

  19. #19
    Registered User
    Join Date
    09-06-2017
    Location
    indai
    MS-Off Ver
    2013
    Posts
    38

    Re: count unique comma separated names in range of cells

    Sir,
    I understood, pls find the attachment after the macro file is in original format.

    thanks
    rao
    Attached Files Attached Files

  20. #20
    Registered User
    Join Date
    09-06-2017
    Location
    indai
    MS-Off Ver
    2013
    Posts
    38

    Re: count unique comma separated names in range of cells

    sorry
    correction,
    Attached Files Attached Files

  21. #21
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    20,051

    Re: count unique comma separated names in range of cells

    Now what you are asking is completely different from your original question.

    Close this thread and open a new thread with the latest file.

  22. #22
    Registered User
    Join Date
    09-06-2017
    Location
    indai
    MS-Off Ver
    2013
    Posts
    38

    Re: count unique comma separated names in range of cells

    sir,
    it is all same,
    anyway thank you very much.
    rao

  23. #23
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    20,051

    Re: count unique comma separated names in range of cells

    No, it is different and I don't want to waste my time with you anymore.

+ 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] Find all the cells that equal x and put names in another cell separated by comma
    By MichelleP in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-04-2016, 04:29 AM
  2. [SOLVED] Populate Listbox From a range of cells AND comma separated values
    By Code Flunkie in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 11-10-2015, 11:13 AM
  3. Count occurrences of comma separated text from a range
    By rra1968 in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 09-17-2015, 09:37 AM
  4. [SOLVED] Macro to give the count of unique values after comparing the comma separated values
    By Manish_Gupta in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-03-2014, 12:41 AM
  5. Creating a comma separated list for unique entires
    By kreativsoul in forum Excel General
    Replies: 9
    Last Post: 07-22-2012, 11:42 PM
  6. Convert range of cells to comma separated list
    By maw230 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 01-23-2012, 06:03 PM
  7. Replies: 0
    Last Post: 08-08-2005, 01:05 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