Pivot table totals for unique numbers

[sknight22]

Hello,

I have a large database at work for training. I have a lot of information on the individuals including their unique training card number (in the below format). I wanted excel to show a result including date and the total number of new training cards issued that day but the training numbers are recorded on multiple days. I only want them to be counted against the total on the first date the number appears in the data.

Is this possible to do on a pivot table or will it need a formula?

Audit Date	Driver Training Card Number
31/10/2017	SCU001
31/10/2017	SCU002
31/10/2017	SCU056
31/10/2017	SCU010
01/11/2017	SCU007
02/11/2017	SCU010
02/11/2017	SCU025
02/11/2017	SCU027
02/11/2017	SCU191
02/11/2017	SCU007
02/11/2017	SCU035
02/11/2017	SCU036
02/11/2017	SCU038
03/11/2017	SCU040
03/11/2017	SCU041
03/11/2017	SCU010
03/11/2017	SCU042
03/11/2017	SCU046
03/11/2017	SCU049

<colgroup><col><col></colgroup><tbody>
</tbody>

Thanks

Stephen

 

[steve the fish]

So you want the pivot to show the earliest date the card number appears? You can do that yes. Put the card number in the rows and audit date in the values. Change it to min. You will then have unique card numbers and their earliest date.

 

[sknight22]

So you want the pivot to show the earliest date the card number appears? You can do that yes. Put the card number in the rows and audit date in the values. Change it to min. You will then have unique card numbers and their earliest date.

Thanks for your help but I don't think I explained it well. I Basically want to report on how many NEW cards I issue daily from the data, however the data has multiple entries every day for the same card numbers so I wanted the pivot or formula only show each card once in the total on the date it first appears.

Hope this makes sense

Thanks
Stephen

 

[steve the fish]

Didnt that what i explained do? It will show each card just the once and will show you the date it first appeared. What have i missed?

 

[steve the fish]

Like this?

Row Labels	Min of Audit Date
SCU001	31/10/2017
SCU002	31/10/2017
SCU007	01/11/2017
SCU010	31/10/2017
SCU025	02/11/2017
SCU027	02/11/2017
SCU035	02/11/2017
SCU036	02/11/2017
SCU038	02/11/2017
SCU040	03/11/2017
SCU041	03/11/2017
SCU042	03/11/2017
SCU046	03/11/2017
SCU049	03/11/2017
SCU056	31/10/2017
SCU191	02/11/2017

<colgroup><col><col></colgroup><tbody>
</tbody>

 

[sknight22]

Like this?

Row Labels	Min of Audit Date
SCU001	31/10/2017
SCU002	31/10/2017
SCU007	01/11/2017
SCU010	31/10/2017
SCU025	02/11/2017
SCU027	02/11/2017
SCU035	02/11/2017
SCU036	02/11/2017
SCU038	02/11/2017
SCU040	03/11/2017
SCU041	03/11/2017
SCU042	03/11/2017
SCU046	03/11/2017
SCU049	03/11/2017
SCU056	31/10/2017
SCU191	02/11/2017

<tbody>
</tbody>

Hello thanks again for looking at this. Basically, I am training about 70 people a day. Every time they come in a record of their card number (SCU001 etc) about 20 of those are new members with a new card number. I wanted to have a total every day from the data of only new card numbers.

 

[steve the fish]

You can sort by the date and look at the top of the pivot for the date you require. Dates are numbers to excel with the most recent date being greater than past dates. If you want it to only show you a specific date you could put the dates in the total and the column part of the pivot and filter out the dates not required in the column. Refresh the pivot every day and filter by the new date.