Condense formula

McCainM

New Member
Joined
Oct 20, 2023
Messages
2
Office Version
  1. 2021
Platform
  1. Windows
I am looking for a way to be more efficient or condense the following formula. The goal is for it to search 2 consecutive cells to see if they contain the character in cell Settings!$A$5,R5. If yes, then it will return the value of another referenced cell. If not, it will look at the next two consecutive cells. It continues until it finds two consecutive cells that contains the character in cell Settings!$A$5,R5. The formula works, I just feel like there has to be a more efficient way to do it.

=IF(And((ISNUMBER(SEARCH(Settings!$A$5,Q5))),(ISNUMBER(SEARCH(Settings!$A$5,R5)))),Settings!$F$6,IF(And((ISNUMBER(SEARCH(Settings!$A$5,R5))),(ISNUMBER(SEARCH(Settings!$A$5,S5)))),Settings!$F$6,IF(And((ISNUMBER(SEARCH(Settings!$A$5,S5))),(ISNUMBER(SEARCH(Settings!$A$5,T5)))),Settings!$F$6,IF(And((ISNUMBER(SEARCH(Settings!$A$5,T5))),(ISNUMBER(SEARCH(Settings!$A$5,U5)))),Settings!$F$6,IF(And((ISNUMBER(SEARCH(Settings!$A$5,U5))),(ISNUMBER(SEARCH(Settings!$A$5,V5)))),Settings!$F$6,IF(And((ISNUMBER(SEARCH(Settings!$A$5,V5))),(ISNUMBER(SEARCH(Settings!$A$5,W5)))),Settings!$F$6,IF(And((ISNUMBER(SEARCH(Settings!$A$5,J2))),(ISNUMBER(SEARCH(Settings!$A$5,K2)))),Settings!$F$5,IF(And((ISNUMBER(SEARCH(Settings!$A$5,K2))),(ISNUMBER(SEARCH(Settings!$A$5,L2)))),Settings!$F$5,IF(And((ISNUMBER(SEARCH(Settings!$A$5,L2))),(ISNUMBER(SEARCH(Settings!$A$5,M2)))),Settings!$F$5,IF(And((ISNUMBER(SEARCH(Settings!$A$5,M2))),(ISNUMBER(SEARCH(Settings!$A$5,N2)))),Settings!$F$5,IF(And((ISNUMBER(SEARCH(Settings!$A$5,N2))),(ISNUMBER(SEARCH(Settings!$A$5,O2)))),Settings!$F$5,IF(And((ISNUMBER(SEARCH(Settings!$A$5,O2))),(ISNUMBER(SEARCH(Settings!$A$5,P2)))),Settings!$F$5,IF(And((ISNUMBER(SEARCH(Settings!$A$5,C2))),(ISNUMBER(SEARCH(Settings!$A$5,D2)))),Settings!$F$4,IF(And((ISNUMBER(SEARCH(Settings!$A$5,D2))),(ISNUMBER(SEARCH(Settings!$A$5,E2)))),Settings!$F$4,IF(And((ISNUMBER(SEARCH(Settings!$A$5,E2))),(ISNUMBER(SEARCH(Settings!$A$5,F2)))),Settings!$F$4,IF(And((ISNUMBER(SEARCH(Settings!$A$5,F2))),(ISNUMBER(SEARCH(Settings!$A$5,G2)))),Settings!$F$4,IF(And((ISNUMBER(SEARCH(Settings!$A$5,G2))),(ISNUMBER(SEARCH(Settings!$A$5,H2)))),Settings!$F$4,IF(And((ISNUMBER(SEARCH(Settings!$A$5,H2))),(ISNUMBER(SEARCH(Settings!$A$5,I2)))),Settings!$F$4,""))))))))))))))))))
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
Settings!$A$5
You are searching for a cell value throughout. Does it remain constant or it keep changing?

What exactly that cell value is that you search in cells - if you can give some suitable example to understand it better
 
Upvote 0
You are searching for a cell value throughout. Does it remain constant or it keep changing?

What exactly that cell value is that you search in cells - if you can give some suitable example to understand it better
It is currently a ✔ but is meant to be dynamic in that the user could change it. With that said, whether the value remains a ✔ or is changed by the user, the search for that character stays constant throughout the formula.
 
Upvote 0
Try this and revert -

Excel Formula:
=IFS(
Countifs(Q5:R5,"*"&Settings!$A$5&"*"),Settings!$F$6,
Countifs(R5:S5,"*"&Settings!$A$5&"*"),Settings!$F$6,
Countifs(S5:T5,"*"&Settings!$A$5&"*"),Settings!$F$6,
Countifs(T5:U5,"*"&Settings!$A$5&"*"),Settings!$F$6,
Countifs(U5:V5,"*"&Settings!$A$5&"*"),Settings!$F$6,
Countifs(V5:W5,"*"&Settings!$A$5&"*"),Settings!$F$6,
Countifs(J2:K2,"*"&Settings!$A$5&"*"),Settings!$F$5,
Countifs(K2:L2,"*"&Settings!$A$5&"*"),Settings!$F$5,
Countifs(L2:M2,"*"&Settings!$A$5&"*"),Settings!$F$5,
Countifs(M2:N2,"*"&Settings!$A$5&"*"),Settings!$F$5,
Countifs(N2:O2,"*"&Settings!$A$5&"*"),Settings!$F$5,
Countifs(O2:P2,"*"&Settings!$A$5&"*"),Settings!$F$5,
Countifs(C2:D2,"*"&Settings!$A$5&"*"),Settings!$F$4,
Countifs(D2:E2,"*"&Settings!$A$5&"*"),Settings!$F$4,
Countifs(E2:F2,"*"&Settings!$A$5&"*"),Settings!$F$4,
Countifs(F2:G2,"*"&Settings!$A$5&"*"),Settings!$F$4,
Countifs(G2:H2,"*"&Settings!$A$5&"*"),Settings!$F$4,
Countifs(H2:I2,"*"&Settings!$A$5&"*"),Settings!$F$4,True,"")
 
Upvote 0
If you were open to a VBA solution, then please try this on a copy of your workbook (put in a standard module). This code puts the answer into cell A1 - change that to suit your needs.
VBA Code:
Option Explicit
Sub CheckConsec()
    Dim ws As Worksheet
    Set ws = Worksheets("Settings")       '<-- *** Check sheet name ***
    Dim a, b, c, i As Long, s As String
    a = ws.Range("Q5:W5"): a = Application.WorksheetFunction.Transpose(a)
    b = ws.Range("J2:P2"): b = Application.WorksheetFunction.Transpose(b)
    c = ws.Range("C2:I2"): c = Application.WorksheetFunction.Transpose(c)
    s = ws.Range("A5").Value
    
    For i = LBound(a) To UBound(a) - 1
        If a(i, 1) Like "*" & s & "*" And a(i + 1, 1) Like "*" & s & "*" Then
            ws.Range("A1") = ws.Range("F6")
            Exit Sub
        End If
    Next i
    
    For i = LBound(b) To UBound(b) - 1
        If b(i, 1) Like "*" & s & "*" And b(i + 1, 1) Like "*" & s & "*" Then
            ws.Range("A1") = ws.Range("F5")
            Exit Sub
        End If
    Next i
    
    For i = LBound(c) To UBound(c) - 1
        If c(i, 1) Like "*" & s & "*" And c(i + 1, 1) Like "*" & s & "*" Then
            ws.Range("A1") = ws.Range("F4")
            Exit Sub
        End If
    Next i
    ws.Range("A1") = "No consecutive matches found"
End Sub
 
Upvote 0
Try
Excel Formula:
=IF(SUMPRODUCT(ISNUMBER(SEARCH(Settings!$A$5,Q5:V5))*ISNUMBER(SEARCH(Settings!$A$5,R5:W5)))>0,Settings!$F$6,IF(SUMPRODUCT(ISNUMBER(SEARCH(Settings!$A$5,J5:O5))*ISNUMBER(SEARCH(Settings!$A$5,K5:P5)))>0,Settings!$F$5,IF(SUMPRODUCT(ISNUMBER(SEARCH(Settings!$A$5,C5:H5))*ISNUMBER(SEARCH(Settings!$A$5,D5:I5)))>0,Settings!$F$4,"")))
 
Upvote 0
Try this and revert -

Excel Formula:
=IFS(
Countifs(Q5:R5,"*"&Settings!$A$5&"*"),Settings!$F$6,
Countifs(R5:S5,"*"&Settings!$A$5&"*"),Settings!$F$6,
Countifs(S5:T5,"*"&Settings!$A$5&"*"),Settings!$F$6,
Countifs(T5:U5,"*"&Settings!$A$5&"*"),Settings!$F$6,
Countifs(U5:V5,"*"&Settings!$A$5&"*"),Settings!$F$6,
Countifs(V5:W5,"*"&Settings!$A$5&"*"),Settings!$F$6,
Countifs(J2:K2,"*"&Settings!$A$5&"*"),Settings!$F$5,
Countifs(K2:L2,"*"&Settings!$A$5&"*"),Settings!$F$5,
Countifs(L2:M2,"*"&Settings!$A$5&"*"),Settings!$F$5,
Countifs(M2:N2,"*"&Settings!$A$5&"*"),Settings!$F$5,
Countifs(N2:O2,"*"&Settings!$A$5&"*"),Settings!$F$5,
Countifs(O2:P2,"*"&Settings!$A$5&"*"),Settings!$F$5,
Countifs(C2:D2,"*"&Settings!$A$5&"*"),Settings!$F$4,
Countifs(D2:E2,"*"&Settings!$A$5&"*"),Settings!$F$4,
Countifs(E2:F2,"*"&Settings!$A$5&"*"),Settings!$F$4,
Countifs(F2:G2,"*"&Settings!$A$5&"*"),Settings!$F$4,
Countifs(G2:H2,"*"&Settings!$A$5&"*"),Settings!$F$4,
Countifs(H2:I2,"*"&Settings!$A$5&"*"),Settings!$F$4,True,"")
Just Realized we can further shorten it like below. I generally break a long formula in multiple lines to make it easy to read, understand or edit, if the need be.

Check and Revert -

Excel Formula:
=IFS(
OR(COUNTIFS(Q5:R5,"*"&Settings!$A$5&"*"),
   COUNTIFS(R5:S5,"*"&Settings!$A$5&"*"),
   COUNTIFS(S5:T5,"*"&Settings!$A$5&"*"),
   COUNTIFS(T5:U5,"*"&Settings!$A$5&"*"),
   COUNTIFS(U5:V5,"*"&Settings!$A$5&"*"),
   COUNTIFS(V5:W5,"*"&Settings!$A$5&"*")),Settings!$F$6,
OR(COUNTIFS(J2:K2,"*"&Settings!$A$5&"*"),
   COUNTIFS(K2:L2,"*"&Settings!$A$5&"*"),
   COUNTIFS(L2:M2,"*"&Settings!$A$5&"*"),
   COUNTIFS(M2:N2,"*"&Settings!$A$5&"*"),
   COUNTIFS(N2:O2,"*"&Settings!$A$5&"*"),
   COUNTIFS(O2:P2,"*"&Settings!$A$5&"*")),Settings!$F$5,
OR(COUNTIFS(C2:D2,"*"&Settings!$A$5&"*"),
   COUNTIFS(D2:E2,"*"&Settings!$A$5&"*"),
   COUNTIFS(E2:F2,"*"&Settings!$A$5&"*"),
   COUNTIFS(F2:G2,"*"&Settings!$A$5&"*"),
   COUNTIFS(G2:H2,"*"&Settings!$A$5&"*"),
   COUNTIFS(H2:I2,"*"&Settings!$A$5&"*")),Settings!$F$4,True,"")
 
Upvote 0
If you use Office 365 you could (H)stack the ranges and add a blank in between each of the ranges and (V)stack this range excluding the last and first value and use FIND (case sensitive, or SEARCH, case insensitive ) on that array and convert the array of booleans to numeric value, so it can be used in MMULT, which checks row-by-row in the stacked array if both values contain the search value. (If you don't want to search or find the value inside the cell's values, but you want it to equal, the formula could be simplified)


Excel Formula:
=LET(a,HSTACK(Q5:W5,"",J2:P2,"",C2:I2),OR(MMULT({1,1},N(ISNUMBER(FIND(A5,VSTACK(DROP(a,,-1),DROP(a,,1))))))=2))
 
Upvote 0
There was a major error in my previous replies to your post.
COUNTIFS must be tested by a number to validate the result. Here is the corrected version. Try it and revert -

Excel Formula:
=IFS(
OR(COUNTIFS(Q5:R5,"*"&Settings!$A$5&"*")=2,
   COUNTIFS(R5:S5,"*"&Settings!$A$5&"*")=2,
   COUNTIFS(S5:T5,"*"&Settings!$A$5&"*")=2,
   COUNTIFS(T5:U5,"*"&Settings!$A$5&"*")=2,
   COUNTIFS(U5:V5,"*"&Settings!$A$5&"*")=2,
   COUNTIFS(V5:W5,"*"&Settings!$A$5&"*")=2),Settings!$F$6,
OR(COUNTIFS(J2:K2,"*"&Settings!$A$5&"*")=2,
   COUNTIFS(K2:L2,"*"&Settings!$A$5&"*")=2,
   COUNTIFS(L2:M2,"*"&Settings!$A$5&"*")=2,
   COUNTIFS(M2:N2,"*"&Settings!$A$5&"*")=2,
   COUNTIFS(N2:O2,"*"&Settings!$A$5&"*")=2,
   COUNTIFS(O2:P2,"*"&Settings!$A$5&"*")=2),Settings!$F$5,
OR(COUNTIFS(C2:D2,"*"&Settings!$A$5&"*")=2,
   COUNTIFS(D2:E2,"*"&Settings!$A$5&"*")=2,
   COUNTIFS(E2:F2,"*"&Settings!$A$5&"*")=2,
   COUNTIFS(F2:G2,"*"&Settings!$A$5&"*")=2,
   COUNTIFS(G2:H2,"*"&Settings!$A$5&"*")=2,
   COUNTIFS(H2:I2,"*"&Settings!$A$5&"*")=2),Settings!$F$4,True,"")
 
Upvote 0

Forum statistics

Threads
1,223,894
Messages
6,175,250
Members
452,623
Latest member
Techenthusiast

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