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?


[TABLE="width: 313"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Audit Date[/TD]
[TD]Driver Training Card Number[/TD]
[/TR]
[TR]
[TD]31/10/2017[/TD]
[TD]SCU001[/TD]
[/TR]
[TR]
[TD]31/10/2017[/TD]
[TD]SCU002[/TD]
[/TR]
[TR]
[TD]31/10/2017[/TD]
[TD]SCU056[/TD]
[/TR]
[TR]
[TD]31/10/2017[/TD]
[TD]SCU010[/TD]
[/TR]
[TR]
[TD]01/11/2017[/TD]
[TD]SCU007[/TD]
[/TR]
[TR]
[TD]02/11/2017[/TD]
[TD]SCU010[/TD]
[/TR]
[TR]
[TD]02/11/2017[/TD]
[TD]SCU025[/TD]
[/TR]
[TR]
[TD]02/11/2017[/TD]
[TD]SCU027[/TD]
[/TR]
[TR]
[TD]02/11/2017[/TD]
[TD]SCU191[/TD]
[/TR]
[TR]
[TD]02/11/2017[/TD]
[TD]SCU007[/TD]
[/TR]
[TR]
[TD]02/11/2017[/TD]
[TD]SCU035[/TD]
[/TR]
[TR]
[TD]02/11/2017[/TD]
[TD]SCU036[/TD]
[/TR]
[TR]
[TD]02/11/2017[/TD]
[TD]SCU038[/TD]
[/TR]
[TR]
[TD]03/11/2017[/TD]
[TD]SCU040[/TD]
[/TR]
[TR]
[TD]03/11/2017[/TD]
[TD]SCU041[/TD]
[/TR]
[TR]
[TD]03/11/2017[/TD]
[TD]SCU010[/TD]
[/TR]
[TR]
[TD]03/11/2017[/TD]
[TD]SCU042[/TD]
[/TR]
[TR]
[TD]03/11/2017[/TD]
[TD]SCU046[/TD]
[/TR]
[TR]
[TD]03/11/2017[/TD]
[TD]SCU049[/TD]
[/TR]
</tbody>[/TABLE]


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?

[TABLE="width: 189"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Row Labels[/TD]
[TD]Min of Audit Date[/TD]
[/TR]
[TR]
[TD]SCU001[/TD]
[TD="align: right"]31/10/2017[/TD]
[/TR]
[TR]
[TD]SCU002[/TD]
[TD="align: right"]31/10/2017[/TD]
[/TR]
[TR]
[TD]SCU007[/TD]
[TD="align: right"]01/11/2017[/TD]
[/TR]
[TR]
[TD]SCU010[/TD]
[TD="align: right"]31/10/2017[/TD]
[/TR]
[TR]
[TD]SCU025[/TD]
[TD="align: right"]02/11/2017[/TD]
[/TR]
[TR]
[TD]SCU027[/TD]
[TD="align: right"]02/11/2017[/TD]
[/TR]
[TR]
[TD]SCU035[/TD]
[TD="align: right"]02/11/2017[/TD]
[/TR]
[TR]
[TD]SCU036[/TD]
[TD="align: right"]02/11/2017[/TD]
[/TR]
[TR]
[TD]SCU038[/TD]
[TD="align: right"]02/11/2017[/TD]
[/TR]
[TR]
[TD]SCU040[/TD]
[TD="align: right"]03/11/2017[/TD]
[/TR]
[TR]
[TD]SCU041[/TD]
[TD="align: right"]03/11/2017[/TD]
[/TR]
[TR]
[TD]SCU042[/TD]
[TD="align: right"]03/11/2017[/TD]
[/TR]
[TR]
[TD]SCU046[/TD]
[TD="align: right"]03/11/2017[/TD]
[/TR]
[TR]
[TD]SCU049[/TD]
[TD="align: right"]03/11/2017[/TD]
[/TR]
[TR]
[TD]SCU056[/TD]
[TD="align: right"]31/10/2017[/TD]
[/TR]
[TR]
[TD]SCU191[/TD]
[TD="align: right"]02/11/2017[/TD]
[/TR]
</tbody>[/TABLE]

 

[sknight22]

Like this?

[TABLE="width: 189"]
<tbody>[TR]
[TD]Row Labels[/TD]
[TD]Min of Audit Date[/TD]
[/TR]
[TR]
[TD]SCU001[/TD]
[TD="align: right"]31/10/2017[/TD]
[/TR]
[TR]
[TD]SCU002[/TD]
[TD="align: right"]31/10/2017[/TD]
[/TR]
[TR]
[TD]SCU007[/TD]
[TD="align: right"]01/11/2017[/TD]
[/TR]
[TR]
[TD]SCU010[/TD]
[TD="align: right"]31/10/2017[/TD]
[/TR]
[TR]
[TD]SCU025[/TD]
[TD="align: right"]02/11/2017[/TD]
[/TR]
[TR]
[TD]SCU027[/TD]
[TD="align: right"]02/11/2017[/TD]
[/TR]
[TR]
[TD]SCU035[/TD]
[TD="align: right"]02/11/2017[/TD]
[/TR]
[TR]
[TD]SCU036[/TD]
[TD="align: right"]02/11/2017[/TD]
[/TR]
[TR]
[TD]SCU038[/TD]
[TD="align: right"]02/11/2017[/TD]
[/TR]
[TR]
[TD]SCU040[/TD]
[TD="align: right"]03/11/2017[/TD]
[/TR]
[TR]
[TD]SCU041[/TD]
[TD="align: right"]03/11/2017[/TD]
[/TR]
[TR]
[TD]SCU042[/TD]
[TD="align: right"]03/11/2017[/TD]
[/TR]
[TR]
[TD]SCU046[/TD]
[TD="align: right"]03/11/2017[/TD]
[/TR]
[TR]
[TD]SCU049[/TD]
[TD="align: right"]03/11/2017[/TD]
[/TR]
[TR]
[TD]SCU056[/TD]
[TD="align: right"]31/10/2017[/TD]
[/TR]
[TR]
[TD]SCU191[/TD]
[TD="align: right"]02/11/2017[/TD]
[/TR]
</tbody>[/TABLE]

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.