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

UDF
Use in cell like

=CountUniq(D6:J75,",")

Please Login or Register  to view this content.

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

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

L8:
=CountUniq(",",D9)

Please Login or Register  to view this content.

There must be a VBA ?

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

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 don’t want to count that two names in lab 2.

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.

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

Originally Posted by jindon

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)

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

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.

sir,
pls find the attachment.

thanks
rao

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.

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

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.

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

thanks
rao

sorry
correction,

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

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

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

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