Tentando obter o Excel para criar uma nova lista com vários critérios

Question

Eu posso estar perguntando muito sobre o Excel, ou talvez eu simplesmente não consiga descobrir. Eu quero criar uma lista que é um subconjunto de outra lista com base em dois critérios que são exclusivos.

A lista de entrada está em uma planilha no intervalo Sheet1! B9: B20. Os elementos da lista são textos e estão na forma de: NNNN - Descrição de texto (por exemplo, 1320 - Despesa do proprietário). São esses quatro primeiros caracteres que são importantes. É um código GL.

A lista de saída é um subconjunto da entrada. Preciso excluir elementos em que a) o código GL começa com '4' e b) o código GL está em uma lista de códigos de 4 dígitos excluídos (por exemplo, 1320), listados como texto em Sheet3! A2: A20. Então, por exemplo:

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

... onde 4000 começa com um '4' e 1320 está na lista excluída.

Este é o código que exclui com sucesso os itens que começam com '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)
)
),"")}

Eu tentei as seguintes condições sem sorte:

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},

Não sou adverso em ter todos os códigos 4XXX adicionados à minha lista excluída e usar apenas essa lista, mas isso também não funcionou quando tentei. A condição que eu tentei foi:

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

Tem uma sugestão?

    

Answer 1

Você pode usar um filtro avançado para criar uma nova lista em outro local.

Veja a captura de tela da configuração. Excluded é o intervalo de células em Sheet 2 que inclui sua lista excluída.

Fórmulas

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

A fórmula em B2 é um pouco confusa, pois as fórmulas de matriz não são permitidas como critério no Filtro Avançado.

    

Answer 2

Ainda não cheguei à solução completa, mas isso é muito parecido com o que eu acho que você está procurando:

=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
)))

Isso está retornando o resultado, mas há as linhas FALSE entre as quais não consegui me livrar.

Minha lista de entrada na Folha1 estava como abaixo em 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

As exclusões na Planilha3 são as seguintes (coluna A):

1320
2230
3330
5330

A saída é assim:

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

Eu sei que isso não é uma resposta completa, mas colocá-lo aqui para que alguém possa construir em cima do meu trabalho enquanto eu ainda estou tentando quebrar isso. Eu não tenho credenciais suficientes para adicionar comentários.