Este fórum está sendo desativado

[SandroLima]

Boa noite, Jimmy

Obrigado pela solução apresentada.

Ainda não estudei seu código mas de antemão gostaria de lhe perguntar se é possível incluir no código para que os dados extraídos ficassem em ordem com na coluna [Resultado Desejado]

E a questão do tempo de execução da macro? Ainda é uma preocupação quando a base de dados ficar maior?

Tentei bolar uma alternativa puramente com fórmulas... mas me atrapalhei na coluna apontada pela Seta Azul.
Uso essa fórmula para extrair dados únicos em ordem crescente de uma coluna... mas aqui nessa planilha não funcionou adequadamente.

Fiz a seguinte sequencia:
- Extrai as datas que obedeciam os critérios
- Extrai os itens que obedeciam os critérios
- Concatenei os valores das duas colunas
- o próximo passo, a coluna [Itens concatenados únicos], apontada pela Seta Azul foi onde a fórmula não funcionou... deveria trazer os resultados únicos ordenados mas sem espaços vazios.
E a contagem dos valors deveria resultar em 11 e não em 13 como está na célula F32.

Fora esse ajuste dessa última fórmula oque vc acha dessa alternativa.?
Pergunto em relação ao tempo de execução da sua Macro.

Segue a planilha para vc conferir

[Jimmy]

Bom dia Sandro,

Vou ver esta sua última planilha ainda pela manhã, mas enquanto isso te pergunto. Ficaria ruim pra você que se a geração da lista não acontecesse automaticamente, ou seja, que ocorresse ao clicar em um botão?

Se esta mensagem colabora para a solução do problema, peço que dê um Like, clicando no botão com o "positivo" existente acima e a direita de cada mensagem.

Jimmy San Juan

[SandroLima]

Boa noite, Jimmy.

Acho que poderia sim.

Talvez um botão do tipo "Gerar Relatório" com uma inputbox para selecionar o ano e mês desejado e na sequência o restante da macro.

Não vejo problemas... mas pq? Vc acha melhor assim?

[Jimmy]

Bom dia Sandro,

Sim, acho melhor. Vou te passar o quadro.

Sobre as function, não temos controle sobre quando devem ser executadas. Teoricamente deveriam ser apenas quando há mudanças nas células das quais elas são dependentes, mas podem haver mudanças nos dados do Excel, que não impactariam no resultado das células que contém as functions, mas que fazem com que as funcions sejam recalculadas mesmo assim. Se projetarmos que serão 1000 linhas, e que cada uma dessas funções tratará das 1000 linhas, e que em cada uma dessas linhas serão processadas 6 colunas (não mais 4) temos uma quantidade de processamento que pode preocupar. A cada data alterada na tabela de dados, todo esse processamento seria repetido.

Não sei ao certo se isso será um problema ou não. Teríamos que testar com 1000 linhas, distintas o suficiente para simular uma quantidade real de não repetidos. Simplesmente duplicar o mesmo bloco X vezes não dará o resultado real pois a quantidade de não repetidos seria bem pequena, uma vez que os dados foram duplicados.

Existem recursos que podem ser usados para diminuir a carga de processamento. Exemplo: a primeira das functions varre a lista toda, deixar apenas os não repetidos, e coloca o resultado numa variável (vetor) não volátil. Isso permitiria que as outras 999 functions não precisassem executar tudo isso, pegando apenas o valor daquele vetor. Ocorre que quando uma alteração é feita nos dados, e todas as functions devam ser recalculadas, a ordem em que isso acontece não segue a ordem de linha ou de coluna; segue uma ordem fora de nosso controle. A function do quinto elemento pode ser executada antes do que a function do 1º elemento, e assim, o resultado do 5º elemento ainda não teria o vetor atualizado montado, pois quem o montaria ainda não rodou. Pode-se então recalcular todas elas sempre que o 1º elemento for calculado, mas ai começamos a inserir uma série de controles, aumentando o nível de complexidade, que vai ficando mais caro o molho do que a salada.

Se você achar que atrapalha ter um botão para a execução de uma SUB manualmente, então te mando uma última versão da macro, você gera uma massa de testes com mais de mil linhas, e testamos. Se o tempo de resposta atender, encerramos o caso. Se não atender passamos para a SUB mesmo.

A fórmula para obter a lista de não repetidos é possível. Uma delas seria esta:
=SEERRO(ÍNDICE($E$34:$E$63;CORRESP(0;CONT.SE($F$33:F33;$E$34:$E$63);0);1);"") (CORRIGIDA)
matricial, logo, deve ser encerrada com CTRL+SHFT+ENTER.

Ela não dá a possibilidade de classificação, porque quando a linha muda de lugar, seus ranges também mudam, e o resultado também muda, e assim não ficam classificados. Veja o caso da tua coluna CONCATENADO. Todas as linhas tem a fórmula =CONCATENAR(TEXTO([@Data];"dd/mm/aaaa");" ";[@Item]), logo, se classificar e as linhas forem mudadas de ordem, o resultado só pode ser o mesmo, porque são todas iguais!

Para classificar isso teríamos que desenvolver uma fórmula envolvendo não só INDICE, CORRESP, CONT.SE, etc, mas também MAIOR, MENOR, DESLOC, e sei lá quais mais. Sinceramente, acho muito mais prático fazer uma SUB, do que desenvolver uma fórmula quilométrica, de difícil compreensão e manutenção.

Esse é o quadro.

Quer que te mande a última versão (com classificação) pra você testar com mais de 1000 linhas? Ou partimos pra SUB com botão?

Jimmy San Juan

[SandroLima]

Bom dia, Jimmy.

Acho que então partimos para a Sub com o botão.

Obrigado pela explanação sobre a function.

[Jimmy]

Ok Sandro,

Como estamos no final de semana, proponho esperarmos até segunda-feira, porque existe a possibilidade de alguém responder o seu outro post, e apresentar uma solução satisfatória por fórmula.

Bom final de semana,

Jimmy San Juan

[Jimmy]

Esqueci de anexar novamente...

Segue a planilha do seu outro post, com a solução.

[Jimmy]

Boa tarde Sandro,

Demorou mas saiu!

Segue anexa a planilha para seus testes. O Botão de acionamento da macro está na célula G2 da planilha CONFIG.

Não creio que teremos problemas de performance dessa forma que está agora, mas caso tenhamos, ainda é possível melhorar bastante a performance. Gastaríamos mais de memória, mas rodaria mais rápido. Vamos deixar essa carta na manga pra usar apenas em caso de necessidade.

Se esta mensagem colabora para a solução do problema, peço que dê um Like, clicando no botão com o "positivo" existente acima e a direita de cada mensagem.

Jimmy San Juan

[SandroLima]

Boa noite, Forumeiros.

Boa noite, Jimmy.

Só agora pude testar o código... e funcionou perfeitamente.

Entendi quase todo o código exceto pelas seguintes partes:

Código: Selecionar todos

SLi.Item(

da linha

Código: Selecionar todos

SLi.Item(Dat.DataBodyRange(Idx).Value & " " & _
                    Ite.DataBodyRange(Idx).Value) = ""

E as seguintes partes também:

Código: Selecionar todos

To SLi.Count - 1

e

Código: Selecionar todos

SLi.getkey(Idx)

Código: Selecionar todos

For Idx = 0 To SLi.Count - 1
        Uni.DataBodyRange.Range("A" & Idx + 1).Value = SLi.getkey(Idx)

Desde já meu muito obrigado e tenha uma semana abençoada.

[Jimmy]

Bom dia Sandro,

Pensei que estivesse de férias...

Entendi quase todo o código ...

Fico muito satisfeito com isso. O progresso de seu conhecimento é de se notar. Parabéns!!

...exceto pelas seguintes partes:

Terei prazem em explicar.

Criei SLi como sendo um objeto SortedList, que é um tipo de coleção.

Imagine que sua secretária Maria organiza sua agenda de telefones. Você passa pra ela o nome e o telefone, e ela registra isso. O nome vamos chamar de "Chave" porque é por ele que você chaga ao telefone. Não pode haver duas chaves iguais. O telefone em si vamos chamar de Valor.

SLi.Item("João") = "(11) 98765-4321" significa: Maria, coloque o telefone tal, associado à chave João.
SLi.Item("João") = "" significa, Maria: abra um registro para João, mas o telefone deixe em branco.
similar a: SLi.Item(Dat.DataBodyRange(Idx).Value & " " & Ite.DataBodyRange(Idx).Value) = ""

Como Maria é organizada, sempre que você pedir pra ela inserir um nome, ela vai inserir sempre na posição que faça com que as chaves fiquem em ordem alfabética. Por isso do nome SortedList.

Como Maria também é inteligente, antes de inserir um nome ela verifica se ele já existe. Se já existir, apenas o atualiza com o novo valor. Se não existe, então sim é acrescentado ao catálogo telefônico.

SLi.Count significa: Maria, quantos nomes temos registrados?
Para a macro percorrer os nomes, como eles iniciam na posição zero, vão até a quantidade de nomes menos 1.
Se há 10 nomes, vão da posição zero até a 9.

SLi.getkey(4) Significa: Maria, qual é o nome do quinto (lembre-se que inicia em zero) registro de telefones?

For Idx = 0 To SLi.Count - 1 Percorre todas as posições dos telefones armazenados, desde o zero até o último.

No seu caso, a SortedList foi usado para extrair vantagem das características da SortedList estar sempre classificado, e não aceitar chaves duplicadas, uma vez que na sua planilha te interessava apenas um "espécime" de cada par. Como só me interessava isso, não coloquei nenhum valor para cada chave (= "").

Em linhas gerais: leio tudo o que interessa e sei que há diversos valores repetidos. Coloco tudo la SortedList, e sei que os repetidos vão virando um só, pois quando um repetido é inserido, ele substitui o anterior. Depois percorro cada elemento da SL jogando de volta na planilha, sabendo que os repetidos se foram, e que o que virá estará classificado.

Poderíamos tem feito de outras formas, como por exemplo, criando um vetor (matriz ou Array). Poderíamos varrer o vetor antes de incluir algo, pra ver se já existe. No final poderíamos jogar esse vetor numa range da planilha, e mandar classificar pelo Excel, etc... mas pra que fazer tudo isso se temos a nossa querida Maria? kkk

Se tiver dúvida, pergunte.

Nas mensagens que colaboram para a solução do problema, peço que dê um Like, clicando no botão com o "positivo existente acima e a direita de cada mensagem.

Jimmy San Juan

[SandroLima]

Boa tarde, Jimmy.

Fico muito satisfeito com isso. O progresso de seu conhecimento é de se notar. Parabéns!!

Muito obrigado...

E obrigado pelo exemplo prático da eficiente e organizada "Maria"... rsrsrs.

Tenha uma excelente semana!