Forum Discussion

Golfstone1121's avatar
Golfstone1121
Copper Contributor
Apr 01, 2024

Validating data between two sheets to then fill blank cells on one sheet.

Hi All,
I'm attempting to populate blank cells on one sheet with information from another sheet. I need to match the information in sheet 2 to accurately fill the blank cells in sheet 1. The common fields between the sheets are company name and incident date.


Sheet 1: Name column, date column, and report number column that is likely empty, but not all.
Sheet 2: Has a name column, date column, and the report number. This is what I need to populate/match to sheet 1.


Basically, I need yellow cells (sheet 1) to be populated by the blue cells from sheet 2. Sorry, I can't provide the worksheet due to sensitive information but hopefully, the example below gives a good representation of my question.

 

Current Issue:

 

Intended Result:

Thanks in advance for your help!

 

Formulas and Functions
  • OliverScheurich's avatar
    OliverScheurich
    Apr 01, 2024

    Golfstone1121 

    Sub incidentreports()

    Dim Name_Incident As Object, Name_incident_missing_report As Range
    Dim i As Long, scriptingKey As String
    Dim resultData
    Set Name_Incident = CreateObject("scripting.dictionary")
    Set Name_incident_missing_report = Range("A2:C" & Cells(Rows.Count, 1).End(xlUp).Row)
        
    resultData = Name_incident_missing_report.Value
    
    For i = LBound(resultData) To UBound(resultData)
        scriptingKey = resultData(i, 1) & "|" & resultData(i, 2)
        Name_Incident(scriptingKey) = resultData(i, 3)
    Next i
    
    Set Name_incident_missing_report = Range("F2:H" & Cells(Rows.Count, "F").End(xlUp).Row)
    resultData = Name_incident_missing_report.Value
    
    For i = LBound(resultData) To UBound(resultData)
        scriptingKey = resultData(i, 1) & "|" & resultData(i, 2)
        If Name_Incident.Exists(scriptingKey) Then _
            resultData(i, 3) = Name_Incident(scriptingKey)
    Next i
    
    Name_incident_missing_report.Value = resultData
    
End Sub

    In the attached file you can click the button in cell K2 to run the macro which fills the missing data in column H with the corresponding values from column C.

  • OliverScheurich's avatar
    OliverScheurich
    Gold Contributor
    Apr 01, 2024

    Golfstone1121 

    Sub incidentreports()

    Dim Name_Incident As Object, Name_incident_missing_report As Range
    Dim i As Long, scriptingKey As String
    Dim resultData
    Set Name_Incident = CreateObject("scripting.dictionary")
    Set Name_incident_missing_report = Range("A2:C" & Cells(Rows.Count, 1).End(xlUp).Row)
        
    resultData = Name_incident_missing_report.Value
    
    For i = LBound(resultData) To UBound(resultData)
        scriptingKey = resultData(i, 1) & "|" & resultData(i, 2)
        Name_Incident(scriptingKey) = resultData(i, 3)
    Next i
    
    Set Name_incident_missing_report = Range("F2:H" & Cells(Rows.Count, "F").End(xlUp).Row)
    resultData = Name_incident_missing_report.Value
    
    For i = LBound(resultData) To UBound(resultData)
        scriptingKey = resultData(i, 1) & "|" & resultData(i, 2)
        If Name_Incident.Exists(scriptingKey) Then _
            resultData(i, 3) = Name_Incident(scriptingKey)
    Next i
    
    Name_incident_missing_report.Value = resultData
    
End Sub

    In the attached file you can click the button in cell K2 to run the macro which fills the missing data in column H with the corresponding values from column C.

Resources