If Statement false check next row for true condition

F

Fishstats

New Member
Joined
Nov 18, 2011
Messages
16
I am trying to use an if statement to enter the value of a cell into another cell if it meets a criteria. If that cell doesn't meet the criteria I want it to check the cell below it and if that cell is true I would like it to be placed in another cell.

For example if

if a1 is false but a2 is true I want it to enter the value of a2 in b1. I would like this to continue down both columns. So that any value that meets the criteria in column a be placed in column b with no blank cells or 0's in column b.

location Location is in
out in
out in
out in
in in
in
out
in
out
out
in
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
Sort by date Sort by votes
Since you do not want a Blank cells or zeros in column B
a macro to loop thru each cell in column "A" would probably work best for you.
Try this.

Code: 
Sub loopthrumatch()
Dim rng As Range
Set rng = Range("Sheet1!A1:A50")    '''designate your range to loop thru here.
 
    For Each C In rng
        If C.Value = "this" Then  '''your text that you want to match
            Range("B99999").End(xlUp).Offset(1, 0).Value = C.Value
        Else
        End If
 
    Next C
 
End Sub
 
Last edited:
Upvote 0
Thanks for the quick response. I was hoping to avoid using a macro, but if there is not alternative I'll just put one in.

Thanks again
 
Upvote 0
Hi,

I don't think it's clear to me what you want but perhaps try:
Excel Workbook
AB
1locationLocation is in
2outin
3outin
4outin
5inin
6in
7out
8in
9out
10out
11in
Sheet1
Excel 2010
Cell Formulas
RangeFormula
B2=IF(ROW(B1)-1A:A,"in"),"in","")
 
Upvote 0
Sorry I wasn't clear in my original post. I was trying to keep it simple but it seems to be more complicated. So I want my table to look like the one below where the equation fills in the site,location and date of the columns starting at E where the criteria for columns e-g is that they must be in location "in".

Hopefully this clears up my miscommunication.

Excel 2007<table rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6" cellpadding="2.5px"><colgroup><col style="background-color: #E0E0F0" width="25px"><col><col><col><col><col><col><col></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th>
</th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th></tr></thead><tbody><tr><td style="color: #161120;text-align: center;">1</td><td style=";">Site</td><td style=";">Location</td><td style=";">Date</td><td style="text-align: right;;">
</td><td style=";">site</td><td style=";">location</td><td style=";">date</td></tr><tr><td style="color: #161120;text-align: center;">2</td><td style=";">a</td><td style=";">in</td><td style="text-align: right;;">12/1/2011</td><td style="text-align: right;;">
</td><td style=";">a</td><td style=";">in</td><td style="text-align: right;;">12/1/2011</td></tr><tr><td style="color: #161120;text-align: center;">3</td><td style=";">b</td><td style=";">in</td><td style="text-align: right;;">12/2/2011</td><td style="text-align: right;;">
</td><td style=";">b</td><td style=";">in</td><td style="text-align: right;;">12/2/2011</td></tr><tr><td style="color: #161120;text-align: center;">4</td><td style=";">c</td><td style=";">out</td><td style="text-align: right;;">12/3/2011</td><td style="text-align: right;;">
</td><td style=";">f</td><td style=";">in</td><td style="text-align: right;;">12/6/2011</td></tr><tr><td style="color: #161120;text-align: center;">5</td><td style=";">d</td><td style=";">out</td><td style="text-align: right;;">12/4/2011</td><td style="text-align: right;;">
</td><td style=";">h</td><td style=";">in</td><td style="text-align: right;;">12/8/2011</td></tr><tr><td style="color: #161120;text-align: center;">6</td><td style=";">e</td><td style=";">out</td><td style="text-align: right;;">12/5/2011</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td></tr><tr><td style="color: #161120;text-align: center;">7</td><td style=";">f</td><td style=";">in</td><td style="text-align: right;;">12/6/2011</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td></tr><tr><td style="color: #161120;text-align: center;">8</td><td style=";">g</td><td style=";">out</td><td style="text-align: right;;">12/7/2011</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td></tr><tr><td style="color: #161120;text-align: center;">9</td><td style=";">h</td><td style=";">in</td><td style="text-align: right;;">12/8/2011</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td></tr><tr><td style="color: #161120;text-align: center;">10</td><td style=";">i</td><td style=";">out</td><td style="text-align: right;;">12/9/2011</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td></tr><tr><td style="color: #161120;text-align: center;">11</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td></tr></tbody></table>
Sheet1​
 
Upvote 0
Hi,

Maybe something like this, dragging down the formula in each column. Note that you need to use CTRL-SHIFT-ENTER not just ENTER for columns E and G.
Excel Workbook
ABCDEFG
1SiteLocationDatesitelocationdate
2ain12/01/2011ain12/01/2011
3bin12/02/2011bin12/03/2011
4cout12/03/2011fin12/08/2011
5dout12/04/2011hin00/01/1900
6eout12/05/2011
7fin12/06/2011
8gout12/07/2011
9hin12/08/2011
10iout12/09/2011
Sheet1
Excel 2010
Cell Formulas
RangeFormula
F2=IF(E2<>"","in","")
#VALUE!
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
 
Upvote 0
Hello circledchicken. I saw your answer to this problem. I'm trying to apply your code to a project, but I'm running into troubles. Hope you can give me a hand. Thanks so much in advance.

I have Two columns I have to compare (A, C). Both columns have codes.
I have to compared column C with column A to see if the codes are the same, if they are, I have to add the repeated code into Column E and at the same time, add the qty on column B into column F. If the codes are not the same, skip the cell on column A, and continue comparing Column C with the next column A cell and so on...

My code on cell F2: =IF(E2<>"",B2,"")

My code on cell E2: {=IFERROR(INDEX(A$2:A$12,SMALL(IF(C$2:C$12=A2:A$12,ROW(A$2:A$12)-ROW(A$2)+1),ROW(A1))),"")}

When I tried to apply the code on E2 to the rest of the cell (on column E), nothing happens The first cell work but from E3 onwards, no luck.

I believe the problem is on the E2 code but can't figure it out how to make in work for the rest of the cells.

Thanks for your help or anybody else who answer to this post. Cheers!!

[TABLE="class: grid, width: 495"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD][TABLE="width: 77"]
<tbody>[TR="class: grid"]
[TD="width: 77, align: center"]All skus [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 98"]
<tbody>[TR="class: grid"]
[TD="width: 98, align: center"]All skus qty[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 77"]
<tbody>[TR="class: grid"]
[TD="width: 77, align: center"]Supplier[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: center"][/TD]
[TD][TABLE="width: 178"]
<tbody>[TR="class: grid"]
[TD="width: 178, align: center"]Matching Supplier skus[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 107"]
<tbody>[TR="class: grid"]
[TD="width: 107, align: center"]Supplier qty[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD][TABLE="width: 77"]
<tbody>[TR="class: grid"]
[TD="width: 77, align: center"]AD-1007-05[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]AD-1007-05[/TD]
[TD="align: center"][/TD]
[TD][TABLE="width: 178"]
<tbody>[TR="class: grid"]
[TD="width: 178, align: center"]AD-1007-05[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD][TABLE="width: 77"]
<tbody>[TR="class: grid"]
[TD="width: 77, align: center"]AD-1006-03[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: center"]25[/TD]
[TD="align: center"]AD-1006-03[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD][TABLE="width: 77"]
<tbody>[TR="class: grid"]
[TD="width: 77, align: center"]AD-1008-05[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]AD-1023-05[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD][TABLE="width: 77"]
<tbody>[TR="class: grid"]
[TD="width: 77, align: center"]AD-1009-01[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: center"]66[/TD]
[TD="align: center"]AD-1089-01[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD][TABLE="width: 77"]
<tbody>[TR="class: grid"]
[TD="width: 77, align: center"]AD-1009-09[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: center"]55[/TD]
[TD="align: center"]AD-1009-09[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
Hi and welcome to the forum,

Can you please show the full set of results you expect in columns E and F based on your sample?
 
Upvote 0
gpaco2 said:
Hello circledchicken. I saw your answer to this problem. I'm trying to apply your code to a project, but I'm running into troubles. Hope you can give me a hand. Thanks so much in advance.

I have Two columns I have to compare (A, C). Both columns have codes.
I have to compared column C with column A to see if the codes are the same, if they are, I have to add the repeated code into Column E and at the same time, add the qty on column B into column F. If the codes are not the same, skip the cell on column A, and continue comparing Column C with the next column A cell and so on...

My code on cell F2: =IF(E2<>"",B2,"")

My code on cell E2: {=IFERROR(INDEX(A$2:A$12,SMALL(IF(C$2:C$12=A2:A$12,ROW(A$2:A$12)-ROW(A$2)+1),ROW(A1))),"")}

When I tried to apply the code on E2 to the rest of the cell (on column E), nothing happens The first cell work but from E3 onwards, no luck.

I believe the problem is on the E2 code but can't figure it out how to make in work for the rest of the cells.

Thanks for your help or anybody else who answer to this post. Cheers!!

[TABLE="class: grid, width: 495"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD][TABLE="width: 77"]
<tbody>[TR="class: grid"]
[TD="width: 77, align: center"]All skus [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 98"]
<tbody>[TR="class: grid"]
[TD="width: 98, align: center"]All skus qty[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 77"]
<tbody>[TR="class: grid"]
[TD="width: 77, align: center"]Supplier[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: center"][/TD]
[TD][TABLE="width: 178"]
<tbody>[TR="class: grid"]
[TD="width: 178, align: center"]Matching Supplier skus[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 107"]
<tbody>[TR="class: grid"]
[TD="width: 107, align: center"]Supplier qty[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD][TABLE="width: 77"]
<tbody>[TR="class: grid"]
[TD="width: 77, align: center"]AD-1007-05[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]AD-1007-05[/TD]
[TD="align: center"][/TD]
[TD][TABLE="width: 178"]
<tbody>[TR="class: grid"]
[TD="width: 178, align: center"]AD-1007-05[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD][TABLE="width: 77"]
<tbody>[TR="class: grid"]
[TD="width: 77, align: center"]AD-1006-03[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: center"]25[/TD]
[TD="align: center"]AD-1006-03[/TD]
[TD="align: center"][/TD]
[TD="align: center"]AD-1006-03[/TD]
[TD="align: center"]25[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD][TABLE="width: 77"]
<tbody>[TR="class: grid"]
[TD="width: 77, align: center"]AD-1008-05[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]AD-1023-05[/TD]
[TD="align: center"][/TD]
[TD="align: center"]AD-1009-09[/TD]
[TD="align: center"]55[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD][TABLE="width: 77"]
<tbody>[TR="class: grid"]
[TD="width: 77, align: center"]AD-1009-01[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: center"]66[/TD]
[TD="align: center"]AD-1089-01[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD][TABLE="width: 77"]
<tbody>[TR="class: grid"]
[TD="width: 77, align: center"]AD-1009-09[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: center"]55[/TD]
[TD="align: center"]AD-1009-09[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...

Hello circledchicken!! Thanks for your quick response. I just added the rest of the results. Let me know if you need more information. And thanks for your help.
 
Upvote 0
You were close ... perhaps try the following and drag down (using CTRL-SHIFT-ENTER rather than just ENTER as they are array formulae):

E2:
Code: 
=IFERROR(
    INDEX(A$2:A$10,
      SMALL(
        IF(A$2:A$10 = C$2:C$10,
           ROW(A$2:A$10) - ROW(A$2) + 1),
        ROWS(A$2:A2))),
    "")

F2:
Code: 
=IFERROR(
    INDEX(B$2:B$10,
      SMALL(
        IF(A$2:A$10 = C$2:C$10,
           ROW(A$2:A$10) - ROW(A$2) + 1),
        ROWS(A$2:A2))),
    "")

If all the codes in a given column will be unique then you can reduce the F2 formula down to a SUMIF.
 
Upvote 0
You must log in or register to reply here.

Similar threads

W
  • Question Question
Move Row to Another Sheet Based on Multiple Cell Values
Replies
9
Views
446
jolivanes
jolivanes
M
  • Solved
Using Filter formula to return a data table
Replies
5
Views
155
dreid1011
dreid1011
F
  • Solved
count last n times TRUE in column, skip is not TRUE or FALSE
Replies
2
Views
213
Scott Huish
Scott Huish
E
  • Question Question
Specific VBA Function
Replies
0
Views
235
EAlexWolfe
E
J
  • Solved
IF Statement with multiple conditions
Replies
4
Views
266
Alex Blakenburg
A

Forum statistics

Threads
1,223,227
Messages
6,170,848
Members
452,361
Latest member
d3ad3y3

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