• 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.

Unique & distinct result based on multiple criteria

D

David Jenkins

New Member
Good Morning,

I was wondering if someone can advise on how I ca return a list of unique and distinct results based on multiple criteria. I have google'd this and followed many examples and they worked to an extent but don't work now that I have started including dates.

I have attached a file with an example of the data that i am using, what criteria I am looking for and what I am trying to return.

I am using named ranges.

I am trying to return the names of people that are on a specific team and >= to a certain date, but they could swap teams and an end date would be added so they should only return up to the end date (if that makes sense). If they moved back to the same team in the future I would only want them to return data for that date range, not a prior one.

Any help is appreciated. Loving this site and the dashboards especially (thats what I am trying to start using this on).

Regards.

Dave.
 
I have attached a file with an example of the data that i am using
Click to expand...
....nope

This has been asked if the past few weeks a search of the last two or three pages may help
 
Sorry both, that was a fail! I've uploaded it now.

Bobhc - I'll also have a scroll through to see what I can see. Loving the signature as well :)
 

Attachments

  • Book1.xls
    27 KB · Views: 7
I've had a look through the last five pages and can find some that look like what I want but when I go in they aren't.

I've tried index matching, sumproducts and some other formulas that are on google, but none of them worked.
 
Hi David,

Can you tell as per your uploaded file do you expect ManagerD & ManagerF as answer?

Secondly, can you explain ManagerD is in Team2 01-01-2014 to 31-01-2014 and also in Team4 from 01-01-2014 with no end date. What this means?

Regards,
 
Yeah it should return ManagerD and ManagerF for the criteria that are provided.

A manager can move teams. StartDate is the date that they manage the team from, EndDate is the last date that they managed that team. If there is nothing in EndDate then they are still managing that team.

If they have moved then the StartDate is normally the day after the last EndDate. With the example you give a above both ManagerD and ManagerF, managed their teams from 01/01/2014 to 31/01/2014 and then on the 01/02/2014 they managed Team4.

This is the type of formula I was trying to use:

{=INDEX(tmHistory[ManagerName],MATCH(0,COUNTIF(J3:$J$3,tmHistory[ManagerName])+IF(tmHistory[Team]<>$H$3,1,0)+IF(tmHistory[StartDate]>=$H$4,1,0),0))}

Thank you for looking at this.
 
Okay, so I've just played again and the code above works to extent when tmHistory[StartDate]>=$H$4 is changed to tmHistory[StartDate]<>$H$4.

But realised that my logic is wrong. As long as the Date is >= StartDate and <= EndDate it should return (any that dont have an EndDate I have now set to 31/12/3000.
 
@Somendra Misra

That works absolutely perfectly - that is awesome - thank you so much for your help!

If it isn't too much trouble can you advise how this works? I've never used ROW or SMALL before cant quite grasp whats happening here!
 
@David Jenkins

Basically Small function is getting row number in the array where crieterias are matching. So in SMALL function an array of Numbers 1 - n with the help of ROW function is generated and ROWS function is giving 1st and 2nd small out of that array as you copy down the formula.

Consider below:

=ROW(A$1:A$5)-ROW(A$1)+1 will generate something like this {1;2;3;4;5}-{1}+1 which will give {0;1;2;3;4}+1 and finally {1;2;3;4;5}. So where ever criteria matches with the help of IF function it will get a number which will correspond to the row number of the array from which you want to extract data.

Hope this will help you.

And welcome back anytime!

Regards,
 
You must log in or register to reply here.
Back
Top