Macro tipo Filtro Avançado - recortar dados

alison.rm · 21 de novembro de 2014

Olá!

Gostaria de ajuda para criar a seguinte macro, parecida com Filtro Avançado: filtrar (limpar!) as colunas baseados em algumas palavras (critérios).

A Planilha do Excel tem 3 colunas: DESCRITIVO - VENDAS - PREÇO

Eu tenho que imprimir essa planilha, mas muitos dos DESCRITIVOS são irrelevantes (metade dos itens não preciso imprimir).

Então eu usei o Filtro Avançado, mas ele filtra o que tem, não o que não tem... Ex: gostaria que, se em DESCRITIVO tivesse a palavra SALAME, ou CALABRESA ou SALSICHA ele recortasse essas linhas pra outro lugar (pode ser na própria planilha).

Queria que recortasse porque dou uma analisada pra ver se tudo está ok pra depois imprimir, isso porque, por exemplo, ele vai recortar (limpar) se houver a palavra CALABRESA, mas salgadinho e biscoito sabor calabresa não podem ser excluído. Então eu teria duas tabelas, uma "limpa" e uma "suja".

Não sei se ficou claro.

Estou mandando uma planilha (resumida) pra olharem.

Desde já muito obrigado!

ARQUIVO DE EXEMPLO.rar

DJunqueira · 22 de novembro de 2014

você poderia resolver isso com tabela dinâmica.

Primeiro transforme seus dados em uma Tabela reconhecida pelo Excel, isso vai ajudar quando você inserir novos dados e for olhar o resumo na Tabela Dinâmica.

Após transformar seus dados em Tabela e com toda ela marcada insira tabela dinâmica, inclua as colunas q você quer e desmarque os itens q você não quer mais ver neste Resumo1, repita o processo p/ criar outra tabela dinâmica e assim concentrar nela os itens q você raramente quer dar conta - Resumo2.

Depois é só atualizar, sem programação ou macro.

alison.rm · 22 de novembro de 2014

Cara, não consegui, mas acho que nesse caso vou ter que inserir os termos que quero filtrar manualmente. São 183 termos!

Estou usando o Filtro Avançado por enquanto. Eu jogo o filtro e ele me retorna nos valores desses 183 termos. Isso será descartado, lixo. Estou fazendo assim:

Uso o Filtro Avançado pra filtrar esses 183 termo.
Coloco de volta na mesma planilha (vai ficar duplicado!)
Uso a Formatação Condicional para valores duplicados (os duplicados ficam com cor)
Uso o filtro de cor.
Deleto todos eles (cor cor/duplicado)

Daí fico com as duas tabelas, uma suja (a do Filtro Avançado) e outra limpa para impressão.

Dá um pouco de trabalho, mas está resolvendo.

DJunqueira · 22 de novembro de 2014

Muito bem, estou te enviando o arquivo com minha solução que gera menos trabalho.

Se os termos são tão numerosos (183) você pode criar mais uma coluna (Nomeie de Filtro...) com os termos q você não quer e depois na tabela dinâmica você faz um comando simples p/ q todos eles não apareçam (Resumo1) ou apareçam (Resumo2). Fazendo assim você não terá q lidar com termos duplicados e nem vai alterar a tabela original.

Para a coluna sugerida Filtro você pode inserir a função

=SE(ÉERRO(PROCV(ProdutoExcluido;ListaProdutosExcluidos;0));"";"X")

Essa solução é melhor q a sua, não dá trabalho e é automatizada.

QQ dificuldade mande outra mensagem.

ARQUIVO DE EXEMPLO.rar

alison.rm · 22 de novembro de 2014

DJunqueira, parece que você me enviou o mesmo arquivo que eu tinha postado, ainda não entendi como funciona a Tabela Dinâmica.

Estou enviando um arquivo mais completo, o que usei o Filtro Avançado, ficaram vária abas pra você ver as várias etapas que percorri. Nela tem uma planilha com os termos que eu quero que não entre (sujo)...

Desde já obrigado pela atenção.

LISTAGEM LIMPAR.rar

22 de novembro de 2014

@Alison

Se entendi corretamente você quer extrair da lista DESC que está na planilha "ORIGINAL" os registros que não contenham qualquer dos "termos excluídos" que estão listados na planilha "TERMOS EXCLUIOS".

Se for isso e você quiser experimentar extrair a lista ao clicar em um botão, instale o código abaixo em um módulo comum, assim:
1. copie o código daqui
2. a partir da planilha em que estão os dados tecle 'Alt+F11' para acessar o editor de VBA
3. no menu do editor >> Inserir >> Módulo
4. cole o código na janela em branco que vai se abrir
5. feito! 'Alt+Q' para retornar para a planilha e testar

para rodar o código:
6. tecle 'Alt+F8' >> selecione a macro correspondente >> Executar, ou insira um botão ou uma figura em qualquer planilha da arquivo e vincule-o(a) à macro ou vincule a macro a um atalho de teclado (Alt+F8 > Opções).

Em seguida, e antes de rodar o código pela primeira vez, cole a lista dos "termos excluídos" na planilha "ORIGINAL" a partir de "J1"

funcionamento - o código irá lançar a lista na planilha "FINAL_LIMPO" a partir de "F2"

Sub ExtraiRegistros() Dim reg() As String, LRj As Long, LRa As Long, LRd As Long, i As Long, k As Long, FLG As Boolean  Sheets("FINAL_LIMPO").Columns("F:H").ClearContents  With Sheets("ORIGINAL")   LRj = .Cells(Rows.Count, 10).End(xlUp).Row: LRa = .Cells(Rows.Count, 1).End(xlUp).Row    For k = 2 To LRa     reg = Split(.Cells(k, 1))       For i = LBound(reg) To UBound(reg)         If Application.CountIf(.Range("J1:J" & LRj), reg(i)) > 0 Then          FLG = True          Exit For         End If       Next i       If FLG = False Then        LRd = Sheets("FINAL_LIMPO").Cells(Rows.Count, 6).End(xlUp).Row        Sheets("FINAL_LIMPO").Cells(LRd + 1, 6).Resize(, 3).Value = .Cells(k, 1).Resize(, 3).Value       Else: FLG = False       End If    Next k  End WithEnd Sub

DJunqueira · 23 de novembro de 2014

Acabei encontrando uma solução ainda melhor

Criei uma coluna chamada Filtro, inseri uma função q varre cada item da tabela a procura dos itens excluídos (tinham itens repetidos nos itens excluídos...). Com isso você pode alternar entre os itens depurados e não depurados na própria tabela! É só selecionar o número 0 na coluna do filtro p/ ter a tabela filtrada. Ficou muito bom, hehe.

A tabela q eu enviei foi criada em Excel 2010, mas qq problema você pode utilizar a fórmula no Excel mais antigo q terá o mesmo resultado.

Dê retorno, Ok

Segundo arquivo com Excel 2003.xls

LISTAGEM LIMPAR.rar

LISTAGEM LIMPAR Solucionado Excel 2003.rar

alison.rm · 23 de novembro de 2014

@Alison

Se entendi corretamente você quer extrair da lista DESC que está na planilha "ORIGINAL" os registros que não contenham qualquer dos "termos excluídos" que estão listados na planilha "TERMOS EXCLUIOS".

Se for isso e você quiser experimentar extrair a lista ao clicar em um botão, instale o código abaixo em um módulo comum, assim:

1. copie o código daqui

2. a partir da planilha em que estão os dados tecle 'Alt+F11' para acessar o editor de VBA

3. no menu do editor >> Inserir >> Módulo

4. cole o código na janela em branco que vai se abrir

5. feito! 'Alt+Q' para retornar para a planilha e testar

para rodar o código:

6. tecle 'Alt+F8' >> selecione a macro correspondente >> Executar, ou insira um botão ou uma figura em qualquer planilha da arquivo e vincule-o(a) à macro ou vincule a macro a um atalho de teclado (Alt+F8 > Opções).

Em seguida, e antes de rodar o código pela primeira vez, cole a lista dos "termos excluídos" na planilha "ORIGINAL" a partir de "J1"

funcionamento - o código irá lançar a lista na planilha "FINAL_LIMPO" a partir de "F2"
Sub ExtraiRegistros() Dim reg() As String, LRj As Long, LRa As Long, LRd As Long, i As Long, k As Long, FLG As Boolean  Sheets("FINAL_LIMPO").Columns("F:H").ClearContents  With Sheets("ORIGINAL")   LRj = .Cells(Rows.Count, 10).End(xlUp).Row: LRa = .Cells(Rows.Count, 1).End(xlUp).Row    For k = 2 To LRa     reg = Split(.Cells(k, 1))       For i = LBound(reg) To UBound(reg)         If Application.CountIf(.Range("J1:J" & LRj), reg(i)) > 0 Then          FLG = True          Exit For         End If       Next i       If FLG = False Then        LRd = Sheets("FINAL_LIMPO").Cells(Rows.Count, 6).End(xlUp).Row        Sheets("FINAL_LIMPO").Cells(LRd + 1, 6).Resize(, 3).Value = .Cells(k, 1).Resize(, 3).Value       Else: FLG = False       End If    Next k  End WithEnd Sub

@osvaldomp, obrigado pela atenção, estava pensando em utilizar uma macro mesmo, mas eu não entendi o funcionamento dessa, dos códigos, e fico com receio de haver alguma exceção e eu não detectar. Eu vou utilizar a solução do @DJunqueira porque etendi rsrs e posso alterar qualquer coisa se for necessário, fazer um upgrade. Mas vou guardar essa sua caso precise.

Novamente obrigado!

Acabei encontrando uma solução ainda melhor

Criei uma coluna chamada Filtro, inseri uma função q varre cada item da tabela a procura dos itens excluídos (tinham itens repetidos nos itens excluídos...). Com isso você pode alternar entre os itens depurados e não depurados na própria tabela! É só selecionar o número 0 na coluna do filtro p/ ter a tabela filtrada. Ficou muito bom, hehe.

A tabela q eu enviei foi criada em Excel 2010, mas qq problema você pode utilizar a fórmula no Excel mais antigo q terá o mesmo resultado.

Dê retorno, Ok

Segundo arquivo com Excel 2003.xls

@DJunquera, exatamente, era isso que eu precisava.

Se no Filtro a linha for diferente de "0", ele é um item excluído, procede? Depois é só copiar os itens "0" e copiar para a planilha LIMPA e os demais para SUJA.

Eu só não entendi uma coisa na fórmula localizar...

...LOCALIZAR('TERMOS EXCLUIOS'!$A$2:$A$181;A2)))

Porque que no final tem que ter A2, A3, A4...? Parece que ela tá invertida rs.

A leitura da fórmula seria assim "Some a quantidade de produtos localizados em TERMOS EXCLUSIVOS que tenha em A2"?

Att

DJunqueira · 23 de novembro de 2014

@osvaldomp, obrigado pela atenção, estava pensando em utilizar uma macro mesmo, mas eu não entendi o funcionamento dessa, dos códigos, e fico com receio de haver alguma exceção e eu não detectar. Eu vou utilizar a solução do @DJunqueira porque etendi rsrs e posso alterar qualquer coisa se for necessário, fazer um upgrade. Mas vou guardar essa sua caso precise.

Novamente obrigado!

@DJunquera, exatamente, era isso que eu precisava.

Se no Filtro a linha for diferente de "0", ele é um item excluído, procede? Depois é só copiar os itens "0" e copiar para a planilha LIMPA e os demais para SUJA.

Eu só não entendi uma coisa na fórmula localizar...

...LOCALIZAR('TERMOS EXCLUIOS'!$A$2:$A$181;A2)))

Porque que no final tem que ter A2, A3, A4...? Parece que ela tá invertida rs.

A leitura da fórmula seria assim "Some a quantidade de produtos localizados em TERMOS EXCLUSIVOS que tenha em A2"?

Att

Boa noite Alison

Isso, é ficar variando entre 0 selecionado ou não. Eu nem copiaria p/ outra planilha, pois você pode imprimir direto, quanto menos trabalho melhor.

A fórmula faz uma varredura em cada item da coluna A p/ ver se localiza algum termo excluído, se encontrar soma um 'ponto', alguns itens chegaram a ficar com 4 'pontos'.

Ok, fico feliz por ter ajudado.

Abç