Попытка получить Excel, чтобы создать новый список с несколькими критериями

Question

Возможно, я слишком много спрашиваю об Excel, или, может быть, я просто не могу понять это. Я хочу создать список, который является подмножеством другого списка на основе двух уникальных критериев.

Список ввода находится на одном листе в диапазоне Sheet1!B9:B20. Элементы списка являются текстовыми и имеют вид: NNNN - текстовое описание (например, 1320 - расходы владельца). Это те первые четыре персонажа, которые важны. Это код GL.

Выходной список является подмножеством входного. Мне нужно исключить элементы, где a) код GL начинается с «4», и b) код GL находится в списке исключенных 4-значных кодов (например, 1320), перечисленных в виде текста на листе 3!A2:A20. Так, например:

Input list:                     Output list
1000 - Description 1    --->    1000 - Description 1  
1320 - Description 2            5110 - Description 4  
4000 - Description 3  
5110 - Description 4  

... где 4000 начинается с «4», а 1320 находится в списке исключенных.

Вот код, который успешно исключает элементы, начинающиеся с '4':

Note the {} brackets; it's an array formula.

{=IFERROR(
INDEX(Sheet1!B$9:B$20,
SMALL(
IF(
LEFT(Sheet1!$B$9:$B$20,1)<>"4",    // I can enter only a single condition here
ROW(Sheet1!B$9:B$20)-ROW(Sheet1!B$9)+1
),
ROWS(Sheet1!B$9:'SS Version'!B9)
)
),"")}

Я попробовал следующие условия без удачи:

AND(LEFT(Sheet1!$B$9:$B$20,1)<>"4", LEFT(Sheet1!$B$9:$B$20,4)<>Sheet3!$A$2:$A$20),

AND(LEFT(Sheet1!$B$9:$B$20,1)<>"4", LEFT(Sheet1!$B$9:$B$20,4)<>{"1320","1330", excluded code list},

Я не против, чтобы все коды 4XXX были добавлены в мой исключенный список и использовался только этот список, но это не сработало, когда я попробовал. Я пытался:

LEFT(Sheet1!$B$9:$B$20,4)<>Sheet3!$A$2:$A$20),

Есть предложение?

Answer 1

Вы можете использовать расширенный фильтр для создания нового списка в другом месте.

Смотрите скриншот для настройки. Excluded диапазон ячеек на Sheet 2 который включает ваш исключенный список.

Формулы

A2:   =LEFT(A7)<>"4"
B2:   =ISERROR(LOOKUP(2,1/ISNUMBER(FIND(Excluded,A7))))

Формула в B2 немного запутана, поскольку формулы массива не допускаются в качестве критерия в расширенном фильтре.

Answer 2

Я не пришел к полному решению, но это очень близко к тому, что я думаю, что вы ищете:

=IF(ISNA(MATCH(NUMBERVALUE(LEFT(INDEX(Sheet1!$B$9:$B$20,SMALL(
IF(
LEFT(Sheet1!$B$9:$B$20,1)<>"4",
ROW(Sheet1!B$9:B$20)-ROW(Sheet1!B$9)+1
),
ROW()-8
)),4)),Sheet3!A:A,0)),INDEX(Sheet1!$B$9:$B$20,SMALL(
IF(
LEFT(Sheet1!$B$9:$B$20,1)<>"4",
ROW(Sheet1!B$9:B$20)-ROW(Sheet1!B$9)+1
),
ROW()-8
)))

Это возвращает результат, но между ними есть ЛОЖНЫЕ строки, от которых я не смог избавиться.

Мой список ввода в Sheet1 был таким же, как в A9:B20:

1   1000 - Description 1    
2   1320 - Description 2 on exlusion list   
3   4000 - Description 3    
4   5110 - Description 4    
5   4100 - Description 5    
6   2230 - Description 6 on exclusion list  
7   3330 - Description 7 on exclusion list  
8   6000 - Description 8    
9   7000 - Description 9    
10  5330 - Description 10 on exclusion list 
11  blank
12  blank

Исключения на листе 3 приведены ниже (столбец A):

1320
2230
3330
5330

Вывод выглядит так:

1000 - Description 1
FALSE
5110 - Description 4
FALSE
FALSE
6000 - Description 8
7000 - Description 9
FALSE
0
0
FALSE
FALSE

Я знаю, что это не полный ответ, но я говорю о том, что кто-то может подняться над моей работой, пока я все еще пытаюсь это взломать. У меня недостаточно прав для добавления комментариев.