Fill Blank Excel Report Number Cells by Matching Data on Another Sheet
Question details
Fill blank report-number cells by matching each company name and incident date against records on another worksheet.
- Product
- Microsoft Excel VBA macro
- Device & OS
- not provided
- Scenario
- A workbook contains a source worksheet with report numbers and a target worksheet where report-number cells are blank.
- Observed behavior
- The target worksheet needs the matching report number returned when the company name and incident date match a source record.
Confirm which columns contain the company name, incident date, and report number on both worksheets. Make sure incident dates are real Excel dates rather than text values, and use the same date value on both sheets.
Use a VBA Dictionary to Fill Blank Report Numbers from a Source Sheet
Use this method when the company name and incident date together uniquely identify the correct report number in another worksheet.
This macro creates a lookup key from the company name and the numeric incident-date value. It reads records from the Source sheet, then fills only blank report-number cells on the Target sheet when it finds a matching key.
The example assumes company names are in column A, incident dates are in column B, and report numbers are in column C on both worksheets. Update the sheet names or column references if your workbook uses different locations.
In Excel, press Alt+F11, select Insert > Module, and paste the macro into the new code window.
Paste this code: Sub FillBlankReportNumbers() Dim wsSource As Worksheet, wsTarget As Worksheet Dim dict As Object Dim lastSourceRow As Long, lastTargetRow As Long Dim r As Long, key As String Set wsSource = ThisWorkbook.Worksheets("Source") Set wsTarget = ThisWorkbook.Worksheets("Target") Set dict = CreateObject("Scripting.Dictionary") lastSourceRow = wsSource.Cells(wsSource.Rows.Count, "A").End(xlUp).Row For r = 2 To lastSourceRow If Len(Trim(wsSource.Cells(r, "A").Value)) > 0 And IsDate(wsSource.Cells(r, "B").Value) Then key = LCase(Trim(wsSource.Cells(r, "A").Value)) & "|" & CLng(CDate(wsSource.Cells(r, "B").Value)) If Not dict.Exists(key) Then dict.Add key, wsSource.Cells(r, "C").Value End If Next r lastTargetRow = wsTarget.Cells(wsTarget.Rows.Count, "A").End(xlUp).Row For r = 2 To lastTargetRow If Len(Trim(wsTarget.Cells(r, "C").Value)) = 0 And IsDate(wsTarget.Cells(r, "B").Value) Then key = LCase(Trim(wsTarget.Cells(r, "A").Value)) & "|" & CLng(CDate(wsTarget.Cells(r, "B").Value)) If dict.Exists(key) Then wsTarget.Cells(r, "C").Value = dict(key) End If Next r End Sub
Replace "Source" and "Target" in the code with your worksheet tab names. If the fields are in different columns, change each "A", "B", and "C" reference to the appropriate company, date, and report-number columns.
Return to Excel, press Alt+F8, select FillBlankReportNumbers, and click Run. The macro fills blank cells in Target column C only when both matching fields are found.
Match Sheet Data and Fill Missing Report Numbers in WPS Spreadsheet
WPS Spreadsheet provides a familiar worksheet interface for reviewing matched company and date fields, filling missing report numbers, and working with macro-enabled Excel files. Open the workbook in WPS Office, verify the matched records, and use its spreadsheet tools to complete and save the file in a compatible format.
- 1. Open the workbook in WPS Spreadsheet: Launch WPS Office, choose Spreadsheets, and open the workbook containing the Source and Target worksheets.
- 2. Check the matching columns: Select the Source and Target sheet tabs and confirm that company names, incident dates, and report-number columns contain the expected values.
- 3. Use macro tools when available: Open the Developer or Macro tools in WPS Spreadsheet, then add or run the compatible VBA procedure after confirming the worksheet names and column references.
- 4. Verify filled records: Filter the target report-number column for blank cells and review the rows that were populated from matching company and incident-date values.
- 5. Save in the required Excel format: Choose Menu > Save As and select Excel Workbook (*.xlsx) or Excel Macro-Enabled Workbook (*.xlsm) to preserve the required workbook format.
Frequently Asked Questions
Why does the macro use both the company name and incident date as the lookup key?
Using both fields prevents a report number from being matched by company name alone when the same company has multiple incidents. The macro combines the values into one dictionary key.
Will the macro overwrite report numbers that are already entered?
No. The target-row condition checks whether the report-number cell in column C is blank before it writes a value. Remove or change that condition if you need to replace existing values.
What should I do if dates look the same but records do not match?
Check whether either worksheet stores the date as text. Convert the column to real dates using Excel's Data > Text to Columns or a date conversion formula, then rerun the macro.
How can I change the report-number column used by the VBA code?
Replace the column C references in wsSource.Cells(r, "C") and wsTarget.Cells(r, "C") with the letter or column number containing your report numbers.
