Verificando se existe um rótulo entre dois outros rótulos em uma coluna

Question

Eu tenho uma coluna (chamada A) no Excel lista itens diferentes e suas propriedades. Cada item tem a palavra "Tipo" associada a ele e, portanto, há uma célula com esse rótulo na coluna A, de modo que, na coluna ao lado dele (B), o tipo possa ser listado. Alguns (não todos) itens também têm um rótulo "Widget" associado a eles.

Como cada item tem apenas um marcador "Tipo", eu gostaria de poder fazer uma pesquisa no Excel com o rótulo "Tipo" e verificar se há um rótulo "Widget" ANTES da próxima etiqueta "Tipo". e se houver colar o valor na coluna B adjacente ao rótulo "Tipo" em outra planilha. Ele continuaria fazendo isso até que mais nenhuma ocorrência da palavra "Tipo" fosse encontrada. Isso é algo viável para implementar? Para tornar o layout mais claro, uma coluna de amostra está abaixo. Observe que o número de espaços entre cada entrada, infelizmente, não é consistente e, portanto, não pode ser usado como uma maneira de rastrear onde está. Da mesma forma, a palavra Widget não precisa estar diretamente acima de Tipo, ela pode aparecer em qualquer lugar entre as duas instâncias de "Tipo" que a sustentam.

**Type**  01
Width     .5
Length    .4
Height    .3
Weight    15
Widget    Blue
**Type**  072
Width      .1 
Length     .1
Height     .1
Weight     50
**Type**   025
Width      .4
Length     .4
Widget     Red
Weight     11
Height     .4

A saída desejada na folha separada seria simplesmente:

01
025

pois esses são os tipos que possuem um widget associado.

    

Answer 1

Fórmula atualizada. Encontra a célula com o primeiro tipo e segundo tipo e procura o widget de palavras entre elas. Se existir, retorne o valor ao lado do tipo, se não retornar em branco.

=IFERROR(IF(MATCH("widget",INDIRECT("A" & SMALL(IF(A:A="type",ROW(A:A),1000000),E1) & ":A" &                
SMALL(IF(A:A="type",ROW(A:A),1000000),E1+1)),0)>0,INDIRECT("B" & 
SMALL(IF(A:A="type",ROW(A:A),1000000),E1)),""),"")

Inserido com ctrl + shift + enter

A célula E1 teria o número 1, E2 teria o número 2. Copie a fórmula para baixo para retornar cada instância do WIDGET. Será erro se não houver mais, então você pode usar um iferror em torno dele.

Provavelmente demoraria um pouco para calcular (ou meu comp é lento apenas por fazer outras coisas agora). e você pode estar melhor usando uma macro.

Abaixo está uma tentativa muito rápida. Ele os lista de baixo para cima. Basicamente, ele passa da última linha para a primeira. Se encontrar um widget, ele ativará uma bandeira. Se o sinalizador estiver ativado e encontrar um tipo, ele retornará a célula ao lado dele.

* Editou a macro para trabalhar com o tipo ou widget encontrado na string e não apenas com uma correspondência exata

Sub get_types()

Dim lRow As Long, cRow As Long, nRow As Long
Dim FindType As Boolean

FindType = False
nRow = 2

With Sheets("sheet1")
    lRow = .Range("A" & .Rows.Count).End(xlUp).Row

    For cRow = lRow To 1 Step -1
        If InStr(UCase(.Range("A" & cRow).Value), "WIDGET") > 0 Then FindType = True
        If FindType And InStr(UCase(.Range("A" & cRow).Value), "TYPE") > 0 Then
            .Range("E" & nRow).Value = .Range("B" & cRow).Value
            FindType = False
            nRow = nRow + 1
        End If
    Next
End With

End Sub

Saída, células azuis devem ser listadas, células vermelhas são ignoradas porque não possuem um widget. Células listadas na ordem inversa devido à direção do loop.

    

Answer 2

Você pode fazer isso com uma instrução IF AND, como:

=IF(AND(A1="Widget",A2="**Type**"),B2,"")

Isso basicamente informa ao Excel se o texto "Widget" estiver em uma célula acima de uma célula com o texto "** Tipo **", insira os dados da célula na coluna B ao lado de "** Tipo **", caso contrário, deixe em branco.

Você acaba com isso:

Sevocêdesejaqueosdados(072nesteexemplo)apareçamemoutraplanilha,suafórmulaprecisasereferiràscélulasemsuaplanilhaespecífica.Supondoquevocêtenhaosdadosemumaplanilhachamada"Folha 1", use esta fórmula na planilha onde deseja que os dados apareçam:

=IF(AND(Sheet1!A2="Widget",Sheet1!A3="**Type**"),Sheet1!B3,"")

    

Answer 3

Aqui está minha opinião sobre seus dados e requisitos de amostra. Observe que eu expandi seus dados de amostra para demonstrar como vários cenários são tratados.

AsfórmulaspadrãoemD2:G2são,

D2é=IF(COUNTIF(A:A,"Widget") > COUNTIF (D $ 1: D1, "Widget"), "Widget", "") '

E2 é =IF(LEN($D2),INDEX($B$1:$B$9999,SMALL(INDEX(ROW($1:$9999)+($A$1:$A$9999<>"widget")*1E+99,,),ROW(1:1))),"")

F2 é =IF(LEN($D2),INDEX($B$1:$B$9999,SMALL(INDEX(ROW($1:$9999)+($A$1:$A$9999<>"**Type**")*1E+99,,),COUNTIF(INDIRECT("A1:A"&SMALL(INDEX(ROW($1:$9999)+($A$1:$A$9999<>"widget")*1E+99,,),ROW(1:1))),"**Type**"))),"")

G2 é =IF(LEN($D2),VLOOKUP("**Type**", INDIRECT("A"&SMALL(INDEX(ROW($1:$9999)+($A$1:$A$9999<>"widget")*1E+99,,),ROW(1:1))&":B9999"), 2, FALSE),"")

Reduzi os intervalos de células referenciados das referências de colunas completas para minimizar o atraso no cálculo. Ambos INDIRECT e INDEX (em sua forma de matriz aqui) são considerados voláteis funções que recalculam sempre que ocorre um ciclo de cálculo; não apenas quando um valor muda que os afeta diretamente. Preencha conforme necessário.

Eu disponibilizei essa pasta de trabalho de amostra para você no meu OneDrive aqui para referência e download.

Widget_types.xlsx

Isso provavelmente vai mantê-lo ocupado por um tempo ao transcrever as fórmulas para seus próprios propósitos. Poste de volta com qualquer específico perguntas que você tem se você tiver um problema.