how to list top 10 open issue based on status priority

[koi]

hi guys,


hope you're well and i want to ask how to list only top 10 issues from let say 50 issues,


issue status : open, closed, ongoing

priority : high, medium, low


the top 10 issue list must be based on

1. looking up for high priority first..then medium then low

2. only find the status open and ongoing


thanks for the input, kinda stuck on this


note: if possible only using formula since people i work with not familiar with macro

 

[koi]

please find file for better seeing this problem,


and i think will involve count since if less than 10 meaning the rest of the line will be blank


http://www.speedyshare.com/tzEzv/top-10-issue-list.xlsx

 

[jason]

1) Does each issue/record have a status and priority value?(i would assume yes to both)

2) what version of excel do you have?


A pivot table may be great. i cant view the file now due to work security restrictions... so kinda shooting blind.

but with the pivotTable, you would have your priority field first and a Top 10 Filter

 

[SirJB7]

Hi, koi!


Give a look at this file:

https://dl.dropbox.com/u/60558749/how%20to%20list%20top%2010%20open%20issue%20based%20on%20status%20%26%20priority%20-%20top%2010%20issue%20list%20%28for%20koi%20at%20chandoo.org%29.xlsx


I defined a few dynamic named ranges (PriorityList, StatusList, VlookUpList, Top10List) and a static one (Top10ListTitle), so as to make formulas easier and VlookUp combinations automatically updatable.


In worksheet Summary you might want to hide or white color the yellow values in cells B2:E2.


The formula for the VlookupList is:

=INDICE(PriorityList;ENTERO((FILA()-FILA(PriorityList)+1+FILAS(StatusList)-1)/FILAS(StatusList)))&INDICE(StatusList;RESIDUO(FILA()-FILA(StatusList);FILAS(StatusList))+1) -----> in english: =INDEX(PriorityList,INT((ROW()-ROW(PriorityList)+1+ROWS(StatusList)-1)/ROWS(StatusList)))&INDEX(StatusList,MOD(ROW()-ROW(StatusList),ROWS(StatusList))+1)


The array formula for the Top10List is:

{=SI.ERROR(INDICE(List1;K.ESIMO.MENOR(SI.ERROR(COINCIDIR(VlookUpList;List1[[#Todo];[Priority]]&List1[[#Todo];[Status]];0);0);FILA()-FILA(Top10ListTitle)+1);B$2);"")} -----> in english: {=IFERROR(INDEX(List1,SMALL(IFERROR(MATCH(VlookUpList,List1[[#All],[Priority]]&List1[[#All],[Status]],0),0),ROW()-ROW(Top10ListTitle)+1),B$2),"")}


Omit the external brackets and remember that array formulas should be entered with Ctrl-Shift-Enter instead of just Enter.


If you keep the top 10 list column titles in worksheet Summary as those of the table in worksheet 'Issue Register', you might avoid the back yellowed colored values in first worksheet and replace them by a HlookUp function over table titles row.


Just advise if any issue.


Regards!

 

[koi]

hi SirJB7,


i will take a look, but now i solve it by using helper column + pivot table


1. i combine status & priority in G1

2. combine G1&count(range,criteria) so i will make it status&priority1,status&priority2 etc if there is double

3. pivot all

4. sort based on priority highest to lowest

5. vlookup from that pivot


really hard..i didnt think that something like that will be this hard


thanks and i will post about your solution

 

[koi]

hi SirBJ7,


yours almost correct, but then if you notice issue no 9 is not in the list, because it using the same vlookup as issue no 10,


that is why i'm using Cell&Count(range,criteria) to make it unique


also the priority supposed to be from H to M then L in bottom...so we will working on highest priority first

 

[SirJB7]

Hi, koi!

Ooops...! You're right, let me open another Carlsberg's six-pack (shared with my friend b(ut)ob(ut)hc, of course!) and see if inspiration comes by hand of my muse.

Gonna get back to you.

Regards!

PS: Regarding priority's and status order, that's not a problem, just reorder properly your listed values and get the updated list ready to use (that's the why for three dynamic named lists).


EDIT:

PS2: Nop! The order is related to sorting records, not just as simple as I wrote before.