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

 

[gpaco2]

Hey circledchicken thanks. ALmos there hhaa! Thanks.

Hey but is there a way that lets say:

• C2 compares with A2, finds a match, puts the data on E2 and F2. Then...
• C3 compares with A3, finds a match, puts the data on E3 and F3. Then...
• C4 compares with A4, no match, so, no data on E and F. Then...
• C4 again compares with A5, finds a match, puts the data on E4 and F4.
• Then Column C continues doing the same process until the en of this column..

This is pretty much what I need. Almost there. It's because right now it stops when C column finds a no match on A column.

Thanks for your help.

 

[gpaco2]

Opps I think Iposted the reply somewhere else.

Well

Almos there hhaa! Thanks.

Hey but is there a way that lets say:

• C2 compares with A2, finds a match, puts the data on E2 and F2. Then...
• C3 compares with A3, finds a match, puts the data on E3 and F3. Then...
• C4 compares with A4, no match, so, no data on E and F. Then...
• C4 again compares with A5, finds a match, puts the data on E4 and F4.
• Then Column C continues doing the same process until the en of this column..

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.

 

[circledchicken]

Sorry, your question is still not completely clear to me. Perhaps like this:

E2 and drag down (CTRL-SHIFT-ENTER rather than just ENTER):

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

F2 and drag down (just ENTER):

=IF(E2="",
    "",
    SUMIF(A$2:A$10, E2, B$2:B$10))

This says, list the 'Supplier' codes that have a matching 'All skus' code, and find the associated sum of the 'All skus qty'.

 

[gpaco2]

Sorry, your question is still not completely clear to me. Perhaps like this:

E2 and drag down (CTRL-SHIFT-ENTER rather than just ENTER):

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

F2 and drag down (just ENTER):

=IF(E2="",
    "",
    SUMIF(A$2:A$10, E2, B$2:B$10))

This says, list the 'Supplier' codes that have a matching 'All skus' code, and find the associated sum of the 'All skus qty'.

Hello circledchicken.

That was exactly what I needed, sorry if I was making it too confusing. It worked perfect. I have many lists, some share the same skus, others not. With this formula I can check which list has which skus, and add its corresponding quantity.

One more thing,so I understand better:Would you be able to quickly give me an explanation of how the codes works.

Thank you very much for all your help circledchicken

I bet someone will find this helpful too.

 

[circledchicken]

Hi gpaco2,

You're welcome - thanks for the feedback.

I still think you might be making it too complicated for what you need. For example:

• remove columns E and F
  
• in D1 enter 'Supplier qty'
  
• in D2 and drag down: =SUMIF(A$2:A$10, C2, B$2:B$10)

You can then just use autofilter on column D to select all the non-zero quantities.

For more on autofilter see: Excel Filters: Excel 2007 AutoFilter Basics
For more on the SUMIF function see: SUMIF Formulas | RAD Excel

 

[gpaco2]

Hi gpaco2,

You're welcome - thanks for the feedback.

I still think you might be making it too complicated for what you need. For example:

• remove columns E and F
• in D1 enter 'Supplier qty'
• in D2 and drag down: =SUMIF(A$2:A$10, C2, B$2:B$10)

You can then just use autofilter on column D to select all the non-zero quantities.

For more on autofilter see: Excel Filters: Excel 2007 AutoFilter Basics
For more on the SUMIF function see: SUMIF Formulas | RAD Excel

Hello circledchicken, thanks for all the information.

I'll take a look at the other way you mentioned. I might be doing it to complicated the other way haa!

Well anyway, thanks and see you on another excel adventure. See you buddy.

 

[circledchicken]

Hello circledchicken, thanks for all the information.

I'll take a look at the other way you mentioned. I might be doing it to complicated the other way haa!

Well anyway, thanks and see you on another excel adventure. See you buddy.

You're welcome, see you .

 

[Excelcool]

hello people

i have a question hear,

please refer the below table first

AMOUNT	PMT
	300
	300
	800
	800
	1200
	1500
	1700
	2000
	2000
	2000
	2200

<colgroup><col><col></colgroup><tbody>
</tbody>

in this table i have to fill in a fixed amount i.e 800 Rs constant, where in if the PMT coloum gives negative, it should automatically skip that row and apply 800 Rs in the next row, again if PMT goes negative, check the next row and apply again 800 ... so on and so fourth ..... and if possible please try first without using macro ....

thanks ....

 

[mayank141985]

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.

Sheet1

	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

<thead>
</thead><tbody>
</tbody>

Excel 2010

Worksheet Formulas Cell Formula F2 =IF(E2<>"","in","") <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Array Formulas Cell Formula E2 =IFERROR(INDEX(A$2:A$10,SMALL(IF(B$2:B$10="in",ROW(A$2:A$10)-ROW(A$2)+1),ROW(A1))),"") G2 =IFERROR(INDEX(C2:C10,MATCH(E2&F2,A$2:A$10&B$2:B$10,0)),"") <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter these manually yourself

<tbody>
</tbody>

Hi..
I have tried the same example for Column E with shared formula for cell E2 but i can get only the result for cell E2 and while dragging down all cell E3, E4.... are coming as blank
Pls suggest the formula for cell E3 & E4...

 

[Eyale]

Hey there,

This solution is so close to what I want to do. Are you able to help me out? See my post: https://www.mrexcel.com/forum/excel...-than-insert-my-false-result.html#post5213372

I am only a beginner with vba. Thanks!