Count Unique Values from 3 Worksheets - HELP PLEASE!

mryexcel4

New Member
Joined
Sep 3, 2014
Messages
6
Hello,

I have 3 worksheets (Sheet 1, Sheet 2, Sheet 3) which contain email addresses in column J.
Each worksheet has a separate group/bucket of data however some of the email addresses in each sheet may be the same

I am looking for a formula to count only unique values (emails) from Sheet 1 column J which are not in Sheet 2 column J and are not in Sheet 3 column J.
For example, if johndoe@ is in Sheet 1 column J multiple times as well as in Sheet 2 and Sheet 3 column J multiple times. I would like to receive a count of 1.

Any suggestions would be highly appreciated.

Thanks in advance!
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
Hi.

It's not clear what you mean, unfortunately.

You say you want to count "only unique values (emails) from Sheet 1 column J which are not in Sheet 2 column J and are not in Sheet 3 column J".

But does that mean, for example, that an entry in Sheet 2 which does not occur in Sheet 1 or Sheet 3 is not to form part of the count, even though it is unique (as in it only occurs once across all 3 sheets)?

Perhaps you could post a small dataset with your expected results to clarify.

Regards
 
Upvote 0
Sorry for being unclear.

What I really need to accomplish is the following:
1. If an email is in more than one sheet only count the unique emails that are in one sheet and not the other 2. I would also like to use a condition if a record has a value of 1 for example in a cell, select only the unique emails that meet the condition.

Overall I need three formulas which each factor in the type of condition described above:
1. Select unique emails that are in Sheet 1 and not the other 2
2. Select unique emails that are in Sheet 2 and not the other 2
3. Select unique emails that are in Sheet 3 and not the other 2

Hope this helps.



QUOTE=XOR LX;3926902]Hi.

It's not clear what you mean, unfortunately.

You say you want to count "only unique values (emails) from Sheet 1 column J which are not in Sheet 2 column J and are not in Sheet 3 column J".

But does that mean, for example, that an entry in Sheet 2 which does not occur in Sheet 1 or Sheet 3 is not to form part of the count, even though it is unique (as in it only occurs once across all 3 sheets)?

Perhaps you could post a small dataset with your expected results to clarify.

Regards
[/QUOTE]
 
Upvote 0
Ok, but exactly how many counts are we talking about? 1? 3?

And what do these three new conditions that you've laid out mean? What does "select" here mean?

I think that you really should consider posting a small dataset to help clarify things. You could have three column headers - Sheet1, Sheet2, Sheet 3 - with some values underneath each (representing the column J values), together with your expected results.

Regards
 
Upvote 0
Sheet 1 EmailSheet 1 ConditionSheet 2 EmailSheet 2 ConditionSheet 3 EmailSheet 3 Condition
email11email11email21
email11email41
email21email41
email11
email31

<colgroup><col><col><col><col><col><col></colgroup><tbody>
</tbody>
Sheet 1 count result after counting only unique values in sheet 1 that are not in the other 2 sheets with a condition value of 1 = 1 (email2)
Sheet 2 count result after counting only unique values in sheet 2 that are not in the other 2 sheets with a condition value of 1 = 0
Sheet 3 count result after counting only unique values in sheet 3 that are not in the other 2 sheets with a condition value of 1 = 1 (email4)

I have three columns which I need to enter the count results into and that is why I said I need 3 formulas (or one formula which I can edit for the other sheets). One for Sheet 1, another for Sheet 2, and a third for Sheet 3 results.
 
Upvote 0
My mistake. For "Sheet 1 count result after counting only unique values in sheet 1 that are not in the other 2 sheets with a condition value of 1 = 1 (email2)." The result would actually be email 3
 
Upvote 0
Forgive me but this is becoming, if anything, less clear, rather than more.

In the data you've posted, every entry has a Condition of 1, so why is this a factor? If it is in fact possible to have a Condition other than 1, wouldn't it have made sense to include some examples where that was the case?

Because of this, your statement "after counting only unique values in sheet 1 that are not in the other 2 sheets with a condition value of 1" is left ambiguous. To the value in which sheet(s) is the "condition value of 1" bit applying here?

What would your result be for Sheet 1 if the one entry of email3 had a Condition different from 1?

Also, where are your results to be stored? In a fourth sheet? Or do the results for Sheet 1 go in Sheet 1, the results for Sheet 2 in Sheet 2, etc.?

Regards
 
Upvote 0
Consider 1 to be 2014 and 2 to be 2013.

Regarding your question "What would your result be for Sheet 1 if the one entry of email3 had a Condition different from 1?"...
If email3 had a "condition" of 2 or 2013 then the result would be 0 if the formula condition required the "Sheet 1 Condition" column as well as the "Sheet 2 Condition" and "Sheet 3 Condition" columns to have a 1 for the value.
If the formula condition required the value to be 2 then the result would be 1.

The results will go into a separate worksheet into columns containing one cell each. One cell in one column for Sheet 1 count, another cell in the second column for Sheet 2 count, and another cell in the third column for Sheet 3 count.


Forgive me but this is becoming, if anything, less clear, rather than more.

In the data you've posted, every entry has a Condition of 1, so why is this a factor? If it is in fact possible to have a Condition other than 1, wouldn't it have made sense to include some examples where that was the case?

Because of this, your statement "after counting only unique values in sheet 1 that are not in the other 2 sheets with a condition value of 1" is left ambiguous. To the value in which sheet(s) is the "condition value of 1" bit applying here?

What would your result be for Sheet 1 if the one entry of email3 had a Condition different from 1?

Also, where are your results to be stored? In a fourth sheet? Or do the results for Sheet 1 go in Sheet 1, the results for Sheet 2 in Sheet 2, etc.?

Regards
 
Upvote 0
Apologies. It's not often that I'm completely bamboozled by a thread, but, 8 posts in, I still have absolutely no idea what you're asking for. Each post that you make seems to either feature some new, previously-unmentioned information or else contradict something you've said in an earlier post.

I hope that someone else will pick up on this thread shortly and that they will better understand your query.

Regards
 
Upvote 0
Okay.

What I did was ran three queries in sql server to obtain data. One query was to pull records including emails that were received 0-30 days after a certain date say 1/1/2014. The second query pulled results for 31-60 days and the third query pulled emails from 61-90 days. Each query results were then put into a separate Excel sheet. I want to count unique emails for sheet 1 that are not in sheet 2 or 3. Then do the same for sheet 2 and sheet 3. Then have the results entered into the separate cells and columns as I described earlier.

QUOTE=XOR LX;3927018]Apologies. It's not often that I'm completely bamboozled by a thread, but, 8 posts in, I still have absolutely no idea what you're asking for. Each post that you make seems to either feature some new, previously-unmentioned information or else contradict something you've said in an earlier post.

I hope that someone else will pick up on this thread shortly and that they will better understand your query.

Regards[/QUOTE]
 
Upvote 0

Forum statistics

Threads
1,213,544
Messages
6,114,239
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top