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
 
Sort by date Sort by votes
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.
 
Upvote 0

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
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..


circledchicken said:
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.
Click to expand...
 
Upvote 0
Sorry, your question is still not completely clear to me. Perhaps like this:

E2 and drag down (CTRL-SHIFT-ENTER rather than just ENTER):
Code: 
=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):
Code: 
=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'.
 
Last edited:
Upvote 0
circledchicken said:
Sorry, your question is still not completely clear to me. Perhaps like this:

E2 and drag down (CTRL-SHIFT-ENTER rather than just ENTER):
Code: 
=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):
Code: 
=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'.
Click to expand...

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.
 
Upvote 0
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
 
Upvote 0
circledchicken said:
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
Click to expand...


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. :-)
 
Upvote 0
gpaco2 said:
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. :-)
Click to expand...
You're welcome, see you :).
 
Upvote 0
hello people

i have a question hear,

please refer the below table first


[TABLE="width: 161"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]AMOUNT[/TD]
[TD]PMT[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]300[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]300[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]800[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]800[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]1200[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]1500[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]1700[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]2000[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]2000[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]2000[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]2200[/TD]
[/TR]
</tbody>[/TABLE]

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 ....
 
Upvote 0
circledchicken said:
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[TABLE="class: html-maker-worksheet"]
<thead>[TR]
[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]
[TH]1[/TH]
[TD]Site[/TD]
[TD]Location[/TD]
[TD]Date[/TD]
[TD][/TD]
[TD]site[/TD]
[TD]location[/TD]
[TD]date[/TD]
[/TR]
[TR]
[TH]2[/TH]
[TD]a[/TD]
[TD]in[/TD]
[TD="align: right"]12/01/2011[/TD]
[TD][/TD]
[TD]a[/TD]
[TD]in[/TD]
[TD="align: right"]12/01/2011[/TD]
[/TR]
[TR]
[TH]3[/TH]
[TD]b[/TD]
[TD]in[/TD]
[TD="align: right"]12/02/2011[/TD]
[TD][/TD]
[TD]b[/TD]
[TD]in[/TD]
[TD="align: right"]12/03/2011[/TD]
[/TR]
[TR]
[TH]4[/TH]
[TD]c[/TD]
[TD]out[/TD]
[TD="align: right"]12/03/2011[/TD]
[TD][/TD]
[TD]f[/TD]
[TD]in[/TD]
[TD="align: right"]12/08/2011[/TD]
[/TR]
[TR]
[TH]5[/TH]
[TD]d[/TD]
[TD]out[/TD]
[TD="align: right"]12/04/2011[/TD]
[TD][/TD]
[TD]h[/TD]
[TD]in[/TD]
[TD="align: right"]00/01/1900[/TD]
[/TR]
[TR]
[TH]6[/TH]
[TD]e[/TD]
[TD]out[/TD]
[TD="align: right"]12/05/2011[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TH]7[/TH]
[TD]f[/TD]
[TD]in[/TD]
[TD="align: right"]12/06/2011[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TH]8[/TH]
[TD]g[/TD]
[TD]out[/TD]
[TD="align: right"]12/07/2011[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TH]9[/TH]
[TD]h[/TD]
[TD]in[/TD]
[TD="align: right"]12/08/2011[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TH]10[/TH]
[TD]i[/TD]
[TD]out[/TD]
[TD="align: right"]12/09/2011[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Excel 2010

CellFormula
F2=IF(E2<>"","in","")

<tbody>
[TD="bgcolor: #FFFFFF"] Worksheet Formulas [TABLE="class: html-maker-worksheet"]
<thead>[TR]

</thead><tbody>
</tbody>
[/TD]
[/TR]
</tbody>[/TABLE]

CellFormula
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)),"")

<tbody>
[TD="bgcolor: #FFFFFF"] Array Formulas [TABLE="class: html-maker-worksheet"]
<thead>[TR]

</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[/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...

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...
 
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
636
jolivanes
jolivanes
M
  • Solved
Using Filter formula to return a data table
Replies
5
Views
229
dreid1011
dreid1011
F
  • Solved
count last n times TRUE in column, skip is not TRUE or FALSE
Replies
2
Views
224
Scott Huish
Scott Huish
E
  • Question Question
Specific VBA Function
Replies
0
Views
261
EAlexWolfe
E
J
  • Question Question
IF statement with time and date.
Replies
8
Views
207
Cubist
Cubist

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

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