Colimn "Count" formula for unique values in a column

[tbobolz]

Excel 2010 V2

Hi All,

Thanks in advance for any help you can offer.

I am looking for a formula that returns the count of unique values in another column. So if I have a column labeled Account Number, which has multiple occurance values in each row, I wish to count on each row the number of times each item occures in a new column, such as 1,2,3 for Account Number 200 below, but only 1 for account number 400 as shown belown.

[TABLE="width: 307"]
<COLGROUP><COL style="WIDTH: 84pt; mso-width-source: userset; mso-width-alt: 4096" width=112><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 51pt; mso-width-source: userset; mso-width-alt: 2486" width=68><COL style="WIDTH: 124pt; mso-width-source: userset; mso-width-alt: 6034" width=165><TBODY>[TR]
[TD="class: xl65, width: 112, bgcolor: transparent"]Account Number[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"]Case[/TD]
[TD="class: xl65, width: 68, bgcolor: transparent"]Date[/TD]
[TD="class: xl68, width: 165, bgcolor: yellow"]Column Formula Needed[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]200[/TD]
[TD="class: xl66, bgcolor: transparent"]1[/TD]
[TD="class: xl67, bgcolor: transparent"]<st1:date ls="trans" Month="7" Day="21" Year="2014">7/21/2014</st1:date> [/TD]
[TD="class: xl66, bgcolor: transparent"] 1[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]200[/TD]
[TD="class: xl66, bgcolor: transparent"]1[/TD]
[TD="class: xl67, bgcolor: transparent"]<st1:date ls="trans" Month="7" Day="21" Year="2014">7/21/2014</st1:date> [/TD]
[TD="class: xl66, bgcolor: transparent"] 2[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]200[/TD]
[TD="class: xl66, bgcolor: transparent"]1[/TD]
[TD="class: xl67, bgcolor: transparent"]<st1:date ls="trans" Month="7" Day="21" Year="2014">7/21/2014</st1:date> [/TD]
[TD="class: xl66, bgcolor: transparent"] 3[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]300[/TD]
[TD="class: xl66, bgcolor: transparent"]1[/TD]
[TD="class: xl67, bgcolor: transparent"]<st1:date ls="trans" Month="7" Day="2" Year="2014">7/2/2014</st1:date> [/TD]
[TD="class: xl66, bgcolor: transparent"] 1[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]400[/TD]
[TD="class: xl66, bgcolor: transparent"]1[/TD]
[TD="class: xl67, bgcolor: transparent"]<st1:date ls="trans" Month="6" Day="30" Year="2014">6/30/2014</st1:date> [/TD]
[TD="class: xl66, bgcolor: transparent"] 1[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]500[/TD]
[TD="class: xl66, bgcolor: transparent"]1[/TD]
[TD="class: xl67, bgcolor: transparent"]<st1:date ls="trans" Month="5" Day="30" Year="2014">5/30/2014</st1:date> [/TD]
[TD="class: xl66, bgcolor: transparent"] 1[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]500[/TD]
[TD="class: xl66, bgcolor: transparent"]1[/TD]
[TD="class: xl67, bgcolor: transparent"]<st1:date ls="trans" Month="5" Day="30" Year="2014">5/30/2014</st1:date> [/TD]
[TD="class: xl66, bgcolor: transparent"] 2[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]700[/TD]
[TD="class: xl66, bgcolor: transparent"]1[/TD]
[TD="class: xl67, bgcolor: transparent"]<st1:date ls="trans" Month="7" Day="21" Year="2014">7/21/2014</st1:date> [/TD]
[TD="class: xl66, bgcolor: transparent"] 1[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]800[/TD]
[TD="class: xl66, bgcolor: transparent"]1[/TD]
[TD="class: xl67, bgcolor: transparent"]<st1:date ls="trans" Month="7" Day="4" Year="2014">7/4/2014</st1:date> [/TD]
[TD="class: xl66, bgcolor: transparent"] 1[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]900[/TD]
[TD="class: xl66, bgcolor: transparent"]1[/TD]
[TD="class: xl67, bgcolor: transparent"]<st1:date ls="trans" Month="7" Day="3" Year="2014">7/3/2014</st1:date> [/TD]
[TD="class: xl66, bgcolor: transparent"] 1[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]900[/TD]
[TD="class: xl66, bgcolor: transparent"]1[/TD]
[TD="class: xl67, bgcolor: transparent"]<st1:date ls="trans" Month="7" Day="3" Year="2014">7/3/2014</st1:date> [/TD]
[TD="class: xl66, bgcolor: transparent"] 2[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]900[/TD]
[TD="class: xl66, bgcolor: transparent"]1[/TD]
[TD="class: xl67, bgcolor: transparent"]<st1:date ls="trans" Month="7" Day="3" Year="2014">7/3/2014</st1:date> [/TD]
[TD="class: xl66, bgcolor: transparent"] 3[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]900[/TD]
[TD="class: xl66, bgcolor: transparent"]1[/TD]
[TD="class: xl67, bgcolor: transparent"]<st1:date ls="trans" Month="7" Day="3" Year="2014">7/3/2014</st1:date> [/TD]
[TD="class: xl66, bgcolor: transparent"] 4[/TD]
[/TR]
</TBODY>[/TABLE]

 

[tbobolz]

Thought I would let everyone know that this was never solved; however, I did achieve my goal by loading the data twice, once with the multi occuring datae in columns and once with individual data for each account. This allowed me to use the RELATED function to pull the items I needed from one data base to the other.

Thanks

Scott, I would love to still solve this for future use and steamline the data size if you have any further thoughts?

Thanks
Terry

 

[scottsen]

Any chance you can share the workbook (or a version modified to hide any personal info on patients ) ?

 

[tbobolz]

Hi,

Here is the sample file link. There is no worksheet data, just two fileds in the power pivot where I (ha, we) have achieved to return a total count of unique account, yet I am still lookin to return a count of unique account.
Thansk

https://drive.google.com/file/d/0B3g_N9q156QuU1lnX0p4MlU5Wmc/edit?usp=sharing

 

[tbobolz]

I hope that link works, It seems to want me to open it with google doc share when I try, but I am thinking that has to do with my work internet access.

Thanks
Again

 

[tbobolz]

Sorry for all the emails,here's a dropbox link that should work.


https://www.dropbox.com/s/xjo0lnriswfaoza/Sample.xls.xlsm?dl=0</SPAN></SPAN>

 

[scottsen]

Reading back through this... did the requirements change? I believe originally you need Account 200 (which had 3 rows) to return 1,2,3. If it could return just 3,3,3 that is WAY easier.

The latter is just =CALCULATE(COUNTROWS(MyTable), FILTER(MyTable, MyTable[AccountNum] = EARLIER(MyTable[AccountNum]))

But if you need 1,2,3... that is where it gets non-trivial.

 

[scottsen]

For the latter... I could NOT get RANKX to behave, but have an easier way. Kinda.

Add a calculated column called "uniquifier", that is just =RAND()

Then...

=CALCULATE(COUNTROWS(CaseInfo), FILTER(ALL(CaseInfo), CaseInfo[AccountNum] = EARLIER(CaseInfo[AccountNum]) && CaseInfo[Uniquifier] <= EARLIER(CaseInfo[Uniquifier])))

This gives what you want. That said, it is really crappy for performance, since obviously =RAND() does not compress... at all. Far better to massage the data somehow on the way in. Or find some other way to uniquely idenitify each row, instead of just a random number. Or aggregate the data BEFORE it gets here (maybe Power Query if you don't have a SQL layer?)