Alphabetically Sort a dynamic range based on multiple filter values

KJefferies

New Member
Joined
Oct 20, 2017
Messages
4
Hi all

I am struggling to get an Array formula to sort based on Multiple search values.

I have 3 List.

when the excel was first written I only had List B and C and i used the following formula to create a result list of just the references in list C

{=IFERROR(INDEX(ListC,MATCH(ROW(1:1),IF(ListB="Manager",COUNTIFS(ListB,"Manager",ListC,"<" &ListC)+1,""),0)),"")} array formula

I am now trying to expend the result list to look at List A to get the filter values by changing the formula to

{=IFERROR(INDEX(ListC,MATCH(ROW(1:1),IF(ListB=ListA,COUNTIFS(ListB,ListA,ListC,"<" &ListC)+1,""),0)),"")}

But this does not get the results I am looking for. All 3 lists are dynamic named ranges so would like if possible to stick to the named ranges and due to the usage of the sheet I can not use VBA.

Can anyone help

Example List Data..

ListA
Manager
Executive


<colgroup><col></colgroup><tbody>
</tbody>
ListB
Manager
Pre Sales
Sales Rep
Manager
Manager
Sales Rep
Sales Rep
Manager
Manager
Manager
Manager
Executive
Executive
Executive

<colgroup><col></colgroup><tbody>
</tbody>

ListC
SF00001111
SS00001112
SPSB0512PS
JK00001001
PP00001113
BTSB0712TB
FFSB0713FF
RB00001114
PB00001115
EE00001116
BB00001117
NN00001118
BB00001119
AW00001120

<colgroup><col></colgroup><tbody>
</tbody>
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
I have been playing some more today whilst looking back at my notes when I wrote the xls in the first place. The Problem appears to be generated as part of the first range and criteria of the COUNTIFS statement. If you run this with a single value in LISTA the new formula works and the COUNTIFS(ListB,ListA part is returning the total count where the condition is met e.g. if you just have "Manager" in ListA this returns 7. However if you add to items to LISTA "MANAGER","EXECUTIVE" it does not return the total but returns an array with the total of each item {7,3}.

Any ideas how I can get the Total Returned.

I have also Noticed that the Condition of the IF statement fails if there are Multiple items in LISTA but I have been able to work round that by changing the "LISTB = LISTA" to be "
ISNUMBER(MATCH(ListB,ListA,0))" which correctly creates the expected True and False list needed by the if statement.

Cheers fro any support folks can give.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,210
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top