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

rwm

New Member
Joined
Jun 6, 2023
Messages
4
Office Version
  1. 365
Platform
  1. Windows
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!
 

Attachments

  • Screenshot (11).jpg
    Screenshot (11).jpg
    90.8 KB · Views: 9

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
Hi @rwm
Welcome to the MrExcel forum. Please accept my warmest greetings and sincere hope that all is well.​

Try the following macro:

VBA Code:
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
--------------​
 
Upvote 0

Forum statistics

Threads
1,223,886
Messages
6,175,196
Members
452,616
Latest member
intern444

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top