Unique, without duplicates, and able to be counted in a pivot table...or another solution

[RLCornish]

I have a large batch of data, below is just a short sampling of it, that I am trying to report on. If the same store, on the same date, had the same style I only want it to be counted 1 time when I pull the data into my pivot table. I've tried concatenating those 3 elements; Store Name, Show Date, & Style, thinking I could do something with that but everything I find wants to count them in the formula, but those are the 3 critical criteria to tell me how many times the style was shown. I'm thinking I just need them to have some numeric indicator so when I pivot the date and report based on style I can accurately show that even though the same style was in the same store on the same date it was only 1 "showing".

I'll also throw out it is absolute that all the data elements I need to consider are not together but separated by various other data elements.

* Note, the dates are in DD-MM-YY format...how my company does it.

I really am stumped. Any help you can offer would be grand.

[TABLE="width: 722"]
<colgroup><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]HIDE FROM PDF RPRT[/TD]
[TD]Store Name[/TD]
[TD]Region[/TD]
[TD]Rep Name[/TD]
[TD]Show Date[/TD]
[TD]Style #[/TD]
[/TR]
[TR]
[TD]BVB[/TD]
[TD]XZY Store[/TD]
[TD]BVB[/TD]
[TD]Ann[/TD]
[TD]15-10-16[/TD]
[TD]ABT2245[/TD]
[/TR]
[TR]
[TD]UWBB[/TD]
[TD]Anything goes store[/TD]
[TD]South[/TD]
[TD]TAYLOR[/TD]
[TD]22-10-16[/TD]
[TD]ABT2245[/TD]
[/TR]
[TR]
[TD]BVB[/TD]
[TD]XZY Store[/TD]
[TD]BVB[/TD]
[TD]Ann[/TD]
[TD]15-10-16[/TD]
[TD]ABT2245[/TD]
[/TR]
[TR]
[TD]UWBB[/TD]
[TD]You're the bomb[/TD]
[TD]South[/TD]
[TD]TAYLOR[/TD]
[TD]22-10-16[/TD]
[TD]ABT2245[/TD]
[/TR]
[TR]
[TD]UWDS[/TD]
[TD]Anything goes store[/TD]
[TD]Midwest[/TD]
[TD]Madison/Morgan[/TD]
[TD]29-10-16[/TD]
[TD]ABT2245[/TD]
[/TR]
[TR]
[TD]UWDS[/TD]
[TD]Anything goes store[/TD]
[TD]Midwest[/TD]
[TD]Madison/Morgan[/TD]
[TD]29-10-16[/TD]
[TD]ABT2245[/TD]
[/TR]
[TR]
[TD]BVB[/TD]
[TD]XZY Store[/TD]
[TD]BVB[/TD]
[TD]Ann[/TD]
[TD]15-10-16[/TD]
[TD]ABT2245[/TD]
[/TR]
[TR]
[TD]UWBB[/TD]
[TD]You're the bomb[/TD]
[TD]South[/TD]
[TD]TAYLOR[/TD]
[TD]22-10-16[/TD]
[TD]ABT2245[/TD]
[/TR]
[TR]
[TD]UWDS[/TD]
[TD]Anything goes store[/TD]
[TD]Midwest[/TD]
[TD]Madison/Morgan[/TD]
[TD]29-10-16[/TD]
[TD]ABT2245[/TD]
[/TR]
</tbody>[/TABLE]

 

[Rijnsent]

Hi RL,
what I would do: add a column with a COUNTIFS formula to count how many times that comination has occured above and up to that line. In your data e.g. as formula in G2: =COUNTIFS($B$1:$B2,B2,$E$1:$E2,E2,$F$1:$F2,F2) . Drag that formula down and put a filter on your pivot table where that new column equals 1.
Hope that helps,
Koen

 

[RLCornish]

Thanks Rijnsent.

Someone gave me the suggestion of adding a column and concatenating some of the elements and then doing a IF with a nested COUNTIF, like this =IF(COUNTIF($AE$2:$AE1388,AE1388)=1,1,""). It honestly works great.