Mesclar linhas do Excel de duas planilhas

Question

Eu gostaria de começar esta pergunta pedindo desculpas, há inúmeras perguntas sobre a fusão de linhas do excel, ainda, depois de navegar na web e a lista de " Perguntas que pode já tenha seu resposta ", eu ainda não sei como resolver o meu problema.

EDIT : parece que as soluções possíveis podem diferir com base no tipo de dados das diferentes planilhas que eu gostaria de mesclar. Nesse caso, parece que o uso da função "Consolidar" resolve o problema uma vez que todos os registros de dados (mas a coluna mais à esquerda) são numéricos. Meu problema é descobrir como encontrar uma solução para dados mistos de números e texto (o texto também pode conter letras diferentes do inglês). A pergunta foi editada para fornecer exemplos melhores.

Eu tenho duas planilhas com dados diferentes. A única semelhança está nos valores da coluna da esquerda:

Já coloquei os dados das duas planilhas em uma só, colando os valores da coluna mais à esquerda de uma das planilhas abaixo dos valores da coluna mais à esquerda da segunda planilha. Para o restante dos valores, colei-os à direita, para que as colunas de diferentes planilhas não se misturem. Depois disso, classifiquei a planilha pelos valores da coluna mais à esquerda e obtive um resultado semelhante ao exemplo simplificado da ilustração:

Oqueeuquerofazercomessaplanilhaéeliminaraslinhasduplicadasemesclarosdadosdaslinhasduplicadas,combasenacolunamaisàesquerda.Umexemplocorrespondenteéfornecidonailustração:

Algumas notas importantes :

Prefiro trabalhar com as planilhas de origem, em vez de combiná-las a uma planilha intermediária não mesclada, mas agradeço qualquer solução que me ajude a atingir minha meta.

Registros de dados (como mostrado nas figuras acima) podem estar incompletos e conter números e texto. Além disso, os registros de dados podem estar errados, por isso não posso presumir que uma determinada coluna sempre terá números ou texto (por exemplo, a coluna Expedição da planilha 2).

Existem linhas cujo valor da coluna mais à esquerda está presente em apenas uma das planilhas (por exemplo, "nome5" só vem da planilha 1, enquanto "nome3" e "nome6" só vêm da planilha 2).

Estou tentando evitar o uso de macros VB para isso e prefiro usar funções internas já presentes no Excel.

Por outro lado, exportar para CSV e usar expressão regular é algo que estou disposto a considerar, se não for possível realizar essa tarefa usando as ferramentas internas do Excel. Neste caso, a questão pode precisar encontrar uma nova casa no StackOverflow.

    

Answer 1

Para dados numéricos ...

Tente usar o / 6521 "> Consolidar recurso.

Digamos que eu tenha o ff. dados em Folha1 e Folha2 , respectivamente.

1. Selecioneumacéluladedestino.Qualquercélula/intervalofaráotempoquevocêtiverespaçosuficienteparaoresultado.Eurecomendocriarumanovaplanilha&realçandoacélulaA1.
2. IrparaDados>Consolidar.VocêtambémpodepressionarAlt+A,N.
3. EscolhaSoma.

4. EmReferência,cliquenobotãodeseleçãodereferência&realceosdadosemFolha1(incluindomarcadoresdelinhaecabeçalhos).CliqueemAdicionar.

5. Repita o passo anterior, mas adicione os dados em Folha2 .

   

6. EmUsemarcadores,certifique-sedequeLinhasuperior&Colunaesquerdaestãomarcadas.Vocêteráalgoassim:

7. Clique em OK .

Resultado:

Paraváriostiposdedados...

1. UseorecursoConsolidarparaobterosrótulosdelinhaexclusivos&cabeçalhosdecoluna.Limpeosdados,masdeixeasetiquetas/cabeçalhos:

2. Insira essa fórmula aproximada na célula vazia mais à esquerda da tabela e copie-a em & baixa. Substitua os intervalos nomeados para se adequarem aos seus dados.

   =IFERROR(IFERROR( INDEX(sheet1_data,MATCH($A2,sheet1_rowlabels,0),MATCH(B$1,sheet1_headers,0)), INDEX(sheet2_data,MATCH($A2,sheet2_rowlabels,0),MATCH(B$1,sheet2_headers,0))), "")

   Onde
   sheet1_data , sheet1_rowlabels & sheet1_headers refere-se a todos os dados (A1: C5), a primeira coluna (A1: A5) & primeira linha (A1: C1) na Folha1, respectivamente, sheet2_data , sheet2_rowlabels & sheet2_headers refere-se a todos os dados (A1: D6), a primeira coluna (A1: A6) & primeira linha (A1: D1) na Folha2, respectivamente

3. Formate conforme desejado.

   

Answer 2

Se eu entendi sua pergunta corretamente, isso pode ser tratado de maneira direta usando VLOOKUPs.

Primeiro, você precisará criar uma lista ordenada e não duplicada dos valores "Item" encontrados nas colunas à esquerda das duas tabelas (ao longo das linhas exibidas no exemplo "Resultado final").

Isso pode ser feito por:

• Copiando a lista de itens da primeira tabela para a coluna A da planilha na qual você deseja manter o resultado final
• Copiando a lista de itens da segunda tabela e colando-a abaixo dos valores copiados da primeira tabela para que você tenha uma longa lista duplicada
• Classificando a lista duplicada usando o recurso de classificação interno do Excel na faixa de opções Início
• Eliminando as duplicatas, destacando a lista com o mouse e selecionando Data / Remove Duplicates da faixa de opções

Em seguida, você pode continuar a configurar a tabela de resultados final com a lista de valores exclusivos de Item, como os rótulos de linha e os rótulos de coluna copiados das duas tabelas (novamente, conforme mostrado no exemplo do Resultado Final).

Com este shell no lugar, você pode inserir as funções do VLOOKUP que puxarão os dados das duas tabelas. A idéia é usar um conjunto de VLOOKUPs para obter os dados da primeira tabela e outro conjunto para obter os dados da segunda tabela.

Aqui está a fórmula para a primeira célula na tabela Resultado Final:

  =IFERROR(IF(VLOOKUP($A2,Sheet1!$A$2:$C$5,2,0)=0,"-",VLOOKUP($A2,Sheet1!$A$2:$C$5,2,0)),"-")

A sintaxe da pesquisa é VLOOKUP( lookup_value, table_array, column_index_num, [range_lookup]) . O último parâmetro opcional "range_lookup" especifica se a correspondência para a pesquisa será aproximada (o padrão) ou exata.

Portanto, as VLOOKUPs na fórmula (ambas as VLOOKUPs são idênticas) procuram o nome do Item na célula A2 na primeira coluna do intervalo A2: C5 e retornam o valor correspondente na segunda coluna do intervalo, que é o Fornecedor. coluna. Como o último argumento no VLOOKUP está definido como 0 (ou FALSE), a correspondência deve ser exata.

OIFERROR,IFeousoduplodeVLOOKUPnafórmulasãonecessáriosparalidarcomapossibilidadedequenãohajacorrespondêncianoItem(oquepoderiaacontecerseoitem"name1" estivesse na tabela 2, mas não na tabela 1) ou que há uma correspondência no Item, mas a célula para o valor correspondente está vazia (o que de outra forma retornaria 0).

A fórmula de pesquisa para a segunda coluna da tabela Resultado Final difere da primeira apenas no número de índice da coluna, que é definido como 3 para retornar as informações de contato:

Exatamenteamesmaabordageméusadaparabuscarosdadosdasegundatabeladeentrada,usandoumVLOOKUPquereferenciaointervaloparaessatabelaeasrespectivascolunasdedadosaseremretornadas.Observequeeudefiniasfórmulaspararetornarumtraço("-") se nenhuma correspondência for encontrada ou se nenhum valor de retorno estiver disponível (ou seja, se a célula com o valor de retorno estiver vazia). Isso poderia facilmente ser alterado para uma seqüência vazia (""). Naturalmente, assim que as fórmulas para a primeira linha da tabela de resultados forem configuradas com referências apropriadas para os intervalos de tabela e colunas de retorno, elas serão copiadas para a parte inferior da lista Item.