• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Locate rows that don't match others in group

dianacris

Member
The solution here is probably easy but I just cannot determine how to approach this. I have information by client that includes dates. Each client will have 1-5 rows of data. Each row includes a date. If the client has 3 rows and all 3 dates are the same, I can skip them. I need to highlight or extract the clients who have more than one date. How should I approach locating those clients with multiple dates? Small subset shown below. I have added some helper columns but perhaps because I'm dealing with dates, I'm not getting the outcome I want.

81034

Thanks in advance!
 

Attachments

vletm

Excel Ninja
dianacris
This way You could see at once - how many Supplier Code ... maybe same as Client has same Expiry date
and
how many Supplier Codes has Expiry date daily...

About Your I'm not getting the outcome I want.
What kind of the outcome would You need to get?
 

Attachments

dianacris

Member
Thanks for the reply! I need to find out which clients have more than one unique expiry date. The pivot table shows all clients and their expiry dates, but I just want those that have 2+ unique expiry dates and I can't figure out how to be able to filter for them.
 

p45cal

Well-Known Member
In the attached, 2 solutions:
1. A pivot table of your data (not including a helper column) but when creating the pivot table ticked the box 'Add to Data Model' so that a distinct count of dates could be used.

2. The above pivot has to include a Distinct Count of Expiry Date column which isn't very useful to the user so the pivot table at cell J2 is derived from a Power Query query on your data.

Both look something like this:
81157
Both pivots need refreshing to update them.
 

Attachments

Last edited:
Top