Two part problem: 1- If a cell in a column in blank, return value in a cell in the same row, but different column

[excellearner17]

Second part is based on the row number of the returned cell value, match other two columns. Maybe an example will make this more clear:

From the raw data I want to extract the program name whenever there is no responsible person assigned (cell blank), After I want to be able to match the sub-program and task with the right program
Key facts:
- The list is long, so I need a formula that can continue to scan down, even if more rows are added
- The raw data and output will be in separate tabs

Raw data

Program	Sub-program	Task	Responsible person
Office	Supplies	Contact suppliers	Kevin
Office	Supplies	Pay suppliers	Jessica
Office	Recruitment	Contact agencies
Home	*******	Monthly shopping	Paul

<tbody>
</tbody>

Output

Program	Sub-program	Task
Office	Recruitment	Contact agencies

<tbody>
</tbody>

Any help much much appreciated!
****** id="cke_pastebin" style="position: absolute; top: 214.667px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">

Program

Sub-program

Task

<tbody>
</tbody>

</body>

 

[excellearner17]

Hi,

Could I have it updated with all three options? Also is there any downside or thing I need to be aware of using macros?
For example, I have another table to create which has a similar problem to this - could I use the same macro multiple times?

 

[mumps]

Place the following 3 macros in the code module for ThisWorkbook:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Application.ScreenUpdating = False
    Dim LastRow As Long
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Sheets("Sheet2").UsedRange.ClearContents
    Sheets("Sheet1").Rows(1).EntireRow.Copy Sheets("Sheet2").Cells(1, 1)
    Sheets("Sheet1").Range("A1:D" & LastRow).AutoFilter Field:=4, Criteria1:="="
    Sheets("Sheet1").Range("A2:D" & LastRow).SpecialCells(xlCellTypeVisible).EntireRow.Copy Sheets("Sheet2").Cells(Sheets("Sheet2").Rows.Count, "A").End(xlUp).Offset(1, 0)
    If Sheets("Sheet1").AutoFilterMode = True Then Sheets("Sheet1").AutoFilterMode = False
    Application.ScreenUpdating = True
End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Application.ScreenUpdating = False
    Dim LastRow As Long
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Sheets("Sheet2").UsedRange.ClearContents
    Sheets("Sheet1").Rows(1).EntireRow.Copy Sheets("Sheet2").Cells(1, 1)
    Sheets("Sheet1").Range("A1:D" & LastRow).AutoFilter Field:=4, Criteria1:="="
    Sheets("Sheet1").Range("A2:D" & LastRow).SpecialCells(xlCellTypeVisible).EntireRow.Copy Sheets("Sheet2").Cells(Sheets("Sheet2").Rows.Count, "A").End(xlUp).Offset(1, 0)
    If Sheets("Sheet1").AutoFilterMode = True Then Sheets("Sheet1").AutoFilterMode = False
    Application.ScreenUpdating = True
End Sub

Private Sub Workbook_Open()
    Application.ScreenUpdating = False
    Dim LastRow As Long
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Sheets("Sheet2").UsedRange.ClearContents
    Sheets("Sheet1").Rows(1).EntireRow.Copy Sheets("Sheet2").Cells(1, 1)
    Sheets("Sheet1").Range("A1:D" & LastRow).AutoFilter Field:=4, Criteria1:="="
    Sheets("Sheet1").Range("A2:D" & LastRow).SpecialCells(xlCellTypeVisible).EntireRow.Copy Sheets("Sheet2").Cells(Sheets("Sheet2").Rows.Count, "A").End(xlUp).Offset(1, 0)
    If Sheets("Sheet1").AutoFilterMode = True Then Sheets("Sheet1").AutoFilterMode = False
    Application.ScreenUpdating = True
End Sub

You will notice that they are exactly the same except for the type of event. You can unse the same macros multiple times provided that the data in your worksheet is organized in an identical manner as your current data.