Como procurar uma string dentro de uma coluna de substrings

Question

Eu tenho um arquivo de dados, onde há uma tabela como esta:

(DATA TABLE)
           REF_ID              |     COST    |   TYPE
======================================================
123_COMPUTER_XYZ               | 50000       | (to be filled)
45623_LAPTOP_AOS               | 90000
BHJ_WALLET_ASODO               | 1000
BUIGQ32_TRIMMER                | 200
...

Agora, quero adicionar outra coluna à tabela, em que o type do produto é colocado automaticamente usando uma tabela de pesquisa, que defini da seguinte forma:

(TYPE TABLE)
PRODUCT SUBSTRING    |  TYPE
=====================================
COMPUTER             | computer
LAPTOP               | computer
WALLET               | personal
TRIMMER              | personal-care
...

Eu quero usar a coluna PRODUCT SUBSTRING no TYPE TABLE como uma pesquisa e colocar o valor correspondente de TYPE no 3ª coluna da TABELA DE DADOS.

Eu olhei para muitas perguntas:

• Localizar substring na coluna - Excel
• Lookup e valor de retorno com base na correspondência de subcadeia
• link

mas todos eles fazem o contrário, em que a string a ser pesquisada (campo REF_ID) é uma sub-string da TYPE-TABLE.

Você pode me ajudar?

    

Answer 1

Aqui está uma solução baseada em um curto UDF () .

Digamos que a tabela de pesquisa esteja nas colunas F e G da linha 1 até o máximo 100 linhas. Primeiro insira este UDF () em um módulo padrão:

Public Function GetType(sIN As String) As String
   Dim LookupTable As Range, nItems As Long
   Set LookupTable = Range("F1:G100")
   nItems = 100

   For i = 1 To nItems
      If LookupTable(i, 1) = "" Then Exit For
      If InStr(1, sIN, LookupTable(i, 1)) > 0 Then
         GetType = LookupTable(i, 2)
         Exit Function
      End If
   Next i
   GetType = "UN-FOUND"
End Function

Em seguida, na célula C1 , digite:

=GetType(A1)

e copie:

FunçõesDefinidaspeloUsuário(UDFs)sãomuitofáceisdeinstalareusar:

1. ALT-F11exibeajaneladoVBE
2. ALT-IALT-Mabreumnovomódulo
3. coleomaterialefecheajaneladoVBE

Sevocêsalvarapastadetrabalho,aUDFserásalvacomela.SevocêestiverusandoumaversãodoExcelposteriora2003,deverásalvaroarquivocomo.xlsmemvezde.xlsx

PararemoveroUDF:

1. abrirajaneladoVBEcomoacima
2. limpeocódigo
3. fecheajaneladoVBE

ParausaroUDFdoExcel:

=myfunction(A1)

Parasabermaissobremacrosemgeral,consulte:

link

e

link

e para detalhes sobre UDFs, consulte:

link

As macros devem estar ativadas para que isso funcione!

    

Answer 2

Aqui está uma solução para o seu problema:

=INDEX(Table2[TYPE],MAX((ROW(Table2[PRODUCT SUBSTRING])-1)*SIGN(IFERROR(SEARCH(Table2[PRODUCT SUBSTRING],[@[REF_ID]]),0))))
Esta é uma fórmula de matriz, você precisa entrar com CTRL + Deslocar + Enter .

onde:

• IFERROR(SEARCH(Table2[PRODUCT SUBSTRING],[@[REF_ID]]),0)) verifica qual substring está incluída no REF_ID real (dá posição inicial para ele, 0 para todas as outras substrings)
• SIGN(...) - converte a lista anterior para 1 para a substring encontrada
• (ROW(Table2[PRODUCT SUBSTRING])-1) - número da linha menos um (posição na lista se o cabeçalho estiver na primeira linha)
• MAX(...) - como o único valor para a substring encontrada é diferente de zero, essa função extrai sua posição
• =INDEX(Table2[TYPE],MAX(...)) - tipo da lista

Atualizar

Acima da fórmula refere-se a tabelas, para converter seus intervalos em tabelas, selecione: Inserir - Tabela (no exemplo table1 é o do lado esquerdo, então converta primeiro)

Fórmula com referências padrão:
=INDEX(G$2:G$7,MAX((ROW(F$2:F$7)-1)*SIGN(IFERROR(SEARCH(F$2:F$7,A3),0))))