If Statement false check next row for true condition

[Fishstats]

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

 

[bstory84]

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.

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

 

[Fishstats]

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

 

[circledchicken]

Hi,

I don't think it's clear to me what you want but perhaps try:

Excel Workbook
	A	B
1	location	Location is in
2	out	in
3	out	in
4	out	in
5	in	in
6	in
7	out
8	in
9	out
10	out
11	in
Sheet1

Excel 2010

Cell Formulas
Range	Formula
B2	=IF(ROW(B1)-1A:A,"in"),"in","")

 

[Fishstats]

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​

 

[circledchicken]

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
	A	B	C	D	E	F	G
1	Site	Location	Date		site	location	date
2	a	in	12/01/2011		a	in	12/01/2011
3	b	in	12/02/2011		b	in	12/03/2011
4	c	out	12/03/2011		f	in	12/08/2011
5	d	out	12/04/2011		h	in	00/01/1900
6	e	out	12/05/2011
7	f	in	12/06/2011
8	g	out	12/07/2011
9	h	in	12/08/2011
10	i	out	12/09/2011
Sheet1

Excel 2010

Cell Formulas
Range	Formula
F2	=IF(E2<>"","in","")

#VALUE!

Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.

 

[gpaco2]

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!!

	A	B	C	D	E	F
1	All skus <tbody> </tbody>	All skus qty <tbody> </tbody>	Supplier <tbody> </tbody>		Matching Supplier skus <tbody> </tbody>	Supplier qty <tbody> </tbody>
2	AD-1007-05 <tbody> </tbody>	1	AD-1007-05		AD-1007-05 <tbody> </tbody>	1
3	AD-1006-03 <tbody> </tbody>	25	AD-1006-03
4	AD-1008-05 <tbody> </tbody>	3	AD-1023-05
5	AD-1009-01 <tbody> </tbody>	66	AD-1089-01
6	AD-1009-09 <tbody> </tbody>	55	AD-1009-09

<tbody>
</tbody>

 

[circledchicken]

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?

 

[gpaco2]

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!!

	A	B	C	D	E	F
1	All skus <tbody> </tbody>	All skus qty <tbody> </tbody>	Supplier <tbody> </tbody>		Matching Supplier skus <tbody> </tbody>	Supplier qty <tbody> </tbody>
2	AD-1007-05 <tbody> </tbody>	1	AD-1007-05		AD-1007-05 <tbody> </tbody>	1
3	AD-1006-03 <tbody> </tbody>	25	AD-1006-03		AD-1006-03	25
4	AD-1008-05 <tbody> </tbody>	3	AD-1023-05		AD-1009-09	55
5	AD-1009-01 <tbody> </tbody>	66	AD-1089-01
6	AD-1009-09 <tbody> </tbody>	55	AD-1009-09

<tbody>
</tbody>

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.

 

[circledchicken]

You were close ... perhaps try the following and drag down (using CTRL-SHIFT-ENTER rather than just ENTER as they are array formulae):

E2:

=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:

=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.