Como juntar duas planilhas com base nos dados que elas contêm

Question

Gostaria de receber ajuda sobre como participar de duas planilhas diferentes que recebo toda semana.

A folha 1 contém informações sobre os defeitos observados durante uma semana (#defeito, tipo de defeito, # controle de qualidade) e a Folha 2 contém informações sobre as ações corretivas que precisam ser tomadas para esses defeitos (#defeito, corretivo ação, pessoa responsável, data de conclusão).

Eu quero unir esses dados e criar uma nova planilha com as seguintes colunas: #defect, tipo de defeito, # controle de qualidade, ação corretiva, respons. pers, data de conclusão.

Eu tentei com a função VLOOKUP, mas enfrento dois problemas:

1.) Quando eu tentei o VLOOKUP, o valor de pesquisa #defect (planilha de defeitos) na matriz da tabela Ação corretiva, perdi alguns resultados porque um defeito pode ter mais de uma ação corretiva

2.) Quando eu tentei VLOOKUP o lookupvalue #defect (planilha de ações corretivas) na matriz da tabela Defeitos também sinto falta de alguns resultados porque nem todo defeito tem uma ação corretiva.

Eu gostaria de receber ajuda!

    

Answer 1

Uma maneira de realizar isso é criar uma macro que faz todas as coisas necessárias automaticamente. A desvantagem é que ele provavelmente ficará abaixo do ideal em termos de desempenho, já que as operações necessárias se ajustam mal à funcionalidade disponível no Excel.

Dadas as planilhas Sheet1, Sheet2 e Result e as planilhas

#defect type    #quality
4       B       574
1       A       34
2       C       7564
3       A       23
5       A       783
6       B       23

e

#defect action  person  completion
1       foo     John    2.10.2011
3       bar     Eric    14.8.2012
4       zzzz    John    16.2.2013
3       asdf    Jeff    2.8.2012

e layout da coluna da planilha de resultados como

#defect type    #quality    action  person  completion

a macro a seguir deve fazer o que está sendo perguntado ( corrigiu os erros e alguns dos problemas de desempenho encontrados na versão original ):

Sub doFullOuterJoin()
'
' Perform what SQL terminology calls full outer join on two sheets
'
'
    Dim defectRange As Range
    Dim actionRange As Range
    Dim resultSheet As Worksheet

    Set defectRange = Sheets("Sheet1").Range("A2:C999") ' the data range 1
    Set actionRange = Sheets("Sheet2").Range("A2:D999") ' the data range 2
    Set resultSheet = Worksheets("Result")

    defRangeCols = defectRange.Columns.Count
    actRangeCols = actionRange.Columns.Count

    resRow = 2 ' result sheet row number to start filling data at
    lastMatch = 0 ' used to keep track of last matching index to improve performance
    For Each rw In defectRange.Rows
        ' process defects one at a time
        defectId = rw.Cells(1, 1)
        If (defectId = "") Then Exit For
        actIndex = 1
        Do
            ' find all the actions for the current defect
            matchedAction = VLookupRow(defectId, actionRange, lastMatch + 1)
            If (matchedAction = 0) Then
                ' no matching action was found
                If (actIndex = 1) Then
                    ' no actions at all, but copy defect record anyway
                    rw.Copy (resultSheet.Cells(resRow, 1))
                    resRow = resRow + 1
                End If
                lastMatch = 0
                Exit Do ' move on to next defect
            Else
                ' a matching action was found
                rw.Copy (resultSheet.Cells(resRow, 1)) ' copy defect record
                ' copy action data
                actionRange.Cells(matchedAction, 2).Resize(1, actRangeCols - 1).Copy
                resultSheet.Cells(resRow, defRangeCols + 1).Select
                resultSheet.Paste
                actIndex = actIndex + 1
                lastMatch = matchedAction
            End If
            resRow = resRow + 1
        Loop Until actIndex = 999
    Next rw
End Sub


Function VLookupRow(lookup_value, table_array As Range, Optional start_row As Long) As Integer
' Do VLOOKUP-like operation with optionally given start position
' This allows searching sequentially for the rest of matching rows with rather good performance
    Dim nRow As Long

    If (start_row = 0) Then start_row = 1 ' no start row provided, start at first row

    With table_array
        For nRow = start_row To .Rows.Count
            If .Cells(nRow, 1).Value = lookup_value Then
                VLookupRow = nRow
                Exit Function
            End If
        Next nRow
    End With
End Function

Basicamente, isso será executado pelas linhas de defeitos (Folha1), uma por uma, copiará os dados para a folha de resultados (Resultado) e localizará todas as linhas de ação correspondentes (Folha2) e as copiará também na folha de resultados. Parará quando encontrar a primeira linha com #defeito vazio na Folha1. No entanto, o código é um pouco lento, copiando os dados um pouco sem jeito. Deve, no entanto, permitir uma modificação bastante fácil para diferentes tamanhos de intervalos de dados e, com algum ajuste, pode ser eficaz o suficiente para a tarefa.