Top 10 with multiple criteria

[Sracc]

Hi all,

Got a database with some 175 document numbers that I need to extract the top 10 from (based on value). Have been working with a combination of INDEX, MATCH and LARGE function and I managed to get a top 10.

The current code:
=INDEX(PIVOT!$B:$B;MATCH(1;INDEX((PIVOT!$D:$D=LARGE(PIVOT!$D:$D;ROWS(D$1:D1)))*(COUNTIF(D$1:D1;PIVOT!$B:$B)=0);0))

- PIVOT!B:B = Document number
- PIVOT!D:D = Value of document

My struggle now is to get two different criteria included in it. So it should be a top 10 of document numbers that have:
1. Due date "<24-11-2014" (Found in PIVOT!C:C)
2. Document number "<3900143000" (Found in PIVOT!B:B)

Hoping that anyone here can help me find a way to do this!?

Thanks in advance!

 

[Worf]

Hi and welcome to the Board

Here is a VBA solution:

Sub Filtering()
'place headers at b1:d1
'test on a copy of your workbook
Range("f1").Value = Range("b1").Value
Range("g1").Value = Range("c1").Value
Range("f2").Value = "<3900143000"
Range("g2").Value = "<41967"        'date represented as its serial number
'reason: Excel 2007 VBA will not work properly if regional formats like 30/07/14 are used;
'however,they work fine if the filter is done manually...
'advanced filter will use the two criteria above
Range("b1").CurrentRegion.AdvancedFilter xlFilterCopy, Range("f1:g2"), Range("i1:k1"), False
Columns("k:k").AutoFilter
Columns("k:k").AutoFilter 1, "10", xlTop10Items
End Sub