Excel Localizar valor com cruzamento de intervalo entre linhas e colunas

[Natalia Benassi]

Boa tarde!

Será que alguém pode me ajudar?

 

Tenho uma planilha onde constam 3 valores diferentes para 1 única célula correspondente. Ex.:

A            B        C       D

123     456     789     Arquivo: x

135     790      497    Arquivo: y

 

 

Em outras 3 em que os valores estão em lista, mas sem os dados da coluna D. Exemplo:

  A       B

123     Arquivo: x

456     

789     

135    

 

Como faço esse cruzamento de dados, sem precisar fazer coluna por coluna?

Obrigada

[Patropi]

Boa noite< @Natalia Benassi

 

Experimentei assim:

 

=PROC(A4;$A$1:$C$2;$D$1:$D$2)

 

[]s

[Natalia Benassi]

13 horas atrás, Patropi disse:

Boa noite< @Natalia Benassi

 

Experimentei assim:

 

=PROC(A4;$A$1:$C$2;$D$1:$D$2)

 

[]s

Bom Dia Patropi!

Muito obrigada pela resposta, mas a função retorna com 0. 

Eu anexei uma planilha como exemplo.

Fiz uma função nela, usando Índice e Corresp. O problema é que tenho que especificar a coluna ou a linha onde está o pedido que estou procurando, mas na minha planilha original tem 2 mil linhas e 12 colunas com as ID. 

Eu até pensei em fazer 12 vezes a mesma função. Mas além de ficar gigante, eu não sei fazer fórmula com condição.

Será que existe alguma forma de fazer isso? Mesmo que a função fique grande, não tem problema. Eu só preciso fazer de uma forma que não seja manual rsrs

 

Obrigada

Localizar pedido.xlsx

[Patropi]

@Natalia Benassi  Bom dia!

 

Copie e cole em B2 da planilha Atualizar e arraste para as demais linhas da coluna.

=SEERRO(PROCV(A2;Base!$A$2:$D$2;4;0);SEERRO(PROCV(A2;Base!$B$2:$D$2;3;0);SEERRO(PROCV(A2;Base!$C$2:$D$2;2;0);SEERRO(PROCV(A2;Base!$A$3:$D$3;4;0);SEERRO(PROCV(A2;Base!$B$3:$D$3;3;0);SEERRO(PROCV(A2;Base!$C$3:$D$3;2;0);""))))))

 

[]s

[Natalia Benassi]

Em 09/09/2021 às 20:02, Patropi disse:

Boa noite< @Natalia Benassi

 

Experimentei assim:

 

=PROC(A4;$A$1:$C$2;$D$1:$D$2)

 

[]s

Bom Dia Patropi!

Muito obrigada pela resposta, mas a função retorna com 0. 

Eu anexei uma planilha como exemplo.

Fiz uma função nela, usando Índice e Corresp. O problema é que tenho que especificar a coluna ou a linha onde está o pedido que estou procurando, mas na minha planilha original tem 2 mil linhas e 12 colunas com as ID. 

Eu até pensei em fazer 12 vezes a mesma função. Mas além de ficar gigante, eu não sei fazer fórmula com condição.

Será que existe alguma forma de fazer isso? Mesmo que a função fique grande, não tem problema. Eu só preciso fazer de uma forma que não seja manual rsrs

 

Obrigada

Em 09/09/2021 às 20:02, Patropi disse:

Boa noite< @Natalia Benassi

 

Experimentei assim:

 

=PROC(A4;$A$1:$C$2;$D$1:$D$2)

 

[]s

Bom Dia Patropi!

Muito obrigada pela resposta, mas a função retorna com 0. 

Eu anexei uma planilha como exemplo.

Fiz uma função nela, usando Índice e Corresp. O problema é que tenho que especificar a coluna ou a linha onde está o pedido que estou procurando, mas na minha planilha original tem 2 mil linhas e 12 colunas com as ID. 

Eu até pensei em fazer 12 vezes a mesma função. Mas além de ficar gigante, eu não sei fazer fórmula com condição.

Será que existe alguma forma de fazer isso? Mesmo que a função fique grande, não tem problema. Eu só preciso fazer de uma forma que não seja manual rsrs

 

Obrigada

 

 

 

[Midori]

@Natalia Benassi Acho mais fácil com macro, veja se assim resolve,

 

Function ProcuraPedido(Valor As Range, Area As Range) As String
    Dim Celula As Range
    
    If Valor = "" Then Exit Function
    
    Set Celula = _
        Area.Find(Valor.Value, LookIn:=xlValues, LookAt:=xlWhole)

    If Not Celula Is Nothing Then
        Dim Coluna As Integer
        Coluna = _
            Area.Column + Area.Columns.Count - Celula.Column
        ProcuraPedido = Celula.Cells(1, Coluna).Value
    End If
End Function

 

E na planilha é só usar a função como uma fórmula, p.ex a célula B2 da planilha Atualizar,

=ProcuraPedido(A2;Base!A:D)

 

Se tiver mais colunas é só aumentar o range da fórmula.

 

[Natalia Benassi]

Em 10/09/2021 às 11:03, Patropi disse:

@Natalia Benassi  Bom dia!

 

Copie e cole em B2 da planilha Atualizar e arraste para as demais linhas da coluna.

=SEERRO(PROCV(A2;Base!$A$2:$D$2;4;0);SEERRO(PROCV(A2;Base!$B$2:$D$2;3;0);SEERRO(PROCV(A2;Base!$C$2:$D$2;2;0);SEERRO(PROCV(A2;Base!$A$3:$D$3;4;0);SEERRO(PROCV(A2;Base!$B$3:$D$3;3;0);SEERRO(PROCV(A2;Base!$C$3:$D$3;2;0);""))))))

 

[]s

Muito obrigada pela ajuda!

Deu certo em 3 planilhas (aquelas do Google)

Mas na ultima, quando a função não encontra o numero do pedido, está retornando a procura errada.

Exemplo: estou procurando o pedido 12345. Se a função não encontra, ela retorna o nome do arquivo da A2, onde o número do pedido é 0. Mas quando a função localiza, ela retorna certo.

Quando tentei fazer pelo próprio excel (versão 2019), deu erro "você inseriu um numero excessivo de argumentos". 

Preciso procurar a célula da coluna A, 19 vezes, e se não encontrar eu preciso procurar a célula da coluna B mais 19 vezes  (porque são números diferentes para o mesmo pedido).

Eu não sei o que fazer

 

 

 

20 minutos atrás, Midori disse:

@Natalia Benassi Acho mais fácil com macro, veja se assim resolve,

 

Function ProcuraPedido(Valor As Range, Area As Range) As String
    Dim Celula As Range
    
    If Valor = "" Then Exit Function
    
    Set Celula = _
        Area.Find(Valor.Value, LookIn:=xlValues, LookAt:=xlWhole)

    If Not Celula Is Nothing Then
        Dim Coluna As Integer
        Coluna = _
            Area.Column + Area.Columns.Count - Celula.Column
        ProcuraPedido = Celula.Cells(1, Coluna).Value
    End If
End Function

 

E na planilha é só usar a função como uma fórmula, p.ex a célula B2 da planilha Atualizar,

=ProcuraPedido(A2;Base!A:D)

 

Se tiver mais colunas é só aumentar o range da fórmula.

 

Obrigada! Mas não sei como criar macro. E os tutoriais ensinam a fazer macro de repetição.

[Midori]

21 minutos atrás, Natalia Benassi disse:

Obrigada! Mas não sei como criar macro. E os tutoriais ensinam a fazer macro de repetição.

 

A macro já está completa, se tiver interesse é só copiar e colar. E não precisa de repetição, basta fazer como comentei.

[Natalia Benassi]

20 minutos atrás, Midori disse:

 

A macro já está completa, se tiver interesse é só copiar e colar. E não precisa de repetição, basta fazer como comentei.

Eu tenho interesse sim. Eu só não sei como fazer. kkk

Porque ao clicar em Macros no excel e depois em Gravar macros. Aparece uma janela com 4 campos:

Nome da macro

Tecla de atalho

Armazenar macro em:

Descrição.

 

Eu tentei colar a macro que você mandou no campo descrição, mas não vai.

[Midori]

@Natalia Benassi Siga estes passos,

 

1) Digite Alt+F11 para abrir o editor do VBA

2) Crie um módulo no menu Inserir > Módulo

3) Cole a macro

4) Volte na planilha para usar a fórmula (é o mesmo nome da macro/função)

 

[Natalia Benassi]

53 minutos atrás, Midori disse:

@Natalia Benassi Siga estes passos,

 

1) Digite Alt+F11 para abrir o editor do VBA

2) Crie um módulo no menu Inserir > Módulo

3) Cole a macro

4) Volte na planilha para usar a fórmula (é o mesmo nome da macro/função)

 

Eu tentei, mas não deu certo.

Anexei uma cópia da planilha real para você ver como é, e tentar me ajudar.

Tem a aba onde consta os números dos pedidos e seus respectivos arquivos (Aba Recebimento de Pedidos). Tem bastante coluna porque um único arquivo pode corresponder até 19 pedidos.

Todas as outras abas, os pedidos são individuais, porém com numero de reenvio. Exemplo: na aba Procon PT  na A2 e B2 estão os pedidos 7445862 e 38894556 preciso procurar os dois, pois são um único pedido, e preciso saber se o cliente recebeu algum dos dois.

Eu deixei de verde os títulos dos pedidos e de amarelo a imagem.

 

Após procurar todos os pedidos e incluir nas outras abas, vou precisar fazer o reverso. Informar na coluna (Não tem na planilha - da aba Recebimento de Pedidos) qual pedido não foi encontrado em nenhuma aba. 

 

Será que você consegue me ajudar ?

 

1023970331_Pedidos2.xlsx

[Midori]

@Natalia Benassi O que não deu certo? Conseguiu colar a macro no módulo e usar a fórmula?

 

Na planilha que anexou não tem a fórmula e nem a macro que postei.

 

[Natalia Benassi]

53 minutos atrás, Midori disse:

@Natalia Benassi O que não deu certo? Conseguiu colar a macro no módulo e usar a fórmula?

 

Na planilha que anexou não tem a fórmula e nem a macro que postei.

 

desculpa. Segue novamente. porém eu desabilitei a macro porque o site não deixa anexar o arquivo com a macro ativa

1 hora atrás, Midori disse:

@Natalia Benassi O que não deu certo? Conseguiu colar a macro no módulo e usar a fórmula?

 

Na planilha que anexou não tem a fórmula e nem a macro que postei.

 

Quando eu fiz, ficou tudo em branco. Nem erro não deu.

[Patropi]

@Natalia Benassi

 

O fórum aceita arquivos com macro, mas desde que estejam compactados. (Zipados)

 

[]s

[Natalia Benassi]

12 minutos atrás, Patropi disse:

@Natalia Benassi

 

O fórum aceita arquivos com macro, mas desde que estejam compactados. (Zipados)

 

[]s

 

57 minutos atrás, Natalia Benassi disse:

desculpa. Segue novamente. porém eu desabilitei a macro porque o site não deixa anexar o arquivo com a macro ativa

Quando eu fiz, ficou tudo em branco. Nem erro não deu.

Não está indo não. Só aparece um botão "inserir outra midia" e nas opções tem "inserir anexo existente" e "inserir imagem através de uma url"

 

[Patropi]

@Natalia Benassi

 

Você compactou o arquivo como falei na mensagem anterior?

Se compactar o fórum aceita.

 

[]s

[Natalia Benassi]

7 minutos atrás, Patropi disse:

@Natalia Benassi

 

Você compactou o arquivo como falei na mensagem anterior?

Se compactar o fórum aceita.

 

[]s

sim. Eu compactei tanto em zip quanto em rar. Mas nem imagem está indo. Eu queria mandar um print, mas também não vai 

 

[Midori]

@Natalia Benassi Na sua planilha fiz como comentei nos passos e consegui retornar os links das imagens.

 

Na célula I2 da planilha Procon PT coloque a fórmula e arraste,

 

=ProcuraPedido(B2;'Recebimento de Pedidos'!D:W)

 

Assim vai retornar os links da planilha Recebimento de Pedidos

[Natalia Benassi]

6 horas atrás, Midori disse:

@Natalia Benassi Na sua planilha fiz como comentei nos passos e consegui retornar os links das imagens.

 

Na célula I2 da planilha Procon PT coloque a fórmula e arraste,

 

=ProcuraPedido(B2;'Recebimento de Pedidos'!D:W)

 

Assim vai retornar os links da planilha Recebimento de Pedidos

agora deu certo.. muito obrigada!!

Na verdade está dando certo... porque ficou muito pesado, e ainda faltam encontrar alguns pedidos.

Está demorando 10 minutos para executar 1% da fórmula. Eu a inseri nas 4 abas que preciso dela.

Será que é isso que está pesando?

Será que se eu separar em arquivos diferentes fica mais leve?

outra pergunta: esse "calculando" é para todas as abas, ou só para a aba aberta?

 

[Natalia Benassi]

22 horas atrás, Midori disse:

@Natalia Benassi Na sua planilha fiz como comentei nos passos e consegui retornar os links das imagens.

 

Na célula I2 da planilha Procon PT coloque a fórmula e arraste,

 

=ProcuraPedido(B2;'Recebimento de Pedidos'!D:W)

 

Assim vai retornar os links da planilha Recebimento de Pedidos

 

22 horas atrás, Midori disse:

@Natalia Benassi Na sua planilha fiz como comentei nos passos e consegui retornar os links das imagens.

 

Na célula I2 da planilha Procon PT coloque a fórmula e arraste,

 

=ProcuraPedido(B2;'Recebimento de Pedidos'!D:W)

 

Assim vai retornar os links da planilha Recebimento de Pedidos

Bom dia!

Como estava demorando muito para calcular, eu deixei rodando a noite, mas meu PC reiniciou e agora a fórmula está  dando #nome?

Eu já refiz os passos. E a planilha está salva como habilitada para macro.

Ontem tinha dado tudo certo, faltava a ultima só. Mas agora deu isso.

[Midori]

@Natalia Benassi Se está demorando muito para calcular com essa fórmula então é melhor resolver com outra macro.

 

Não seria melhor deixar os pedidos no formato de tabela em outra aba e só com as duas colunas? Assim você só terá que usar um procv para pegar os valores. Uma macro pode pegar os dados da planilha dos pedidos e copiar no formato para outra aba assim,

 

  A          B
123 | Arquivo: x
456 | Arquivo: x
789 | Arquivo: x
135 | Arquivo: y
790 | Arquivo: y
497 | Arquivo: y

 

E nas outras planilhas você vai usar o procv para buscar os valores dessa.

[Natalia Benassi]

14 minutos atrás, Midori disse:

@Natalia Benassi Se está demorando muito para calcular com essa fórmula então é melhor resolver com outra macro.

 

Não seria melhor deixar os pedidos no formato de tabela em outra aba e só com as duas colunas? Assim você só terá que usar um procv para pegar os valores. Uma macro pode pegar os dados da planilha dos pedidos e copiar no formato para outra aba assim,

 

  A          B
123 | Arquivo: x
456 | Arquivo: x
789 | Arquivo: x
135 | Arquivo: y
790 | Arquivo: y
497 | Arquivo: y

 

E nas outras planilhas você vai usar o procv para buscar os valores dessa.

Eu pensei em fazer isso, mas a planilha de pedidos tem 9 mil linhas e é alimentada dos os dias. Eu tentei fazer isso manualmente, levaria 48 horas seguidas só copiando e colando com o programa macro recorder. kkkk

Se você tiver alguma coisa que ajude, realmente ficaria mais  fácil.

 

 

 

 

[Midori]

@Natalia Benassi Esse é o método que comentei, assim vai deixar os pedidos na forma de tabela onde você poderá fazer um procv.

 

Crie uma nova aba com o nome de Tabela (antes de buscar os pedidos a macro apaga os dados das colunas A e B dessa aba).

Sub TabelaPedidos()
    Dim Recebimento As Worksheet
    Dim Tabela      As Worksheet
    Dim Celula      As Range
    Dim L           As Long
    
    L = 1
    Set Recebimento = ThisWorkbook.Sheets("Recebimento de Pedidos")
    Set Celula = Recebimento.[D2]
    Set Tabela = ThisWorkbook.Sheets("Tabela")
    Tabela.[A:B].Clear
    
    While Celula <> ""
        Dim Link    As String
        Dim C       As Integer
        
        C = Recebimento.[W1].Column - Celula.Column
        Link = Celula.Offset(0, C)
        Call Celula.Resize(1, C).Copy        
        Call Tabela.Cells(L, 1).PasteSpecial( _
            Paste:=xlPasteValues, Transpose:=True)
        
        Tabela.Cells(L, 2).Resize(C) = Link
        
        Set Celula = Celula.Offset(1)
        L = L + C
    Wend
    Application.CutCopyMode = False
End Sub

 

Outra coisa que pode ser feita depois para deixar a busca mais rápida é remover as linhas sem pedidos.

 

[Natalia Benassi]

1 hora atrás, Midori disse:

@Natalia Benassi Esse é o método que comentei, assim vai deixar os pedidos na forma de tabela onde você poderá fazer um procv.

 

Crie uma nova aba com o nome de Tabela (antes de buscar os pedidos a macro apaga os dados das colunas A e B dessa aba).

Sub TabelaPedidos()
    Dim Recebimento As Worksheet
    Dim Tabela      As Worksheet
    Dim Celula      As Range
    Dim L           As Long
    
    L = 1
    Set Recebimento = ThisWorkbook.Sheets("Recebimento de Pedidos")
    Set Celula = Recebimento.[D2]
    Set Tabela = ThisWorkbook.Sheets("Tabela")
    Tabela.[A:B].Clear
    
    While Celula <> ""
        Dim Link    As String
        Dim C       As Integer
        
        C = Recebimento.[W1].Column - Celula.Column
        Link = Celula.Offset(0, C)
        Call Celula.Resize(1, C).Copy        
        Call Tabela.Cells(L, 1).PasteSpecial( _
            Paste:=xlPasteValues, Transpose:=True)
        
        Tabela.Cells(L, 2).Resize(C) = Link
        
        Set Celula = Celula.Offset(1)
        L = L + C
    Wend
    Application.CutCopyMode = False
End Sub

 

Outra coisa que pode ser feita depois para deixar a busca mais rápida é remover as linhas sem pedidos.

 

Eu criei a aba e inseri a formula. Mas nada aconteceu.

[Midori]

18 minutos atrás, Natalia Benassi disse:

Eu criei a aba e inseri a formula. Mas nada aconteceu.

 

Se inseriu o procv é porque deve ter rodado a macro que deixa os dados no formato para usar a fórmula.

 

E se fez como comentei não tem como não acontecer nada... O procv retorna #N/D quando não encontra um valor.