Filtering based on user input

[Bjorn]

Dear all,

I have the following table:

BranchID Customer Value Field1 Field2 Fieldn
1110 aa 50
1110 aa 100
1110 bb 200
1111 cc 30
1111 dd 60
1111 ee 90

Based on user input (BranchID) I would like to report the first three columns of the two largest records (by value) for that particular branch.

I can do this by advance filtering and then sorting the output, but this involves manual involvement by the user.

I suppose one way to do this is with VBA, but I would like to avoid that.

Is there any other way?

Thankful for any help.

 

[NARAYANK991]

Hi Bjorn ,

Where will the user input the BranchID ?

Which are the first 3 columns ? Are they Field1 , Field2 and Field3 ?

If so , try the following formula :

=INDEX(Table1[Field1],MATCH(LARGE(IF(Table1[BranchID] = Entered_BranchID , Table1[Value]),ROW(A1))&Entered_BranchID,Table1[Value]&Table1[BranchID],0))

will give data for the largest value for the entered BranchID. Note that this is an array formula , and has to be entered using CTRL SHIFT ENTER.

Copying this one row down will change the ROW(A1) to ROW(A2) , and result in returning the first field value corresponding to the second highest value.

Narayan

 

[Bjorn]

Thank you Narayan!

You saved my day (or week or year)!

I am much obliged.

 

[Bjorn]

Hello all (and especially NARAYANK991),

following the earlier postings in this thread I have discovered a problem with the above formula.

If I want to list the five biggest lines from my table, it works great as long as there are values <> 0 in the column it is to be sorted by. When there is not, it repeats the the first of "0 customers". It is probably more easily understood in the attached example

If I input '3503111 in A1 it works fine, with '3503110 it does not

I would be most helpful for any help.

 

[NARAYANK991]

Hi ,

See if this is OK.

Narayan

 

[Bjorn]

Hello Narayan

Lovely, works a treat. Thank you.