CODEWITHSUNDEEP
excelvba
Revolucion for Monica
Revolucion for Monica

Reputation: 3296

Recovering the data of a column of record of an Excel sheet in another sheet

I want to retrieve the data (registration numbers) of a column of record from an Excel sheet on another Excel sheet when the name columns are the same.

That is to say on the "Convocation" sheet there is

Matric Apellido         Nombre  Avión
12     Burrow           Irving  F15     
42     Nelson           Howard  A10
18     Krings           Jack    F18    
10     Mitaux-Maurouard Guy     Rafale

And in the sheet "RECAP" I have

Apellido         Nombre ...
Nelson           Howard ...
Burrow           Irving ...
Mitaux-Maurouard Guy   
Krings           Jack   ...

Y me gustaría 

Apellido Nombre ... Matric
Nelson   Howard ... 42
Burrow   Irving ... 12  
Krings   Jack   ... 18

I tried :

Sub Test()

    Dim i As Long
    Dim arr As Variant
    Dim Matriculas As New Scripting.Dictionary 'Need the Microsoft Scripting Runtime library in Tools-> References

    'We store the sheet with everything in an array
    With ThisWorkbook.Sheets("Convocation")
        arr = .UsedRange.Value
    End With

    'We assume that the registration is in column A and surnames B and C
    For i = 2 To UBound(arr)
        If arr(i, 1) = vbNullString Then Exit For 'if we find empty values we leave the loop
        If Not Matriculas.Exists(arr(i, 2) & arr(i, 3)) Then 'we check that the combo of surnames is not duplicated
            Matriculas.Add arr(i, 2) & arr(i, 3), arr(i, 1) 'we keep in the dictionary the combo of surnames with their registration
        Else 'If the person could have more than 1 license plate then:
            Matriculas(arr(i, 2) & arr(i, 3)) = Matriculas(arr(i, 2) & arr(i, 3)) & ", " & arr(i, 1) 'we add the others separated by ","
        End If
    Next i

    'We store the sheet with everything in an array
    With ThisWorkbook.Sheets("RECAP")
        arr = .UsedRange.Value
        'We assume that the surnames are in columns A and B, and the registration in C
        For i = 2 To UBound(arr)
            If arr(i, 1) = vbNullString Then Exit For 'if we find empty values we leave the loop
            arr(i, 3) = Matriculas(arr(i, 1) & arr(i, 2)) 'here we assign the registration stored in the dictionary according to surnames
        Next i
        .UsedRange.Value = arr 'we return the array to the sheet
    End With

End Sub

But when I compile I have this error: Sub or Function undefined. I have put the code in Microsoft Excel Objects that contains all the sheets, in particular in ThisWorkbook.

Edit: I know my problem

My problem wasn't with the first line but with Ubond. When I get rid of it I don't have errors anymore. But they are necessary for the loops.

Upvotes: 0

Views: 34

Answers (1)

Foxfire And Burns And Burns
Foxfire And Burns And Burns

Reputation: 11988

Data on CONVOCATION sheet is like:

enter image description here

And sheet RECAP is:

enter image description here

To get the number from field MAtric I've used an array formula:

=INDEX(Convocation!$A$2:$A$5;MATCH(A2&B2;Convocation!$B$2:$B$5&Convocation!$C$2:$C$5;0))

Because this is an array formula, it must be inserted with CTRL+SHIFT+ENTER or it won't work!

NOTE: About your data, this formula will work as long as all Apellido and Nombre are different between rows. If at any moment you have 2 clients (2 rows) with same Apellido and Nombre then the formula won't work properly, because yoo would need a third field to differentiate both clientes between them (maybe a second Apellido).

Hope you can adapt this to your needs.

UPDATE: In case the formula finds nothing, it will throw an error, but you can trap it with an IFERROR, so the ideal formula would be:

=IFERROR(INDEX(Convocation!$A$2:$A$5;MATCH(A2&B2;Convocation!$B$2:$B$5&Convocation!$C$2:$C$5;0));"Not found!")

Because this is an array formula, it must be inserted with CTRL+SHIFT+ENTER or it won't work!

UPDATE 2: You can download a sample here: https://drive.google.com/open?id=1kQ0boXYl7nvkTef62zVg9Jw7raOqgsuf

Upvotes: 1

Related Questions