Ranking using a medium data set

[scottmoss]

Hi,

Been a member for a few months now and I can't find the answer to this anywhere.

I got a medium sized quality database (in Excel - i know) that has these columns (plus some more)

Name (Col A)
Date (Col G)
Error Reason (Col E)
Week # (Col O)
Week # & Name (Concatenated) (Col R)

What I want without using a pivot table or VBA if possible is to be able to say tell me the top 3 error reasons for person X for week num X. I know this can be done easily with a pivot table but I'm trying to stay away from that for ease of use reasons.

I've tried sumproduct, rank & countif with no luck.

Any help would be greatly received!

Thanks,

Scott

[daddylonglegs]

Hello Scott, try this setup....

In T2 specify week number and in T3 name and then in T4 use this formula

=INDEX(E$2:E$1000,MODE(IF(R$2:R$1000=T$2&T$3,IF(E$2:E$1000<>"",IF(COUNTIF(T$3:T3,E$2:E$1000)=0, MATCH(E$2:E$1000,E$2:E$1000,0))))))

confirmed with CTRL+SHIFT+ENTER and copied down to T6 to give the top three error reasons

For a count of each use this formula in U4 copied down to U6

=SUMPRODUCT((E$2:E$1000=T4)*(R$2:R$1000=T$2&T$3))

Assumes 1000 rows of data, extend as required

[scottmoss]

Hello Scott, try this setup....

In T2 specify week number and in T3 name and then in T4 use this formula

=INDEX(E$2:E$1000,MODE(IF(R$2:R$1000=T$2&T$3,IF(E$2:E$1000<>"",IF(COUNTIF(T$3:T3,E$2:E$1000)=0, MATCH(E$2:E$1000,E$2:E$1000,0))))))

confirmed with CTRL+SHIFT+ENTER and copied down to T6 to give the top three error reasons

For a count of each use this formula in U4 copied down to U6

=SUMPRODUCT((E$2:E$1000=T4)*(R$2:R$1000=T$2&T$3))

Assumes 1000 rows of data, extend as required

Thanks for this it works great! How do I make it display their second and third most?

Thanks again for your help.

Scott

[scottmoss]

Ignore the above, was being dumb. Thanks again for the help. But I've noticed if there is only 1 of an item it will #N/A anyway I can combat this so it will display it?

Thanks again.

[daddylonglegs]

If you make this small change then the formula will return even single values

=INDEX(E$2:E$1000,MODE(IF(R$2:R$1000=T$2&T$3,IF(E$2:E$1000<>"",IF(COUNTIF(T$3:T3,E$2:E$1000)=0, MATCH(E$2:E$1000,E$2:E$1000,0)*{1,1})))))

still confirmed with CTRL+SHIFT+ENTER