Returning a value in a cell based on text in a different set of multiple cells

[rwm]

Hi! I need some help creating a formula to achieve the following:

The goal is to return "Pass" or "Fail" in column E based on the information in column F. Column E cells are merged to show ownership over certain cells in column F (ex. E2 returns value based on information in F2:F3, E5 returns value based on information in F5:F7). I want to return "Pass" in Column E if the respective cells in Column F all show "Accurate" and return "Fail" if even 1 of the cells in Column F shows "Inaccurate". For example, E2:3 should return "Pass", E4 should return "Fail", E8:10 should return "Fail, etc)

Thanks!

 

[DanteAmor]

Hi @rwm

Welcome to the MrExcel forum. Please accept my warmest greetings and sincere hope that all is well.​

Try the following macro:

Sub Returning_a_value_v1()
  Dim i&, j&, f As Range
 
  For i = 2 To Range("F" & Rows.Count).End(3).Row
    With Range("E" & i)
      If .MergeCells Then
        j = .MergeArea.Rows.Count
        Set f = Range("F" & i).Resize(j).Find("Inaccurate", , xlValues, xlWhole, , , False)
        If Not f Is Nothing Then .Value = "Fail" Else .Value = "Pass"
        i = i + j - 1
      Else
        If Range("F" & i).Value = "Inaccurate" Then .Value = "Fail" Else .Value = "Pass"
      End If
    End With
  Next
End Sub

--------------
Let me know the result and I'll get back to you as soon as I can.
Cordially
Dante Amor
--------------​