Need to Count Number of Unique Text Values as Long as Date is Same in Column

**smp4502004** · 07-03-2012, 09:45 AM

So I have a column filled with email addresses, and I need to count the number of unique values in this column, as long as the date hasn't changed in another column. Once the date changes, then I need to reset the count. I have a formula that works for counting the number of unique text values, but I have to hardcode the section of the column of email addresses to check. This is not homework, so don't say I haven't tried anything, this is for my job, just don't haven't used Excel too much.

Any help would be greatly appreciated.

**Ron Coderre** · 07-03-2012, 10:15 AM

With these values in A1:B16

Please Login or Register  to view this content.

This ARRAY FORMULA, completed by pressing CTRL+SHIFT+ENTER (instead of just ENTER) begins the list of unique item counts per day

Please Login or Register  to view this content.

Copy C2 and paste into C3:C16

These are the results:

Please Login or Register  to view this content.

Is that something you can work with?

**Ron Coderre** · 07-03-2012, 10:26 AM

ALSO...Welcome to the forum.
This is a moderated forum so please take a few minutes to read the rules (Click the Forum Rules link, above, to see them).

I noticed that you posted this same question at another forum. We strongly prefer that you don't do that.
Many Excel experts frequent the more popular forums, so you gain very little by multi-posting.
However, if you DO, we insist that you include a link to the other forum(s) here so we can track the other activity and stop trying to help you if you receive an acceptable solution elsewhere.

Since you're new here, I'll post the link for you...this time.
http://www.mrexcel.com/forum/showthr...=1#post3197746

**smp4502004** · 07-03-2012, 10:41 AM

I did the exact example you did, and in the count column, I got the 5 to output, but not the 3. Did you only put that formula in C2, and it put both the 5 and the 3? This is exactly what I need if I can get it to go down all my information without me hardcoding something for each individual date.

**smp4502004** · 07-03-2012, 10:51 AM

Is there a way to make the formula not count blank spaces as a unique entry?

**Ron Coderre** · 07-03-2012, 11:08 AM

Originally Posted by smp4502004

I did the exact example you did, and in the count column, I got the 5 to output, but not the 3. Did you only put that formula in C2, and it put both the 5 and the 3? This is exactly what I need if I can get it to go down all my information without me hardcoding something for each individual date.

Did you follow these steps?
• Enter the formula in C2...AND...while still in edit mode
• Hold CTRL and SHIFT when you press ENTER
Then
• Copy C2
• Paste into C3:C16

See my attached workbook.

Does that help?

**smp4502004** · 07-03-2012, 11:10 AM

Yeah I got it to work, that was my fault. I am now wondering if the formula can be made to not count blank spaces as a unique entry.

Ron I appreciate all your help, and I will stop bothering you after this.

**Ron Coderre** · 07-03-2012, 11:40 AM

Originally Posted by smp4502004

Yeah I got it to work, that was my fault. I am now wondering if the formula can be made to not count blank spaces as a unique entry.

Ron I appreciate all your help, and I will stop bothering you after this.

1) You're not bothering me...yet

2) Try this ARRAY FORMULA

Please Login or Register  to view this content.

Copy down, as before

Does that help?

**smp4502004** · 07-03-2012, 11:51 AM

Well it worked fine on the small example you gave me earlier, but when I try to implement it on my large excel file, it gave me the first section for one day, and never output any more.

**Ron Coderre** · 07-03-2012, 01:02 PM

Can you post the formula that's not working, or...even better...a workbook that displays the problem?

**smp4502004** · 07-03-2012, 01:13 PM

Here is the example you gave me earlier, but expanded, it starts outputting zero when it shouldn't. When I tried on the large spreadsheet I am working on, it only output the value for the first date, then never output anything else.

**Ron Coderre** · 07-03-2012, 01:22 PM

Originally Posted by smp4502004

Here is the example you gave me earlier, but expanded, it starts outputting zero when it shouldn't. When I tried on the large spreadsheet I am working on, it only output the value for the first date, then never output anything else.

You need to adjust the referenced ranges to include ALL of your data. They currently only refer to rows 2 through 30

Try this ARRAY FORMULA:

Please Login or Register  to view this content.

Note: The larger the referenced ranges are, the slower the workbook will calculate. If it becomes oppressively slow, we may need to take a different approach.

**smp4502004** · 07-03-2012, 04:50 PM

Yeah I had some different spreadsheets where it would work and others that wouldn't. Once that was about ~1500 entries worked, as did ~5600. The ones that were ~25000 and ~50000 did not.

Need to Count Number of Unique Text Values as Long as Date is Same in Column

LinkBack

Thread Tools

Rate This Thread

Display

Need to Count Number of Unique Text Values as Long as Date is Same in Column

Re: Need to Count Number of Unique Text Values as Long as Date is Same in Column

Re: Need to Count Number of Unique Text Values as Long as Date is Same in Column

Re: Need to Count Number of Unique Text Values as Long as Date is Same in Column

Re: Need to Count Number of Unique Text Values as Long as Date is Same in Column

Re: Need to Count Number of Unique Text Values as Long as Date is Same in Column

Re: Need to Count Number of Unique Text Values as Long as Date is Same in Column

Re: Need to Count Number of Unique Text Values as Long as Date is Same in Column

Re: Need to Count Number of Unique Text Values as Long as Date is Same in Column

Re: Need to Count Number of Unique Text Values as Long as Date is Same in Column

Re: Need to Count Number of Unique Text Values as Long as Date is Same in Column

Re: Need to Count Number of Unique Text Values as Long as Date is Same in Column

Re: Need to Count Number of Unique Text Values as Long as Date is Same in Column

Thread Information

Users Browsing this Thread

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions