Este fórum está sendo desativado

[agentecaveira]

Tenho um planilha com diversos dados separados por colunas por especificação, precisava que estes dados fossem separados em outra planilha (Separados) sempre com um intervalo de três linhas vazias entre eles.
Não sei se é possível fazer isso através de fórmula ou apenas com VBA.

[osvaldomp]

Experimente:

Código: Selecionar todos

Sub Separados()
 Dim c As Long, LC As Long, x As Long
  Application.ScreenUpdating = False
  Sheets("Separados").Cells = ""
  With Sheets("Dados")
   LC = .Cells(1, Columns.Count).End(1).Column
   For c = 1 To LC
    If .Cells(2, c) <> "" Then
     x = .Cells(1, c).End(4).Row
     .Range(.Cells(2, c), .Cells(Rows.Count, c).End(3)).Copy Sheets("Separados").Cells(Rows.Count, 2).End(3)(5)
     Sheets("Separados").Cells(Rows.Count, 3).End(3)(5).Resize(x - 1) = .Cells(1, c)
     Sheets("Separados").Cells(Rows.Count, 1).End(3)(5) = 1
     If x > 2 Then Sheets("Separados").Cells(Rows.Count, 1).End(3).AutoFill _
      Destination:=Sheets("Separados").Cells(Rows.Count, 1).End(3).Resize(x - 1), Type:=xlFillSeries
    End If
   Next c
  End With
End Sub

#
obs. se a linha 2 estiver vazia em determinada coluna então o Tipo correspondente àquela coluna não será processado.

[agentecaveira]

Experimente:

Código: Selecionar todos

Sub Separados()
 Dim c As Long, LC As Long, x As Long
  Application.ScreenUpdating = False
  Sheets("Separados").Cells = ""
  With Sheets("Dados")
   LC = .Cells(1, Columns.Count).End(1).Column
   For c = 1 To LC
    If .Cells(2, c) <> "" Then
     x = .Cells(1, c).End(4).Row
     .Range(.Cells(2, c), .Cells(Rows.Count, c).End(3)).Copy Sheets("Separados").Cells(Rows.Count, 2).End(3)(5)
     Sheets("Separados").Cells(Rows.Count, 3).End(3)(5).Resize(x - 1) = .Cells(1, c)
     Sheets("Separados").Cells(Rows.Count, 1).End(3)(5) = 1
     If x > 2 Then Sheets("Separados").Cells(Rows.Count, 1).End(3).AutoFill _
      Destination:=Sheets("Separados").Cells(Rows.Count, 1).End(3).Resize(x - 1), Type:=xlFillSeries
    End If
   Next c
  End With
End Sub

#
obs. se a linha 2 estiver vazia em determinada coluna então o Tipo correspondente àquela coluna não será processado.

Quase deu certo, esqueci de mencionar que da Planilha Dados, os valores vem através de uma fórmula matricial, então funcionou porém veio com a fórmula, e não com os valores que a fórmula retorna.

Outra coisa, não sei se vou conseguir utilizar essa solução no trabalho, o meu computador que é u i9900KF já sofreu pra rodar essa função, imagina os i56400 que a gente trabalha.

[osvaldomp]

Outra coisa, não sei se vou conseguir utilizar essa solução no trabalho,...
Você pode aguardar, talvez outro usuário ofereça uma sugestão compatível com as limitações da sua máquina.

Para replicar valores e não fórmulas, substitua a linha abaixo
.Range(.Cells(2, c), .Cells(Rows.Count, c).End(3)).Copy Sheets("Separados").Cells(Rows.Count, 2).End(3)(5)

por estas duas
.Range(.Cells(2, c), .Cells(Rows.Count, c).End(3)).Copy
Sheets("Separados").Cells(Rows.Count, 2).End(3)(5).PasteSpecial xlValues

obs.
1. se nas células da linha 2 há fórmulas retornando vazio, então verifique e se o resultado não for o desejado retorne para ajustes
2. para responder clique em +Resposta, abaixo da última postagem, só clique em Responder com citação se necessário

[agentecaveira]

Muito Obrigado pela a ajuda, mas ainda não tenho o resultado esperado, vou enviar a Planilha para que possa ser analisada.

Eu tentei fazer de uma forma que diferencie um célula com fórmula da com dados, mas não obtive sucesso.

A minha pretensão nessa planilha era não usar VBA, mas já que foi preciso, se tiver uma forma melhor de se fazer o que é feito na Planilha "Fórmulas" em VBA, aceito sugestões.

[osvaldomp]

... mas ainda não tenho o resultado esperado,...
Sim, por conta da existência de fórmulas que você não informou antes.

A minha pretensão nessa planilha era não usar VBA, ...
Se tudo no Excel pudesse ser resolvido por fórmulas, então provavelmente não existiriam macros. E se tudo pudesse ser resolvido rápida e facilmente com macros, então provavelmente não existiriam fórmulas.
Para o resultado que você pretende, na minha opinião, pela complexidade, é caso típico para ser obtido via macro. Mas se você quiser, aguarde algum usuário do fórum lhe propor uma solução via fórmulas.
No entanto, um alerta. Na sua planilha Visualização há 1.700+ fórmulas (metade matriciais) e na planilha Fórmulas há 4.200+ fórmulas matriciais. Inserir mais fórmulas em grande quantidade no seu arquivo tenderá a tornar o recálculo lento.

... se tiver uma forma melhor de se fazer o que é feito na Planilha "Fórmulas" em VBA, aceito sugestões.
Me parece que a sua base de dados está na planilha Dados, e a partir dela você replicou dados na planilha Fórmulas e agora a partir desta última você quer replicar os dados na planilha ALA A. É isso?
Se sim, você pode alimentar a planilha ALA A diretamente da planilha Dados, sem a necessidade de uma planilha auxiliar como é a Fórmulas .
Aguardo retorno antes de fazer os ajustes no código em função da existência de fórmulas.

[agentecaveira]

Obrigado osvaldomp pela paciência.

"No entanto, um alerta. Na sua planilha Visualização há 1.700+ fórmulas (metade matriciais) e na planilha Fórmulas há 4.200+ fórmulas matriciais. Inserir mais fórmulas em grande quantidade no seu arquivo tenderá a tornar o recálculo lento."
R: Realmente são muitas fórmulas, o que já está impactando na performance, porém como eu disse antes, se as fórmulas puderem ser eliminadas, aceito sugestões.

"Me parece que a sua base de dados está na planilha Dados, e a partir dela você replicou dados na planilha Fórmulas e agora a partir desta última você quer replicar os dados na planilha ALA A. É isso?"

R: Sim, a ideia é que a pessoa mecha apenas na planilha dados, e a partir dai se faça automaticamente as divisões, além da planilha ALA A, irei fazer ALA B e ALA C, porém acredito que se houver uma solução para a ALA A eu consiga replicar para as demais .

[osvaldomp]

R: Realmente são muitas fórmulas, o que já está impactando na performance, ...
É recorrente nos fóruns, a pessoa adota soluções não recomendadas via fórmulas, acha que está tudo bem, no entanto depois de algum tempo volta ao fórum por conta da lentidão. Já vi casos neste fórum que a pessoa montou um Banco de Dados via fórmulas. Além do risco de perder dados vai sofrer com lentidão.

R: Sim, a ideia é que a pessoa mecha apenas na planilha dados, e a partir dai se faça automaticamente as divisões, além da planilha ALA A, irei fazer ALA B e ALA C, porém acredito que se houver uma solução para a ALA A eu consiga replicar para as demais .
Ok, vamos elaborar uma solução direta então, sem a planilha Fórmulas. Para isso, seria oportuno você anexar um arquivo de amostra com alguns dados representativos na planilha Dados e com o resultado desejado na planilha ALA A. Se você quiser inclua as demais, ALA B, ALA C, ... assim o código já fica completo.

[agentecaveira]

"Ok, vamos elaborar uma solução direta então, sem a planilha Fórmulas. Para isso, seria oportuno você anexar um arquivo de amostra com alguns dados representativos na planilha Dados e com o resultado desejado na planilha ALA A. Se você quiser inclua as demais, ALA B, ALA C, ... assim o código já fica completo."

Coloquei exemplos na planilha.
Também coloquei uma nova planilha "Todos os critérios" (Contendo todos os critérios existentes), pois o exemplo que está nos dados é real, e as vezes nem todos os critérios terá um representante.

[osvaldomp]

Veja no arquivo anexo. Está com o código elaborado com base no seu último arquivo.

Fiz alguns ajustes na planilha Todos os critérios e coloquei lá os comentários.

[agentecaveira]

Osvaldo, quero lhe agradecer, mas nem sei como, muito obrigado mesmo pela paciência e disposição para ajudar, a sua fórmula está atendendo perfeitamente. Em relação ao comentário de atualizar conforme alteração na planilha, não será necessário, da forma que está, já atende muito bem, e aquela numeração não se trata de ID é apenas um índice mesmo, de 1 até o último nome inserido.

Muito obrigado mais uma vez!

[osvaldomp]

Que bom que atendeu.
Um último comentário pra ilustrar a questão da lentidão provocada pelas fórmulas existentes nas suas planilhas. Eu coloquei no início do código que está no arquivo, um comando que passa o recálculo do arquivo para o modo manual e ao final do código outro comando que retorna para o modo automático, dessa forma as fórmulas não são recalculadas durante a execução e assim o código rodou aqui em cerca de 2 a 3 segundos.
Agora, se deixar o recálculo no modo automático durante a execução o tempo de execução vai a aproximad// 4 minutos. Veja a possibilidade de excluir as duas planilhas que contêm fórmulas, caso elas não tenham mais utilidade.

Se você quiser testar aí comente ou remova esta linha no início do código ~~~> Application.Calculation = xlCalculationManual

[agentecaveira]

Em relação a performance, já está ok, já foi testado no computador que de fato será utilizado a planilha, trata-se de um i5-6400 com 8GB de RAM, e foi bem rápido, muito mais rápido do que fazer tudo isso de forma manual como era realizado anteriormente.

A base de dados oscila em torno de 800 nomes, então acredito que não irá ter problemas de performance futuros.

Mais uma vez muito obrigado!