Tenho uma planilha do Excel 2007 com 2.250 linhas e 19 colunas. Nessas linhas, posso ter duas linhas de informações duplicadas do cliente que precisam ser combinadas, mas somente se a célula acima estiver vazia. Também posso ter linhas de dados de clientes que não precisam ser combinadas. Um número exclusivo de membro do cliente pode ser usado para identificar as linhas que precisam ser combinadas. Estou lutando para desenvolver o script VBA correto para combinar os dados exclusivos do cliente em uma linha (na parte superior) e excluir a linha restante após a combinação. Alguém está disposto a ajudar? Isso me poupará horas/dias combinando essas linhas e estamos no meio de uma auditoria urgente.
Amostra de nossos dados:
MEMBRO NOME MEMBRO SOBRENOME MEMBRO # MVP SISTEMA DATA DE INSCRIÇÃO DATA DE INSCRIÇÃO MVP PONTOS DESENHO INSCRIÇÕES FORMULÁRIO DE INSCRIÇÃO? PONTOS CORRETOS? PONTOS PERDIDOS PONTOS FINAIS INSCRIÇÕES NO DESENHO SP Talon # WP Talon # BD DEPT FUNCIONÁRIO NOTAS DLR Gene S 550061 02/03/2013 0 0 0 #N/A Gene S 550061 02/03/2013 1539 137 MC MJ SP Steve G 550087 02/03/2013 30019 1588 PA NR WP Curtis S 550128 24/04/2013 5 0 5 #N/A Curt S 550128 24/04/2013 358 47 MC MJ SP
Edite (não do OP) para adicionar versão delimitada por barras verticais/parágrafos com sublinhados para espaços nos títulos:
MEMBER_FIRST_NAME|MEMBER_LAST_NAME|MEMBER_#|MVP_SYSTEM_ENTRY_DATE|ENROLL_DATE|MVP_POINTS|DRAWING_ENTRIES|ENROLL_FORM?|POINTS_CORRECT?|POINTS_MISSED|FINAL_POINTS|DRAWING_ENTRIES|SP_Talon_#|WP_Talon_#|BD|DEPT |COLABORADOR|NOTAS|
Gene DLR|S|550061|03/ 02/2013||0|0||||0|#N/A|||||||
Gene|S|550061||03/02/2013||||||||1539|137||MC|MJ||SP
Steve|G|550087||03/02/2013||||||| |30019|1588||PA|NR||WP
Curtis|S|550128|4/24/2013||5|0||||5|#N/A|||||||
Curto|S|550128||24/04/2013||||||||358|47||MC|MJ||SP
Responder1
Não tenho muita certeza do esclarecimento que você forneceu, mas aqui vai mesmo assim!:
Ponto-chave - o seguinte pressupõe que em MEMBER # MVP SYSTEM ENTRY DATE sempre aparecerá acima de ENROLL DATE.
Por segurança, trabalhe em uma cópia e adicione um número de índice a cada linha (digamos insert ColumnA, put 1in A1, =A1+1in A2e copie a fórmula até a linha 2250. Copie ColumnAe cole valores/especiais por cima.
Selecione D2, Home> Estilos – Formatação Condicional, Nova Regra, Use uma fórmula para determinar quais células formatar, Formate valores onde esta fórmula for verdadeira: inserir =AND(NOT(ISBLANK(D2)),OR(D1=D2,D2=D3)), Formatar, Preencher, selecione amarelo, OK, OK. Em Formatação Condicional - Gerenciar Regras, em Aplica-se para inserir =$D$2:$D$2250, Aplicar. OK.
Selecione a planilha inteira (clique no triângulo à esquerda de A e acima de 1 nos títulos), Dados > Classificar e Filtrar –Filtrar e para ColumnDFiltrar por cor, selecione amarelo.
Copie a Linha1 até a última linha numerada em azul e cole em A1uma planilha diferente (digamos, Planilha2).
Na Planilha2, exclua F1, mova as células para cima, OK. Também N1:T1. (É aqui que pode ser necessária uma análise adicional.)
Adicione um novo ColumnAà Planilha2. Coloque 1em A1, 2em A2, selecione A1:A2, pegue o canto inferior direito da seleção, mantenha o botão esquerdo do mouse pressionado enquanto arrasta para baixo até onde for necessário e até depois de pressionar e segurar Ctrl.
Selecione Planilha2, Dados> Classificar e Filtrar – Classificar, marque Meus dados têm cabeçalhos, Classificar por ColumnA(o primeiro dos 1s!), Classificar por valores, Ordenar do menor para o maior, OK.
Observe o número da linha mais baixa que contém 2a ColumnAPlanilha2 e o número da linha ocupada mais alta. Excluir ColumnA.
Volte para a primeira planilha e exclua todas as linhas que contêm destaque amarelo.
Na Planilha2, selecione o número da linha inferior e todas as outras linhas ocupadas com um número maior, copie e cole de volta ColumnAna parte inferior da primeira planilha.
Esperamos que isso atinja a maior parte do que você precisa - ou, se não, seja 'um passo na direção certa'! Para verificar, sua última linha ocupada agora deve ser 2250+1 menos a diferença entre os dois números mencionados acima.
Para verificar o PRIMEIRO NOME DO MEMBRO, sugiro criar uma tabela de pesquisa de MEMBER # e isso e, em seguida, comparar o PRIMEIRO NOME DO MEMBRO nessa base na planilha da qual você tirou uma cópia. Curt ou Curtis é provavelmente uma decisão judicial.
Responder2
Aqui está outra abordagem possível. Depende de três condições:
- Um identificador exclusivo deve estar disponível para distinguir campos duplicados de não duplicados. Neste caso, o campo MEMBER# serve para esse propósito. Em outros casos, o identificador pode ser construído como a combinação dos valores em vários campos. Esse id pode ser o valor em um único campo ou uma composição dos valores em vários campos.
- Não mais do que duas duplicatas de qualquer MEMBER#, ou seja, nenhum registro triplo ou múltiplo "duplicado" superior.
- As linhas são classificadas pelo identificador MEMBER#.
A ideia é construir uma tabela transformada, mais convenientemente à direita da tabela existente, que utilize fórmulas para consolidar - em uma única linha - os dados parciais que são compartilhados entre duas linhas duplicadas, deixando uma linha preenchida e uma em branco. linha.
Feito isso, um filtro pode ser aplicado à tabela de resultados para excluir as linhas em branco, deixando as linhas preenchidas para serem copiadas para outro local.
Conforme mostrado abaixo, adicionei um campo de sinalização "DUP" na coluna A: é igual a 1 se um MEMBER# na coluna C for igual a MEMBER# na linha anterior e for igual a 0 caso contrário. Os dois conjuntos de linhas nos dados de exemplo com MEMBER#s duplicados são destacados em amarelo.
Esta é a aparência da tabela de resultados de fórmulas. Como esperado, as informações complementares que foram compartilhadas entre dois registros foram reunidas em um dos registros, deixando o outro registro preenchido com travessões duplos ("--"). (Os dois conjuntos de linhas duplicadas nos dados de exemplo são destacados em azul mais escuro na tabela.)
Observando as duas primeiras linhas da tabela, que continham versões duplicadas para MEMBER# 550061, o segundo "Gene" na linha 4 da coluna MEMBER_FIRST_NAME foi substituído por "--"; o ENROLLMENT_DATE anteriormente em branco na linha 3 agora é preenchido com 02/03/2013, movido da linha 4; os valores N/A para o segundo campo DRAWING_ENTRIES (coluna M na tabela original, coluna AS na nova) foram substituídos por espaços em branco.
Tudo o que resta a fazer é aplicar um filtro, usar a coluna DUP como coluna de critério, selecionar apenas as linhas onde DUP é igual a 0 - e copiar o resultado para um novo local.
As fórmulas usadas para consolidar as duplicatas são essencialmente idênticas em estrutura, por isso faz sentido examiná-las detalhadamente. Aqui está a primeira fórmula da tabela, da célula AH3, para a coluna MEMBER_FIRST_NAME (incluo no final deste post o conjunto completo de fórmulas para a primeira linha da tabela de resultados).
=IF($A3=1, If this is row 2 of a DUP set,
"--", Set value of the result cell to "--"
Otherwise it's a row 1 (maybe a dup, maybe not)
IF($A4=0, Is the following row its dup?
IF(IFERROR(B3="",FALSE),"",B3), No, set result to the value on this row
IF(OR(IFERROR(B3="",FALSE),ISERROR(B3)), Yes, but is this row's value blank or error?
IF(IFERROR(B4="",FALSE),"",B4), Yes, use the value from the following row
IF(IFERROR(B3="",FALSE),"",B3)))) No, use the value from this row
Um comentário adicional sobre o código: a locução um tanto indireta IFERROR(<cell address>="",FALSE)é necessária para filtrar adequadamente os valores de erro N/A em algumas linhas.
Código para a primeira linha da tabela de resultados
DUP =IF(D3=D2,1,0)
FNAME =IF($A3=1,"--",IF($A4=0,IF(IFERROR(B3="",FALSE),"",B3),IF(OR(IFERROR(B3="",FALSE),ISERROR(B3)),IF(IFERROR(B4="",FALSE),"",B4),IF(IFERROR(B3="",FALSE),"",B3))))
LNAME =IF($A3=1,"--",IF($A4=0,IF(IFERROR(C3="",FALSE),"",C3),IF(OR(IFERROR(C3="",FALSE),ISERROR(C3)),IF(IFERROR(C4="",FALSE),"",C4),IF(IFERROR(C3="",FALSE),"",C3))))
MEMBER# =IF($A3=1,"--",IF($A4=0,IF(IFERROR(D3="",FALSE),"",D3),IF(OR(IFERROR(D3="",FALSE),ISERROR(D3)),IF(IFERROR(D4="",FALSE),"",D4),IF(IFERROR(D3="",FALSE),"",D3))))
ENTRY DT =IF($A3=1,"--",IF($A4=0,IF(IFERROR(E3="",FALSE),"",E3),IF(OR(IFERROR(E3="",FALSE),ISERROR(E3)),IF(IFERROR(E4="",FALSE),"",E4),IF(IFERROR(E3="",FALSE),"",E3))))
ENROL_DT =IF($A3=1,"--",IF($A4=0,IF(IFERROR(F3="",FALSE),"",F3),IF(OR(IFERROR(F3="",FALSE),ISERROR(F3)),IF(IFERROR(F4="",FALSE),"",F4),IF(IFERROR(F3="",FALSE),"",F3))))
MVP_PTS =IF($A3=1,"--",IF($A4=0,IF(IFERROR(G3="",FALSE),"",G3),IF(OR(IFERROR(G3="",FALSE),ISERROR(G3)),IF(IFERROR(G4="",FALSE),"",G4),IF(IFERROR(G3="",FALSE),"",G3))))
ENTRIES =IF($A3=1,"--",IF($A4=0,IF(IFERROR(H3="",FALSE),"",H3),IF(OR(IFERROR(H3="",FALSE),ISERROR(H3)),IF(IFERROR(H4="",FALSE),"",H4),IF(IFERROR(H3="",FALSE),"",H3))))
FORM =IF($A3=1,"--",IF($A4=0,IF(IFERROR(I3="",FALSE),"",I3),IF(OR(IFERROR(I3="",FALSE),ISERROR(I3)),IF(IFERROR(I4="",FALSE),"",I4),IF(IFERROR(I3="",FALSE),"",I3))))
PTS_CORRECT =IF($A3=1,"--",IF($A4=0,IF(IFERROR(J3="",FALSE),"",J3),IF(OR(IFERROR(J3="",FALSE),ISERROR(J3)),IF(IFERROR(J4="",FALSE),"",J4),IF(IFERROR(J3="",FALSE),"",J3))))
PTS_MISSED =IF($A3=1,"--",IF($A4=0,IF(IFERROR(K3="",FALSE),"",K3),IF(OR(IFERROR(K3="",FALSE),ISERROR(K3)),IF(IFERROR(K4="",FALSE),"",K4),IF(IFERROR(K3="",FALSE),"",K3))))
FINAL_PTS =IF($A3=1,"--",IF($A4=0,IF(IFERROR(L3="",FALSE),"",L3),IF(OR(IFERROR(L3="",FALSE),ISERROR(L3)),IF(IFERROR(L4="",FALSE),"",L4),IF(IFERROR(L3="",FALSE),"",L3))))
DR_ENTRIES =IF($A3=1,"--",IF($A4=0,IF(IFERROR(M3="",FALSE),"",M3),IF(OR(IFERROR(M3="",FALSE),ISERROR(M3)),IF(IFERROR(M4="",FALSE),"",M4),IF(IFERROR(M3="",FALSE),"",M3))))
SP_TALON =IF($A3=1,"--",IF($A4=0,IF(IFERROR(N3="",FALSE),"",N3),IF(OR(IFERROR(N3="",FALSE),ISERROR(N3)),IF(IFERROR(N4="",FALSE),"",N4),IF(IFERROR(N3="",FALSE),"",N3))))
WP_TALON =IF($A3=1,"--",IF($A4=0,IF(IFERROR(O3="",FALSE),"",O3),IF(OR(IFERROR(O3="",FALSE),ISERROR(O3)),IF(IFERROR(O4="",FALSE),"",O4),IF(IFERROR(O3="",FALSE),"",O3))))
BD =IF($A3=1,"--",IF($A4=0,IF(IFERROR(P3="",FALSE),"",P3),IF(OR(IFERROR(P3="",FALSE),ISERROR(P3)),IF(IFERROR(P4="",FALSE),"",P4),IF(IFERROR(P3="",FALSE),"",P3))))
DEPT =IF($A3=1,"--",IF($A4=0,IF(IFERROR(Q3="",FALSE),"",Q3),IF(OR(IFERROR(Q3="",FALSE),ISERROR(Q3)),IF(IFERROR(Q4="",FALSE),"",Q4),IF(IFERROR(Q3="",FALSE),"",Q3))))
EMPL =IF($A3=1,"--",IF($A4=0,IF(IFERROR(R3="",FALSE),"",R3),IF(OR(IFERROR(R3="",FALSE),ISERROR(R3)),IF(IFERROR(R4="",FALSE),"",R4),IF(IFERROR(R3="",FALSE),"",R3))))
NOTES =IF($A3=1,"--",IF($A4=0,IF(IFERROR(S3="",FALSE),"",S3),IF(OR(IFERROR(S3="",FALSE),ISERROR(S3)),IF(IFERROR(S4="",FALSE),"",S4),IF(IFERROR(S3="",FALSE),"",S3))))
DLR =IF($A3=1,"--",IF($A4=0,IF(IFERROR(T3="",FALSE),"",T3),IF(OR(IFERROR(T3="",FALSE),ISERROR(T3)),IF(IFERROR(T4="",FALSE),"",T4),IF(IFERROR(T3="",FALSE),"",T3))))