SOLVED

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

Copper Contributor

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:

Golfstone1121_0-1711995409157.png

 

Intended Result:

Golfstone1121_1-1711995435818.png

Thanks in advance for your help!

 

2 Replies
best response confirmed by Hans Vogelaar (MVP)
Solution

@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.

Thank you, this will get the job done!  Sorry for the late response.@OliverScheurich 

1 best response

Accepted Solutions
best response confirmed by Hans Vogelaar (MVP)
Solution

@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.

View solution in original post