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]

 

[skywriter]

Copy the formula down.

Sheet31

*	A	B	C	D
Account Number	Case	Date

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:64px;"><col style="width:64px;"><col style="width:102px;"><col style="width:85px;"></colgroup><tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]

[TD="bgcolor: #ffff00"]Column Formula Needed[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]7/21/2014[/TD]
[TD="align: right"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]7/21/2014[/TD]
[TD="align: right"]2[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]7/21/2014[/TD]
[TD="align: right"]3[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]
[TD="align: right"]300[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]7/2/2014[/TD]
[TD="align: right"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]
[TD="align: right"]400[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]6/30/2014[/TD]
[TD="align: right"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]7[/TD]
[TD="align: right"]500[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]5/30/2014[/TD]
[TD="align: right"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]8[/TD]
[TD="align: right"]500[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]5/30/2014[/TD]
[TD="align: right"]2[/TD]

[TD="bgcolor: #cacaca, align: center"]9[/TD]
[TD="align: right"]700[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]7/21/2014[/TD]
[TD="align: right"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]10[/TD]
[TD="align: right"]800[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]7/4/2014[/TD]
[TD="align: right"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]11[/TD]
[TD="align: right"]900[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]7/3/2014[/TD]
[TD="align: right"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]12[/TD]
[TD="align: right"]900[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]7/3/2014[/TD]
[TD="align: right"]2[/TD]

[TD="bgcolor: #cacaca, align: center"]13[/TD]
[TD="align: right"]900[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]7/3/2014[/TD]
[TD="align: right"]3[/TD]

[TD="bgcolor: #cacaca, align: center"]14[/TD]
[TD="align: right"]900[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]7/3/2014[/TD]
[TD="align: right"]4[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula D2 =COUNTIF($A$2:A2,$A$2:A2) <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

 

[tbobolz]

Thanks Bruce; however, I need the power pivot DAX formula, otherwise this would work great.

Terry

 

[skywriter]

Shoot, sorry I didn't pay attention to that. I just looked at zero reply posts. Sorry I'm of no help.

 

[scottsen]

hhmmm... that's harder than your average question...

In your example, there is no difference between the rows. Same dates even. Do you have some other unique data we can use?

My best answer is likely to involve RANKX, but ... dealing with "ties" will be a problem...

 

[tbobolz]

I will explore the ranking idea, good suggestion.

Actually, the only thing I am conserned about is the account number column, the date is not relevent. I want to count the number of times an account number appears in a new column. So the new column would return 1,2 and 3 for account number 200. this way I can filter on just "1's" in a pivot to eliminate duplicates. Yet I want to achieve this in power pivot columns.

I can not remove the duplicates, as sometimes I need need. I will spare you the explaination to way

 

[scottsen]

If it is not good enough to have all 3 rows have a "3"... if you really need 1,2,3 ... I think you will have to do something weirdly tricky. Like, add a calculated column with a =RANDOM to force there to be a difference, then have your RANKX include that random number in the calculation.

Yes, I am serious

 

[tbobolz]

I am following you, but, a little above my skill set.

 

[scottsen]

Sorry, I gotta go catch a plane to train some folks, so I can't really get you a working sample this week.

The idea is to add a random number to each AcctNumber, so that instead of 200, 200, 200 you end up with 200.0015353, 200.43493820 and 200.358202809 ... so that we have some way to break the ties. Otherwise RANKX will give them all the same value. As would any sort of CALCULATE(COUNTROWS(), ...) --- cuz they ARE all the same unless we break those ties.

The final expression will certainly take some playing with to get right. RANKX always does.

I would start here:
=CALCULATE(RANKX(MyTable, SUM(MyTable[MyRandomNumber])), ALL(MyTable), VALUES(MyTable[Account Number]))

But you might actually need to use an EARLIER() -- not sure I have tried that style of VALUES() in a calc column.

Another to try:
=CALCULATE(COUNTROWS(MyTable), FILTER(ALL(MyTable), MyTable[Account Number] = EARLIER(MyTable[Account Number]) && MyTable[RandomNumber] <= EARLIER(MyTable[RandomNumber])))

Which... *I* like better, cuz I find RANKX a bit confusing

 

[tbobolz]

Hi Scott,

Hope your travels went well!

WOW! I still have tons to learn. I have played around with both of these; however, I am not quite sure how to handle the [MyRandomNumber] and [RandomNumber] parts of these formulas.

In the RANKX example I have the following formula, but receive an error: =CALCULATE(RANKX('MNDOG Physician ID Report - Cases', SUM('MNDOG Physician ID Report - Cases'[MyRandomNumber])), ALL('MNDOG Physician ID Report - Cases'), VALUES('MNDOG Physician ID Report - Cases'[PATIENT ACCOUNT]))

In the EARLIER example I used this exact formula:

=CALCULATE(COUNTROWS('MNDOG Physician ID Report - Cases'), FILTER(ALL('MNDOG Physician ID Report - Cases'), 'MNDOG Physician ID Report - Cases'[PATIENT ACCOUNT] = EARLIER('MNDOG Physician ID Report - Cases'[PATIENT ACCOUNT]) && 'MNDOG Physician ID Report - Cases'[PATIENT ACCOUNT] <= EARLIER('MNDOG Physician ID Report - Cases'[PATIENT ACCOUNT])))
This looks like we are alomost there! the column returns the number of identical patient account number in the patient account column. so...


This is what we have with this formula: Close...
[TABLE="class: cms_table"]
<TBODY>[TR="bgcolor: #cacaca"]
[TD]*[/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca"]1[/TD]
[TD]Account Number[/TD]
[TD]Case[/TD]
[TD]Date[/TD]
[TD="bgcolor: #ffff00"]Column Formula Needed[/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca"]2[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]7/21/2014[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca"]3[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]7/21/2014[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca"]4[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]7/21/2014[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca"]5[/TD]
[TD="align: right"]300[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]7/2/2014[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca"]6[/TD]
[TD="align: right"]400[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]6/30/2014[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca"]7[/TD]
[TD="align: right"]500[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]5/30/2014[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca"]8[/TD]
[TD="align: right"]500[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]5/30/2014[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca"]9[/TD]
[TD="align: right"]700[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]7/21/2014[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca"]10[/TD]
[TD="align: right"]800[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]7/4/2014[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca"]11[/TD]
[TD="align: right"]900[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]7/3/2014[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca"]12[/TD]
[TD="align: right"]900[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]7/3/2014[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca"]13[/TD]
[TD="align: right"]900[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]7/3/2014[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca"]14[/TD]
[TD="align: right"]900[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]7/3/2014[/TD]
[TD="align: right"]4
[/TD]
[/TR]
</TBODY>[/TABLE]