Counting unique data

[kcleere]

I hope I can articulate this properly.

In my spreadsheet I have a column of customer numbers and in another column I have dates on which they made a payment. There can be one or more of the same date for a customer number.

Customer 2134 could have 4 rows showing 10/23/17 and 1 row showing 10/24/17. This would count 2 unique dates for that customer number and this is what I need to return in a pivot.


Cust Payments
2134 2

Not sure if this is clear enough. Thank you in advance.

 

[Zubair Muhammad]

Re: Need help counting unique data

Hi kcleere

One way is to load the data/table into Power Pivot Data Model and then create a Pivot Table using "data Model"

You will surely find the Distinct Count in the "Value Field Settings" then

Pivot tables based on Excel Tables/Data (I believe) do not support "Distinct Count" Aggregation

 

[kcleere]

Re: Need help counting unique data

Thank you. What I envisioned was adding a capture column with a formula that returned Qty on unique dates for each customer number and then use that column in the pivot.

 

[kcleere]

Re: Need help counting unique data

I'll have to re-think this. I don't have Power Pivot as an option add-in

 

[Zubair Muhammad]

Re: Need help counting unique data

Hi kcleere,

Do you have to do it via a pivot table or an Excel Formula would do?

 

[kcleere]

Re: Need help counting unique data

I'd prefer a formula that I could add to the data and then I can use that data in a pivot.

 

[Zubair Muhammad]

Re: Need help counting unique data

Hi kcleere,

My apologies for getting back late

Please see the file attached. Hope it helps

https://1drv.ms/x/s!AhiuKaBxxAufjjOJfA78X0ODt4pv


Basically I wrote a custom formula (UDF) to find distinct count

 

[tonyyy]

Re: Need help counting unique data

kcleere,

Another approach would be to...

Select your data.
Use Advanced Filter, with the "Copy to another location" and "Unique records only" options selected.
Use the result as the data source for a PivotTable.

Cheers,

tonyyy

 

[Rick Rothstein]

Re: Need help counting unique data

If your like dates are grouped next to each other rather than intermingled with each other, then this kind of formula should work...

=SUMPRODUCT(0+(B1:B24<>B2:B25))

Note the first range is your actual data range from the first data item to the last... the second range is offset from that range by one cell.

 

[Marcelo Branco]

Re: Need help counting unique data

I'd prefer a formula that I could add to the data and then I can use that data in a pivot.

Maybe

	A​	B​	C​
1​	Customer​	PaymentDate​	CountUnique​
2​	2134​	10/23/2017​	2​
3​	2134​	10/23/2017​	2​
4​	2134​	10/23/2017​	2​
5​	2134​	10/24/2017​	2​
6​	1234​	10/24/2017​	1​
7​	5678​	10/23/2017​	2​
8​	5678​	10/23/2017​	2​
9​	5678​	10/24/2017​	2​
10​	5678​	10/24/2017​	2​

<tbody>
</tbody>

Array formula in C2 copied down
=SUM(IF(FREQUENCY(IF($A$2:$A$10=A2,$B$2:$B$10),$B$2:$B$10),1))
confirmed with Ctrl+Shift+Enter, not just Enter

Pivot Table

Customer​	Max of CountUnique​
1234​	1​
2134​	2​
5678​	2​

<tbody>
</tbody>

Right-click in the CountUnique field and in Summarize Values by...pick Max

M.