Alinhar linhas com células iguais

Mph · 2 de julho de 2012

Olá

Estou a trabalhar 2 tabelas, onde preciso de alinhar automaticamente as linhas com células iguais.

Envio um exemplo simplificado:

http://www.sendspace.com/file/bl3sla

Agradeço antecipadamente!

Mph

leno mota · 2 de julho de 2012

Olá! Sua explicação está um pouco confusa.

voce precisa alinhar a esquerda ou a direita?

De um exemplo utilizando referencias!

Ex:

Preciso que o intervalo "a17:f17" seja alinhado a esquerda porque é igual ao intervalo "A2:F2".

É isso que precisa?

No aguardo,

Leno Mota.

Hank II · 2 de julho de 2012

A ideia é primeiro classificar a tabela da direita. Vá em Dados e Classificar.

Depois calcular as inserções e deslocamentos necessários para alinhar as duas. Para alinhar, vai ter de usar o recortar-e-colar.

Grave uma macro fazendo tudo isso manualmente e tente adaptá-la.

Espero ter ajudado.

Mph · 3 de julho de 2012

Olá Leno

Tenho 2 tabelas e preciso de alinhar as linhas tendo como referência as colunas F e M, no exemplo.

Neste caso irei alinhar pela tabela com mais linhas. Penso que será indiferente ser a da esquerda ou a da direita, visto que tenho de as copiar e colar uma de cada vez.

A tabela com mais linhas (a Tabela 1, por exemplo) poderá não ter na coluna F, todos os valores da coluna M (Tabela 2) e vice versa. No entanto, a soma da coluna F deverá coincidir com a soma da coluna M. Caso contrário, significa que existem erros nas tabelas, mas isso já é outro problema.

Uma linha da Tabela 1 (linha "composta"), poderá estar dividida em 2 ou 3 linhas (linhas "parciais") na Tabela 2 e vice versa.

Mesmo que não seja possível, encontrar automaticamente as linhas "parciais" que equivalem à linha "composta" respectiva, se for possível alinhar automaticamente as linhas cujos valores das colunas F e M forem iguais, já será uma boa ajuda.

Espero ter sido mais explicito.

Agradeço antecipadamente!

A ideia é primeiro classificar a tabela da direita. Vá em Dados e Classificar.
Depois calcular as inserções e deslocamentos necessários para alinhar as duas. Para alinhar, vai ter de usar o recortar-e-colar.

Grave uma macro fazendo tudo isso manualmente e tente adaptá-la.

Espero ter ajudado.

Olá Hank

Não estou familiarizado com o comando Classificar...

Seja como for, as tabelas têm algumas centenas de linhas e por isso procuro uma alternativa ao recortar-colar.

Agradeço a sua ajuda!

Hank II · 3 de julho de 2012

Caro Mph,

classificar é simples. A macro abaixo, por exemplo, classifica a tabela da direita da sua planilha (Teste) pela coluna H. Escolhi essa coluna por falta de informação. Pode ser por outra e pode ser por mais de uma coluna, em ordem ascendente ou descendente para cada coluna. Se for o caso, me avise que eu monto outra macro.

Sub Macro1()
    Range("H2:M10").Select
    Selection.Sort Key1:=Range("H2"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
End Sub

Você só tem de alterar os dois Ranges, evidentemente, por código.

Uma possível solução então seria:

Defina uma matriz dinâmica {Dim salva()} e redefina-a pelo tamanho efetivo da tabela 2 após a classificação. No caso (com base na planilha Teste) ficaria assim: {Redim salva(6,6)}.

Copie os dados da tabela2 (classificada) para a matriz.

Em seguida, formate as colunas H a M iguais as colunas A a F. (Isso se for preciso.)

    Columns("A:F").Select
    Range("A2").Activate
    Selection.Copy
    Columns("H:M").Select
    Range("H2").Activate
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False

Agora limpe as colunas H a M:

    Columns("H:M").Select
    Selection.ClearContents

Por fim, leia cada linha da matriz, por exemplo, num vetor auxiliar {Dim linha(6)}, procure a localização daquela linha na tabela1 e mova os dados pra tabela2.

Acho que ficou mais simples.

Espero ter ajudado.

Mph · 3 de julho de 2012

Olá Hank II

Agradeço a sua ajuda, mas estou muito "verde" nestas matérias...

Vou guardar a sua resposta de ontem para tentar aplicar mais tarde, quando estiver mais dentro destes assuntos.

Entretanto encontrei o tópico "Alinhar dados correspondentes em várias células" postado pelo fzanchetta, em 28/06/2012, 07:23 H, assim como a resposta do jeffsilveira e acho que poderá servir de base ao que pretendo.

Agradeço novamente a sua ajuda.

Hank II · 4 de julho de 2012

OK, Mph.

Sem dúvida, a macro do Jeff é excelente e bem mais simples.

Um abraço,

Hank II.

jeffsilveira · 4 de julho de 2012

Mph, baixe a planilha:

http://www.sendspace.com/file/vmqvyv

Usei a macro abaixo, espero que seja útil. O procedimento ordena em ordem alfabética a planilha 2 e depois alinha com a planilha 1.

'alinha as colunas A:F com as colunas H:M
Sub alinhar_dados()
'desabilita atualização de tela
Application.ScreenUpdating = False
'organiza em ordem crescente
ActiveSheet.Sort.SortFields.Clear
ActiveSheet.Sort.SortFields.Add Key:=Range("H2"), _
    SortOn:=xlSortOnValues, Order:=xlAscending, _
    DataOption:=xlSortNormal
With ActiveSheet.Sort
    .SetRange Range("H2:M" & Cells(Rows.Count, 8).End(xlUp).Row)
    .Header = xlNo
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With
'executa até a última linha preenchida em A
For i = 2 To Cells(Rows.Count, 1).End(xlUp).Row
    'se a célula em A for diferente de H
    If Cells(i, 1) <> Cells(i, 8) Then
    'insere linha em H:M
    Range(Cells(i, 8), Cells(i, 13)).Insert Shift:=xlDown, _
        CopyOrigin:=xlFormatFromLeftOrAbove
    End If
'próxima linha
Next
'apaga linhas em excesso no final da planilha 2
Dim lin As Long
Do
'penúltima linha em M
lin = Cells(Rows.Count, 13).End(xlUp).Offset(-1, 0).Row
    'se a penúltima célula em M for vazia
    If Cells(lin, 13) = "" Then
    'apaga a linha em H:M
    Range(Cells(lin, 8), Cells(lin, 13)).Delete
    End If
Loop Until Cells(Rows.Count, 13).End(xlUp).Offset(-1, 0) <> ""
'habilita atualização de tela
Application.ScreenUpdating = True
End Sub

Dê retorno.

Abraços.

Mph · 4 de julho de 2012

Olá jeffsilveira

Desculpe, mas não mencionei que as colunas A e H são datas e que há linhas que têm a mesma data.

Postei uma nova planilha com essa alteração.

http://www.sendspace.com/file/ba5qyd

No entanto, as colunas que devem servir de referência/comparação são a F e a M.

Agradeço antecipadamente!

jeffsilveira · 4 de julho de 2012

Pois é, Mph, agora você complicou um pouco. Também, né, ficou escondendo o jogo, hehe...

Por isso sempre solicitamos exemplos de planilha com o mesmo layout e formatos da planilha original.

Usar somente a coluna F com a M acredito que dará erro, pois em algum momento os valores também podem ser iguais.

No seu caso, seria interessante saber que tipo de conteúdo estarão nas células das colunas B:E e I:L. Assim teremos como usar esses dados como comparação também.

Na planilha 1 as datas sempre estarão desordenadas?

Por favor, poste uma planilha fictícia mais fiel à original para que não fiquemos dando tiro no escuro.

Contamos com sua compreensão e colaboração.

Abraços.

Mph · 5 de julho de 2012

Caro jeffsilveira

Perdoe-me a falta de rigor, mas como não sei fazer também não sei o que precisa de saber. Vou tentar dar o meu melhor...

Tabela 1:

Coluna A - data (pode cruzar com a coluna H)

Coluna B - data (sem correspondência na Tabela 2)

coluna C - texto (sem correspondência na Tabela 2)

coluna D - texto (sem correspondência na Tabela 2)

coluna E - texto (sem correspondência na Tabela 2)

coluna F - valor (cruza com coluna M)

Tabela 2:

Coluna H - data (pode cruzar com a coluna A)

Coluna I - texto (sem correspondência na Tabela 1)

coluna J - valor (sem correspondência na Tabela 1)

coluna K - texto (sem correspondência na Tabela 1)

coluna L - texto (sem correspondência na Tabela 1)

coluna M - valor (cruza com coluna F)

- posso ordenar as Tabelas por data ou por valor;

- há valores repetidos na coluna F;

- há valores repetidos na coluna M;

- há datas repetidas na coluna A;

- há datas repetidas na coluna H;

As linhas que não tiverem correspondência na outra tabela, devem ser transferidas para o final da tabela respectiva, onde irei tratá-las manualmente.

Espero não ter complicado mais...

Agradeço o seu tempo!

Tudo Nosso · 18 de julho de 2012

bom dia amigos, nao sei se meu problema é mais confuso ou mais simples que esse mas preciso alinhar as colunas A (codigo) e L (COD AMB), se necessario posso mudar a ordem dessas colunas pro inicio ou fim, mas preciso q todas as outras colunas acompanhem esse alinhamento, igual no "classificar dados"

tentei editar uma macro que o jeff fez, mas nao consegui... Se vocês puderem dar uma olhada pra mim eu agradeço mtu!

http://www.sendspace.com/file/gifjxk

jeffsilveira · 18 de julho de 2012

Vi sua planilha mas preciso sanar uma dúvida: por se tratar de uma tabela apenas, quais colunas acompanham cada Código?

Imagino que as linhas de A:K devam seguir a ordem por A, e de L:V a ordem por L, é isso?

Isso é necesário pois na hora de alinhar as colunas preciso saber quais linhas seguirão o alinhamento e quais ficarão fixas. Ou, resumindo, transformar a tabela em duas.

Fui Claro? Tomara que sim!

No aguardo.

Tudo Nosso · 18 de julho de 2012

Vi sua planilha mas preciso sanar uma dúvida: por se tratar de uma tabela apenas, quais colunas acompanham cada Código?
Imagino que as linhas de A:K devam seguir a ordem por A, e de L:V a ordem por L, é isso?

Isso é necesário pois na hora de alinhar as colunas preciso saber quais linhas seguirão o alinhamento e quais ficarão fixas. Ou, resumindo, transformar a tabela em duas.

Fui Claro? Tomara que sim!

No aguardo.

Boa tarde Jeff! Sim, você acertou, as colunas de A:K sao uma tabela, e de L:V sao outra tabela, então eu colei as duas, agora preciso transformar em uma, assim:

1º A:K devem seguir a ordem de A, e L:V devem seguir a ordem de L,

2º A e L devem se 'igualar' , ou seja, os códigos iguais ficarem na mesma linha

espero ter sido claro tambem hehehe desde ja agradeço!

jeffsilveira · 19 de julho de 2012

Tudo Nosso, o código abaixo alinhou conforme você queria. Demora um cadin pois sua planilha é muito extensa.

Porém, sua planilha tem em L números formatados como texto e em A como números.

Isso fez com que alguns valores não se alinhassem devido a zeros à esquerda.

Além disso, há varios códigos em L que não possuem par em A.

Teste aí e diga-nos o que pode ser melhorado.

Abraços.

Sub organiza()
Application.ScreenUpdating = False
'organiza em ordem crescente A:K
    ActiveSheet.Sort.SortFields.Clear
    ActiveSheet.Sort.SortFields.Add Key:=Range("A2" _
        ), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveSheet.Sort
        .SetRange Range("A2:K" & Cells(Rows.Count, 1).End(xlUp).Row)
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
'organiza em ordem crescente L:V
    ActiveSheet.Sort.SortFields.Clear
    ActiveSheet.Sort.SortFields.Add Key:=Range("L2" _
        ), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Para alinhar").Sort
        .SetRange Range("L2:V" & Cells(Rows.Count, 12).End(xlUp).Row)
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
'transforma valores em A em texto
    Columns("A:A").NumberFormat = "@"
'alinha os valores
Dim lini, linj As Integer
lini = Cells(Rows.Count, 12).End(xlUp).Row
linj = Cells(Rows.Count, 1).End(xlUp).Row
    For i = lini To 2 Step -1 'em L
        For j = linj To 2 Step -1 'em A
        If Cells(i, 12).Text = Cells(j, 1).Text Then
        Range(Cells(i, 12), Cells(i, 22)).Copy
        Range(Cells(j, 12), Cells(j, 22)).PasteSpecial
        Range(Cells(i, 12), Cells(i, 22)).Clear
        Exit For
        End If
        Next
    Next
'verifica se há valores não alinhados e cola na última linha
    Dim link As Integer
    For k = 2 To Cells(Rows.Count, 1).End(xlUp).Row
    If Cells(k, 12) <> "" And Cells(k, 12).Text <> Cells(k, 1).Text Then
        link = Cells(Rows.Count, 1).End(xlUp).Row
        Range(Cells(k, 12), Cells(k, 22)).Copy
        Range(Cells(link, 12), Cells(link, 22)).PasteSpecial
        Range(Cells(k, 12), Cells(k, 22)).Clear
        link = 0
    End If
    Next
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

Tudo Nosso · 19 de julho de 2012

Bom dia Jeff, só tem um problema, eu nao sei criar a macro.. a outra que você fez eu usei porque baixei sua planilha pronta com o botão ja inserido

Outra coisa, no caso da formatação das celulas, é melhor eu formatar tudo em numero então? pra nao ter essa divergencia "numero x texto" ?

jeffsilveira · 19 de julho de 2012

Essa é a planilha alinhada, não sei há erros.

http://www.sendspace.com/file/vzmcfi

Mas se você puder formatar como número os códigos das colunas A e L fica muito melhor.

No aguardo.

Tudo Nosso · 19 de julho de 2012

Essa é a planilha alinhada, não sei há erros.
http://www.sendspace.com/file/vzmcfi

Mas se você puder formatar como número os códigos das colunas A e L fica muito melhor.

No aguardo.

Amigão, ficou otimo! e como nos baseamos na coluna A , os codigos "a mais" que haviam na coluna L sumiram, mas isso nao é problema nenhum pra mim, porque posso adiciona-los futuramente, isso se for preciso.

Agradeço muito pela ajuda!!! só mais uma pergunta, se eu precisar fazer isso com outros dados, essa macro ta gravada nessa planilha q você postou? se nao poderia postar a plan com a macro pronta? ai eu só vou substituir os dados e usa-la sempre q necessario.

Desculpe o trabalho!