Find match through multiple workbooks and tabs and copy a cell's value to a another one

[Aldaron47]

Hello everyone,
I was searching through the forum for a solution on using Index/Match but I didn't manage to find one solving my problem.

I have two excel documents. The one of them contains information about participants of a quiz. Each week I have to retrieve the participants of the quiz and add them to a new tab of the first document. Each new weekly extract will contain the new users along with the users of the previous weekly extracts but with the possibility of the old users having different scores if they tried the quiz again. Each of the tabs will look like this:
[TABLE="width: 500"]
<tbody>[TR]
[TD]Firstname[/TD]
[TD]LastName[/TD]
[TD]ID[/TD]
[TD]Inst[/TD]
[TD]Dept[/TD]
[TD]Email[/TD]
[TD]Score[/TD]
[TD]Date[/TD]
[/TR]
</tbody>[/TABLE]

In a different tab I will also have a table with all the users who have tried the quiz in order for my VBA code to work(it will always contain the latest data).

On the second excel file I will copy all the users that does not have passed the quiz and do not have an ID. This file will look like this:
[TABLE="width: 500"]
<tbody>[TR]
[TD]Firsname[/TD]
[TD]Lastname[/TD]
[TD]ID[/TD]
[TD]Inst[/TD]
[TD]Dept[/TD]
[TD]Email[/TD]
[TD]Score[/TD]
[TD]Enroll[/TD]
[TD]C.Date[/TD]
[TD]Time[/TD]
[TD]Action[/TD]
[/TR]
</tbody>[/TABLE]

What I want to do is to search through all the tabs of the 1st workbook, find a match of a user that is on the second file containing the users that have failed the quiz, determine in which week he was firstly enrolled (based on which tab he was first located) and then copy the date cell of that tab and paste it to the enroll cell column of the second file, in order to subtract it from the current date and find for how long the user is enrolled.

Is it possible to be done with functions or with VBA?
Should VLOOKUP be used or MATCH/INDEX?

Thanks for any help because I am trying to complete this task for 10 days and still no luck

 

[bsquad]

I think I get what you are saying but, I think you are mixing up some of the terms i.e. tabs vs columns
It would be best if you had it like -

Workbook(1)
Tab = Participant Demographics
Column Headers = FirstName LastName ID...
Tab = Participant QuizScores
Column Headers = FirstName LastName ID FailedScore

Workbook(2)....

--or in a similar version of it - be very specific
and then explain how you want them linked

 

[Aldaron47]

Ok I will udate it to the format you propose

Workbook(DataExtract)
Tabs= Week1, week2, week3, week4, week5, week6, ...week24
Column Headers = Firstname | Lastname | ID | Inst | Dept | Email | Score | Date

Workbook( Failed)
Tabs = Unsuccesful List
Column Headers = Firstname | Lastname | ID | Inst | Dept | Email | Score | Enroll Date | Current Date | Time enrolled |Action

I want to be able and search through the week tabs of DataExtract workbook for every email address stored on the workbook(failed) and when the email address is found for the first time put a date at workbook(Failed) under the Enroll Date column.
To explain it further I am interested in finding under which week the user was first added to the quiz and then by subtracting the enrolled date from the current date, I will be able to find for how long the user was enrolled. The email address will be able to be found multiple times under the Workbook(DataExtract) because with each weekly report, the previous week's data will also be included.

I hope its easier to understand now

 

[bsquad]

ok I get what you are saying, but there are still a couple of things,
1. which workbook would be the "Master Sheet" by Master I mean the one you want to run code with / have the formulas in - I am assuming this is the Workbook("Failed")
2. I am also assuming in the Workbook("Failed") the Enrolled Date comes from Date in the Workbook("Data Extract")
3. Assumption that the Workbook("Failed") population or names have are populated by other means, or other means prior, or its done manually - I see that there is 'Score' on both Workbooks. but how does the Workbook("Failed") get populated




With that if all these are true, the below should work out -
The primary key or unique identifier how it searches is by 'ID' I was guess that this would be unique for each individual. I did add a column for Week, this shows which week they first showed up - but it is essentially just the tab name.
ID = Column C EnrollDate = Column H and Week = Column L


this will prompt a dialog box to choose your Data Extract file

Sub FailingAtLife()
    Dim eWorkbook, iWorkbook As Workbook
        Set eWorkbook = ThisWorkbook
    Dim eSheet, iSheet As Worksheet
    Dim x, i, k, z, t As Long
    Dim Cell, xlRange As Range
    Dim iWorkbookImportOpen As Variant


    Application.DisplayAlerts = False: Application.AskToUpdateLinks = False: Application.ScreenUpdating = False: Application.Calculation = xlCalculationManual
    ChDir eWorkbook.Path
    iWorkbookImportOpen = Application.GetOpenFilename(FileFilter:="Excel Workbooks (*.xlsx; *.xlsm; *.xls; *.xltm), *.xlsx; *.xlsm; *.xls; *.xltm", _
                                    Title:="Select Import File", MultiSelect:=True)
                                    On Error GoTo ExitSub
        For t = LBound(iWorkbookImportOpen) To UBound(iWorkbookImportOpen)
            Set iWorkbook = Workbooks.Open(Filename:=iWorkbookImportOpen(t), ReadOnly:=True)
            With eWorkbook.Worksheets(1)
            k = eWorkbook.Worksheets(1).UsedRange.Rows.Count
                For i = 2 To eWorkbook.Worksheets(1).UsedRange.Rows.Count
                    If Cells(i, 12) = "" Then
                    iWorkbook.Worksheets(1).Activate
                        For x = 1 To iWorkbook.Worksheets.Count
                        z = iWorkbook.Worksheets(x).Cells(Rows.Count, "C").End(xlUp).Row
                        Set xlRange = iWorkbook.Worksheets(x).Range("C2:C" & z)
                            For Each Cell In xlRange
                            If eWorkbook.Worksheets(1).Cells(i, 12) = "" Then
                                If Cell.Value = eWorkbook.Worksheets(1).Cells(i, 3) Then
                                    eWorkbook.Worksheets(1).Cells(i, 12).Value = iWorkbook.Worksheets(x).Name
                                    eWorkbook.Worksheets(1).Cells(i, 8).Value = iWorkbook.Worksheets(x).Cells(Cell.Row, 8).Value
                                End If
                            End If
                            Next Cell
                        Next x
                    End If
                Next i
            End With
            iWorkbook.Close SaveChanges = False
        Next t
        Application.DisplayAlerts = True: Application.AskToUpdateLinks = True: Application.ScreenUpdating = True: Application.Calculation = xlCalculationAutomatic
        
        MsgBox ("done")
    
ExitSub: Exit Sub
End Sub

 

[Aldaron47]

1) The function will be run on workbook ("Failed").
2) The enrolled date will be a result of the function and whether it finds the email address and in which tab.
3)Workbook("DataExtract") is manual copy paste from a database extract file.
Workbook("Failed") is populated by a macro that filters the data of Workbook("DataExtract") and the copies them to Workbook("Failed").
Most of the columns are the same. Workbook("Failed") contains the all of the columns of workbook("DataExtract") and some new ones that aren't included in workbook("DataExtract").

Unique ID in our case is the email address because not all of the users have ID.

I have tried to implement the following before I saw your response

 ActiveCell.FormulaR1C1 = _
                       "=IF(NOT(ISERROR(VLOOKUP(RC[-2],DataExtract.xlsx!Week1[Email Address],1,FALSE))),""06/11/2017"", IF(NOT(ISERROR(VLOOKUP(RC[-2],DataExtract.xlsx!Week2[Email Address],1,FALSE))),""13/11/2017"", IF(NOT(ISERROR(VLOOKUP(RC[-2],DataExtract.xlsx!Week3[Email Address],1,FALSE))),""20/11/2017"", IF(NOT(ISERROR(VLOOKUP(RC[-2],DataExtract.xlsx!Week4[Email Address],1,FALSE)))," & _
        """27/11/2017"", IF(NOT(ISERROR(VLOOKUP(RC[-2],DataExtract.xlsx!Week5[Email Address],1,FALSE))),""04/12/2017"", IF(NOT(ISERROR(VLOOKUP(RC[-2],DataExtract.xlsx!Week6[Email Address],1,FALSE))),""11/12/2017"", IF(NOT(ISERROR(VLOOKUP(RC[-2],DataExtract.xlsx!Week7[Email Address],1,FALSE))),""18/12/2017"", IF(NOT(ISERROR(VLOOKUP(RC[-2],DataExtract.xlsx!Week8[Email Address],1,FALSE)))," & _
        """25/12/2017"", IF(NOT(ISERROR(VLOOKUP(RC[-2],DataExtract.xlsx!Week9[Email Address],1,FALSE))),""01/01/2018"", IF(NOT(ISERROR(VLOOKUP(RC[-2],DataExtract.xlsx!Week9[Email Address],1,FALSE))),""08/01/2018"", IF(NOT(ISERROR(VLOOKUP(RC[-2],DataExtract.xlsx!Week10[Email Address],1,FALSE))),""15/01/2018"", IF(NOT(ISERROR(VLOOKUP(RC[-2],DataExtract.xlsx!Week11[Email Address],1,FALSE)))," & _
        """22/01/2018"", IF(NOT(ISERROR(VLOOKUP(RC[-2],DataExtract.xlsx!Week12[Email Address],1,FALSE))),""29/01/2018"", IF(NOT(ISERROR(VLOOKUP(RC[-2],DataExtract.xlsx!Week13[Email Address],1,FALSE))),""05/02/2018"", IF(NOT(ISERROR(VLOOKUP(RC[-2],DataExtract.xlsx!Week14[Email Address],1,FALSE))),""12/02/2018"", IF(NOT(ISERROR(VLOOKUP(RC[-2],DataExtract.xlsx!Week15[Email Address],1,FALSE)))," & _
        """19/02/2018"", IF(NOT(ISERROR(VLOOKUP(RC[-2],DataExtract.xlsx!Week16[Email Address],1,FALSE))),""16/02/2018"", IF(NOT(ISERROR(VLOOKUP(RC[-2],DataExtract.xlsx!Week17[Email Address],1,FALSE))),""05/03/2018"", IF(NOT(ISERROR(VLOOKUP(RC[-2],DataExtract.xlsx!Week18[Email Address],1,FALSE))),""12/03/2018"", IF(NOT(ISERROR(VLOOKUP(RC[-2],DataExtract.xlsx!Week19[Email Address],1,FALSE)))," & _
        """19/03/2018"", IF(NOT(ISERROR(VLOOKUP(RC[-2],DataExtract.xlsx!Week20[Email Address],1,FALSE))),""26/03/2018"", IF(NOT(ISERROR(VLOOKUP(RC[-2],DataExtract.xlsx!Week21[Email Address],1,FALSE))),""02/04/2018"", IF(NOT(ISERROR(VLOOKUP(RC[-2],DataExtract.xlsx!Week22[Email Address],1,FALSE))),""09/04/2018"", IF(NOT(ISERROR(VLOOKUP(RC[-2],DataExtract.xlsx!Week23[Email Address],1,FALSE))), & _
        """16/04/2018"", IF(NOT(ISERROR(VLOOKUP(RC[-2],DataExtract.xlsx!Week24[Email Address],1,FALSE))),""23/04/2018"", IF(NOT(ISERROR(VLOOKUP(RC[-2],DataExtract.xlsx!Week025[Email Address],1,FALSE))),""30/04/2018"","" ""))))))))))))))))))))))))))"

There are multiple "if not" functions that searches for the email on tabs and if they found it, stop and report the date but if not run another if not that does the same until the last (expected) week.
Note. RC[-2] is the way the auto recording macro decided to change the column F that is two columns earlier than where the function is located in H

 

[bsquad]

I changed the ranges and the 'Cell' declaration to the email instead of the ID, let me know if there is still something missing

Sub FailingAtLife()
    Dim eWorkbook, iWorkbook As Workbook
        Set eWorkbook = ThisWorkbook
    Dim eSheet, iSheet As Worksheet
    Dim x, i, k, z As Long
    Dim Cell, xlRange As Range
    Dim iWorkbookImportOpen As Variant


    Application.DisplayAlerts = False: Application.AskToUpdateLinks = False: Application.ScreenUpdating = False: Application.Calculation = xlCalculationManual
    ChDir eWorkbook.Path
    iWorkbookImportOpen = Application.GetOpenFilename(FileFilter:="Excel Workbooks (*.xlsx; *.xlsm; *.xls; *.xltm), *.xlsx; *.xlsm; *.xls; *.xltm", _
                                    Title:="Select Import File", MultiSelect:=True)
                                    On Error GoTo ExitSub
        For z = LBound(iWorkbookImportOpen) To UBound(iWorkbookImportOpen)
            Set iWorkbook = Workbooks.Open(Filename:=iWorkbookImportOpen(z), ReadOnly:=True)
            With eWorkbook.Worksheets(1)
            k = eWorkbook.Worksheets(1).UsedRange.Rows.Count
                For i = 2 To eWorkbook.Worksheets(1).UsedRange.Rows.Count
                    If Cells(i, 12) = "" Then
                    iWorkbook.Worksheets(1).Activate
                        For x = 1 To iWorkbook.Worksheets.Count
                        z = iWorkbook.Worksheets(x).Cells(Rows.Count, "F").End(xlUp).Row
                        Set xlRange = iWorkbook.Worksheets(x).Range("F2:F" & z)
                            For Each Cell In xlRange
                            If eWorkbook.Worksheets(1).Cells(i, 12) = "" Then
                                If Cell.Value = eWorkbook.Worksheets(1).Cells(i, 6) Then
                                    eWorkbook.Worksheets(1).Cells(i, 12).Value = iWorkbook.Worksheets(x).Name
                                    eWorkbook.Worksheets(1).Cells(i, 8).Value = iWorkbook.Worksheets(x).Cells(Cell.Row, 8).Value
                                End If
                            End If
                            Next Cell
                        Next x
                    End If
                Next i
            End With
            iWorkbook.Close SaveChanges = False
        Next z
        Application.DisplayAlerts = True: Application.AskToUpdateLinks = True: Application.ScreenUpdating = True: Application.Calculation = xlCalculationAutomatic
        
        MsgBox ("done")
    
ExitSub: Exit Sub
End Sub

Honestly this could be done with a formula, you were on the right track with INDEX/MATCH but since you would have duplicates from sheet to sheet you'd have to consider things like tiebreaker logic, distinct formula arrays, and INDIRECTs. Dependent on how many participants you have on your sheet; and Excel typically defaults to automatic formula calc (or if you have manual calc on, you always forget to turn it back on and think that the formulas aren't working - or at least I do that all the time still and I use Excel all day) it could really bog down your bandwidth/CPU/resources and freeze up your sheet.