conditional identifying with 0 or 1

J

JoaoGabriel

New Member
Joined
Jun 6, 2021
Messages
15
Office Version
  1. 2019
Platform
  1. Windows
I'm trying to create a macro that identifies with 1 when there is a error which follows two conditions:
1) if the value is SUPRESSED: the next value in the same row have to be SUPRESSED or CONNECTED
2) if the value is FEASIBLE: the next value in the same row have to be FEASIBLE or CONNECTED and before a FEASIBLE value there can be no value beyond FEASIBLE
Ignoring blank cells in both cases and fill with 0 when the error doesnt occur. I tried to create some macros but couldn't ignore blank values and apply to multiple lines at once.

The data looks like this and the error column is the expected result:
data.png


Can anyone help?
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
Sort by date Sort by votes
Can a SUPRESSED be on with all the rest blanks or must there always be something else in the row with it?

Can a FEASIBLE be on with all the rest blanks or must there always be something else in the row with it?
 
Upvote 0
Here's a formula that might work for you:

Book1
ABCDEFGHIJKLMN
1Error
2SUPPRESSEDSUPPRESSEDSUPPRESSEDSUPPRESSEDSUPPRESSEDSUPPRESSEDSUPPRESSEDSUPPRESSEDSUPPRESSEDSUPPRESSEDSUPPRESSEDSUPPRESSED0
3SUPPRESSEDSUPPRESSEDSUPPRESSEDSUPPRESSEDSUPPRESSEDSUPPRESSEDSUPPRESSEDSUPPRESSEDCONNECTEDCONNECTED0
4SUPPRESSEDSUPPRESSEDSUPPRESSEDCONNECTEDCONNECTEDCONNECTEDCONNECTEDCONNECTEDCONNECTED0
5SUPPRESSEDCONNECTED0
6CONNECTED0
7SUPPRESSED0
8FEASIBLEFEASIBLEFEASIBLEFEASIBLEFEASIBLEFEASIBLEFEASIBLEFEASIBLEFEASIBLEFEASIBLEFEASIBLEFEASIBLE0
9SUPPRESSEDSUPPRESSEDSUPPRESSEDSUPPRESSEDSUPPRESSEDSUPPRESSEDSUPPRESSEDSUPPRESSEDSUPPRESSEDSUPPRESSED0
10CONNECTEDCONNECTEDCONNECTEDCONNECTEDCONNECTEDCONNECTEDCONNECTEDCONNECTEDCONNECTEDCONNECTEDCONNECTEDCONNECTED0
11CUTTEDCUTTEDCUTTEDCUTTEDCUTTEDCUTTEDCUTTEDCUTTEDCUTTEDCUTTEDCUTTEDCUTTED0
12SUPPRESSEDSUPPRESSEDSUPPRESSEDFEASIBLEFEASIBLE1
13SUPPRESSEDCUTTEDCUTTED1
14FEASIBLEFEASIBLEFEASIBLEFEASIBLEFEASIBLEFEASIBLEFEASIBLE0
15FEASIBLEFEASIBLEFEASIBLEFEASIBLEFEASIBLEFEASIBLEFEASIBLEFEASIBLEFEASIBLECONNECTEDCONNECTEDCONNECTED0
16FEASIBLEFEASIBLECONNECTEDFEASIBLEFEASIBLEFEASIBLEFEASIBLEFEASIBLEFEASIBLEFEASIBLEFEASIBLECONNECTED1
17FEASIBLEFEASIBLEFEASIBLECONNECTEDCONNECTEDCONNECTED0
18SUPPRESSEDFEASIBLEFEASIBLEFEASIBLEFEASIBLEFEASIBLEFEASIBLEFEASIBLEFEASIBLEFEASIBLEFEASIBLEFEASIBLE1
Sheet23
Cell Formulas
RangeFormula
N2:N18N2=IF(OR(ISNUMBER(SEARCH({"SUPPRESSED|CUTTED","SUPPRESSED|FEASIBLE","FEASIBLE|SUPPRESSED","FEASIBLE|CUTTED"},TEXTJOIN("|",1,B2:M2))),IFERROR(AGGREGATE(14,6,COLUMN(B2:M2)/(B2:M2="FEASIBLE"),1)-AGGREGATE(15,6,COLUMN(B2:M2)/((B2:M2="CONNECTED")+(B2:M2="SUPPRESSED")),1)>0,0)),1,0)
 
Upvote 0
Rick Rothstein said:
Can a SUPRESSED be on with all the rest blanks or must there always be something else in the row with it?

Can a FEASIBLE be on with all the rest blanks or must there always be something else in the row with it?
Click to expand...
Yes, there may be white spaces between them
 
Upvote 0
Eric W said:
Here's a formula that might work for you:

Book1
ABCDEFGHIJKLMN
1Error
2SUPPRESSEDSUPPRESSEDSUPPRESSEDSUPPRESSEDSUPPRESSEDSUPPRESSEDSUPPRESSEDSUPPRESSEDSUPPRESSEDSUPPRESSEDSUPPRESSEDSUPPRESSED0
3SUPPRESSEDSUPPRESSEDSUPPRESSEDSUPPRESSEDSUPPRESSEDSUPPRESSEDSUPPRESSEDSUPPRESSEDCONNECTEDCONNECTED0
4SUPPRESSEDSUPPRESSEDSUPPRESSEDCONNECTEDCONNECTEDCONNECTEDCONNECTEDCONNECTEDCONNECTED0
5SUPPRESSEDCONNECTED0
6CONNECTED0
7SUPPRESSED0
8FEASIBLEFEASIBLEFEASIBLEFEASIBLEFEASIBLEFEASIBLEFEASIBLEFEASIBLEFEASIBLEFEASIBLEFEASIBLEFEASIBLE0
9SUPPRESSEDSUPPRESSEDSUPPRESSEDSUPPRESSEDSUPPRESSEDSUPPRESSEDSUPPRESSEDSUPPRESSEDSUPPRESSEDSUPPRESSED0
10CONNECTEDCONNECTEDCONNECTEDCONNECTEDCONNECTEDCONNECTEDCONNECTEDCONNECTEDCONNECTEDCONNECTEDCONNECTEDCONNECTED0
11CUTTEDCUTTEDCUTTEDCUTTEDCUTTEDCUTTEDCUTTEDCUTTEDCUTTEDCUTTEDCUTTEDCUTTED0
12SUPPRESSEDSUPPRESSEDSUPPRESSEDFEASIBLEFEASIBLE1
13SUPPRESSEDCUTTEDCUTTED1
14FEASIBLEFEASIBLEFEASIBLEFEASIBLEFEASIBLEFEASIBLEFEASIBLE0
15FEASIBLEFEASIBLEFEASIBLEFEASIBLEFEASIBLEFEASIBLEFEASIBLEFEASIBLEFEASIBLECONNECTEDCONNECTEDCONNECTED0
16FEASIBLEFEASIBLECONNECTEDFEASIBLEFEASIBLEFEASIBLEFEASIBLEFEASIBLEFEASIBLEFEASIBLEFEASIBLECONNECTED1
17FEASIBLEFEASIBLEFEASIBLECONNECTEDCONNECTEDCONNECTED0
18SUPPRESSEDFEASIBLEFEASIBLEFEASIBLEFEASIBLEFEASIBLEFEASIBLEFEASIBLEFEASIBLEFEASIBLEFEASIBLEFEASIBLE1
Sheet23
Cell Formulas
RangeFormula
N2:N18N2=IF(OR(ISNUMBER(SEARCH({"SUPPRESSED|CUTTED","SUPPRESSED|FEASIBLE","FEASIBLE|SUPPRESSED","FEASIBLE|CUTTED"},TEXTJOIN("|",1,B2:M2))),IFERROR(AGGREGATE(14,6,COLUMN(B2:M2)/(B2:M2="FEASIBLE"),1)-AGGREGATE(15,6,COLUMN(B2:M2)/((B2:M2="CONNECTED")+(B2:M2="SUPPRESSED")),1)>0,0)),1,0)
Click to expand...
Thank you very much, it worked perfectly
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question
Conditional Formatting - How to Properly Copy + Paste without Duplicating Rules
Replies
1
Views
1K
6StringJazzer
6StringJazzer
S
  • Question
VBA Code - Create Log sheet (Errorlog) in case VBA Macro condition is not met
Replies
1
Views
111
Tupe77
T
L
  • Solved
Conditional Formatting in Excel Table and Ignore Blanks Not Working
Replies
3
Views
215
Joe4
Joe4
I
  • Question
Identify Rows Based on 3 Columns
Replies
6
Views
80
StephenCrump
S
M
  • Solved
VBA macro: How to identify column by name (instead of number) before filling
Replies
3
Views
98
rpaulson
rpaulson

Forum statistics

Threads
1,214,381
Messages
6,119,192
Members
448,874
Latest member
Lancelots

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