Condense formula

M

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

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
Sort by date Sort by votes
McCainM said:
Settings!$A$5
Click to expand...
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
SanjayGMusafir said:
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
Click to expand...
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
SanjayGMusafir said:
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,"")
Click to expand...
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
You must log in or register to reply here.

Similar threads

I
  • Solved
Date formula not applied the same to all Listbox entries
Replies
2
Views
47
ipbr21054
I
Cliffork
  • Solved
Print to PDF Macro, or possibly more
2 3
Replies
21
Views
393
mumps
mumps
gtd526
  • Question Question
convert cell value to formula
Replies
8
Views
370
Fluff
Fluff
gtd526
  • Question Question
macro to add correct number of ")" at end of nested IF statement
Replies
1
Views
311
gtd526
gtd526
B
  • Question Question
How to make this IF formula work?
Replies
1
Views
252
Fluff
Fluff

Forum statistics

Threads
1,224,823
Messages
6,181,170
Members
453,021
Latest member
Justyna P

Share this page

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