Using an IF statement that ignores the false values clause

Question

I have a list of data in excel consisting of "Yes" and "No".

I need an IF statement that will only act on cells with the "Yes" value, and skip any cells with the "No" value.

For context, the 'value if true' clause of my IF statement is an indexmatchmatch which needs to return a value in the sheet based on the "Yes" or "No".

I understand this could have a simple solution using VBA, so if anyone has an excel based solution or VBA based solution they would be equally helpful.

I can add columns and rows to the dataset I am working with if needs be.

I've tried the excel solution myself but my vba skills aren't up to the task, I've tried and failed. — FrasercT
– FrasercT, Commented May 25, 2017 at 11:11
This doesn't work? =IF(A1="Yes",<YourIndexMatchMatchFormulaHere>,"") — tigeravatar
– tigeravatar, Commented May 25, 2017 at 17:50

Plagon · Accepted Answer · 2017-05-25 10:00:25Z

1

Try this:

Sub YesNo()
Dim lRow As Long
Dim sht As Worksheet
Set sht = Worksheets("Tabelle1")

lRow = sht.Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To lRow
    If sht.Cells(i, 1).Value = "True" Then 'Modify Column
        'Your Code
    End If
Next i
End Sub

answered May 25, 2017 at 10:00

Plagon

3,1651 gold badge14 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Rahul Dasgupta · Accepted Answer · 2017-05-25 15:40:10Z

1

Use VBA to achieve it

Assume in sheet , you have

A   Yes
B   Yes
C   No
A   Yes
B   No
C   Yes
A   No
B   No

After running the macro, Sheet 2 will have only those rows which have yes in Sheet1. Please modify the code based on your requirement

Sub g()
Dim lastRow As Long
Dim sheet As Worksheet
Set sht = Worksheets("Sheet1")
Set sht1 = Worksheets("Sheet2")

lRow = sht.Cells(Rows.Count, 1).End(xlUp).Row
Dim j As Long
j = 1
For i = 1 To lRow

    If sht.Cells(i, 2).Value = "Yes" Then
        sht1.Cells(j, 1).Value = sht.Cells(i, 1).Value
        j = j + 1
    End If
Next i
End Sub

edited May 25, 2017 at 15:40

answered May 25, 2017 at 10:06

Rahul Dasgupta

14810 bronze badges

4 Comments

FrasercT Over a year ago

The issue is that where there are false values, i want to ignore them and look for the next "Yes" in the list and apply the indexmatchmatch to that

Rahul Dasgupta Over a year ago

If I understand correctly, you have a list of rows with a column value either "Yes" or "No" and in the next column you want to display "indexmatchmatch" if the value is "Yes" else blank. =IF(<cell_no>="Yes","indexmatchmatch",""). You can drag the formula for all the rows.

FrasercT Over a year ago

They are not adjacent, what I need is a column fully populated (no gaps) with the indexmatchmatch formula I have, but right now because of the IF statement that applies to the "Yes" or "No" field I have gaps where the IF statement is picking up "No". If I applied the IF statement to the "No"'s as well then my data would be inconsistent.

Rahul Dasgupta Over a year ago

I have modified the answer to achieve it by using VBA. Hope it will help

Collectives™ on Stack Overflow

Using an IF statement that ignores the false values clause

2 Answers 2

Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related