• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

IF and condition not met then go to next row ?

K

kerstin boelsen

New Member
Hi,

I'm quite an Excel novice.

I try to find a good way to tell Excel
"if A2 = apple,B2= banana, show me the content of F", but if A2 is not apple and/ or B2 is not banana, go to the next row and try again.
I can get Excel to show me "-" if the condition is not met, but I can't make it "jump"; I need to display the content of a mixed large data file on tab 2 in defined cells of the "display" on the main tab
=IF(AND('2nd tab'!A2="apple", '2nd tab'!B2="Banana"),'2nd tab'!F2,"-") was as far as I got :(

And what would ctrl-shift-enter do ?
 
Hi ,

I am not clear on what some of your explanations mean ; if you can upload your workbook , and then explain with reference to the data in the workbook , it might make it easier to solve your problem.

Narayan
 
NARAYANK991 said:
Hi ,

I am not clear on what some of your explanations mean ; if you can upload your workbook , and then explain with reference to the data in the workbook , it might make it easier to solve your problem.

Narayan
Click to expand...


I have 2 tabs (tab 1 which is to display content of certain cells in tab 2)
in tab 1 i need to display in cell A1 (tab 1) the content of cell F2 (on tab2), but only if cell A2 is a certain value (apple) AND cell B2 is also a certain value (banana).
If not both conditions are met, the formula should continue with the next row and check if both conditions in A3 and B3 are met in the next row and then display the content of that F3. Tab 2 contains 105k lines and the content is to be linked further with other workbooks; just filtering and displaying would not work as it needs to be repeatable with only redefinition of A2 & B2.
with the formula above, I get the data I need but have a lot of "-" when the conditions are not met. instead of a large number of rows "-", I want Excel to jump to the next row and check the conditions there until a suitable row for cell display has been found.
 
Hi ,

Sorry , but things are still unclear.

An Excel formula is entered in a cell , so that it displays the result of the formula in that cell.

For example , A1 can have a formula , which displays one thing if the conditions are satisfied , and another thing if the conditions are not satisfied.

I am not able to understand what you mean by Excel should jump to the next row.

If you could upload your workbook , everything would be clear.

Narayan
 
I am aware that formula display content / value of a cell, however, if I want Excel to check certain conditions are met before displaying the content of a defined cell, it should also be possible to tell excel that " if these 2 cells do not meet the condition, jump to the next row and check if the defined 2 cells there met the conditions" . example file attached. SO Iam not talking about A1 meeting a condition, but A1 and B1 need to meet a certain condition to display F1. if A1 or B1 do not display the condition, then F1 is not to be displayed and the next row shall be checked if A2 and B2 meet the condition so F2 can be displayed. The "display tab" is only to show cells of rows with A& B cells which meet the conditions.
 

Attachments

  • 2016-09-27_Master List example.xlsx
    14.1 KB · Views: 42
This looks like it :) I never thought of index :( however, when I try to repeat the formula, it will continue to only display the result from the first matched row.
 
Last edited:
Hi ,

If you want a proper solution , there are two ways to go about it :

1. Give a clear , complete description of the requirement , including details about all the variations in input data , and how they need to be handled.

2. Upload a workbook , which has enough data , say 50 or even 100 rows , so that all possible variations are available.

Narayan
 
Sorry Narayan, I am not sure how I can explain more detailed what it is I'm looking for: source tab cell condition: if B= EMEA CENTRAL, and Cell F= Digital marketing, then display content of cell O [in that row) ; if B and F do not match condition, check the following row if conditions are met.
If cell B1 = EMEA central, Cell F1= digital media (=condition not met), go to row 2 to check if B2 = EMEA central and F2= digital marketing; if F2= EMEA central and F2 = digital marketing then display content of source tab O2 in cell C2 of Display tab (if not then go to row 3 on source tab [...]), if if F3= EMEA central and F3 = digital marketing then display content of source tab O3 in cell C3 of Display tab (if not then go to row 4 on source tab [...]).
The goal is to have a search displaying the content of Parent Account name from cell O on source tab in cell C on display tab and excluding all Parent account names in rows which do not meet the criteria.
 

Attachments

  • 2016-09-28_Master List example.xlsx
    19 KB · Views: 33
pirya93 said:
Can you please explain the role of Row(A1) in the formula?
Click to expand...
Hi ,

The SMALL function or the LARGE function has the following syntax :

=SMALL(range , k)

where range is a single or multi-row , single or multi-column reference , and k is an integer taking the values 1 , 2 , 3 ,...

Depending on the value of k , the SMALL function will return the smallest , the second smallest , the third smallest value in a range.

However , when you use the SMALL function in a formula , where the formula is entered in one cell and then copied either across columns or down rows , then if you have the value 1 in the formula , that value will be copied to all other cells. Thus , if you want the first cell to contain the smallest value , the second cell to contain the second smallest value , the third cell to contain the third smallest value and so on , you will not get what you want. Every cell will contain the same value.

Thus , if you want k to change from 1 to 2 to 3 and beyond , then there to be a variable in k , not a fixed value such as 1 or 2 or 3.

This is the reason we use the ROW function , with a parameter such as A1.

=ROW(A1) returns 1

=ROW(A2) returns 2

=ROW(A3) returns 3

and so on.

When we have a formula such as :

=SMALL($J$7:$J$19 , ROW(A1))

entered in a cell say D3 , and copy this formula down to cells D4 , D5 , D6 and beyond , then the formulas in those cells will be :

D3 : =SMALL($J$7:$J$19 , ROW(A1))

D4 : =SMALL($J$7:$J$19 , ROW(A2))

D5 : =SMALL($J$7:$J$19 , ROW(A3))

D6 : =SMALL($J$7:$J$19 , ROW(A4))

So because the k value changes , the result of the formula will also change , thus giving us what we want.

Another way of changing the k value is to use the ROWS function , as follows :

D3 : =SMALL($J$7:$J$19 , ROWS(A$1:A1))

D4 : =SMALL($J$7:$J$19 , ROWS(A$1:A2))

D5 : =SMALL($J$7:$J$19 , ROWS(A$1:A3))

D6 : =SMALL($J$7:$J$19 , ROWS(A$1:A4))

=ROWS(A1:A1) returns the number of rows within the range A1:A1 ; the result is 1.

=ROWS(A2:A2) returns the number of rows within the range A2:A2 ; the result is still 1.

=ROWS(A99:A99) returns the number of rows within the range A99:A99 ; the result is still 1.

If we prefix the first row reference with the $ symbol to make it absolute , and leave the second row reference relative (without the $ symbol) , then when we copy the formula downwards , the first row reference will remain unchanged , while the second will change from one cell to the next.

=ROWS(A$1:A2) returns the number of rows within the range A1:A2 ; the result is 2.

=ROWS(A$1:A3) returns the number of rows within the range A1:A3 ; the result is 3.

=ROWS(A$1:A4) returns the number of rows within the range A1:A4 ; the result is 4.

If you have any doubts , feel free to ask.

Narayan
 
Thank you so much for your reply.

I downloaded your file where you have showed this formula, and I am trying to mimic it exactly except the cell references. It is only giving the FALSE value. Could you please help me with that?

Regards.
Pirya
 
NARAYANK991 said:
Hi ,

The SMALL function or the LARGE function has the following syntax :

=SMALL(range , k)

where range is a single or multi-row , single or multi-column reference , and k is an integer taking the values 1 , 2 , 3 ,...

Depending on the value of k , the SMALL function will return the smallest , the second smallest , the third smallest value in a range.

However , when you use the SMALL function in a formula , where the formula is entered in one cell and then copied either across columns or down rows , then if you have the value 1 in the formula , that value will be copied to all other cells. Thus , if you want the first cell to contain the smallest value , the second cell to contain the second smallest value , the third cell to contain the third smallest value and so on , you will not get what you want. Every cell will contain the same value.

Thus , if you want k to change from 1 to 2 to 3 and beyond , then there to be a variable in k , not a fixed value such as 1 or 2 or 3.

This is the reason we use the ROW function , with a parameter such as A1.

=ROW(A1) returns 1

=ROW(A2) returns 2

=ROW(A3) returns 3

and so on.

When we have a formula such as :

=SMALL($J$7:$J$19 , ROW(A1))

entered in a cell say D3 , and copy this formula down to cells D4 , D5 , D6 and beyond , then the formulas in those cells will be :

D3 : =SMALL($J$7:$J$19 , ROW(A1))

D4 : =SMALL($J$7:$J$19 , ROW(A2))

D5 : =SMALL($J$7:$J$19 , ROW(A3))

D6 : =SMALL($J$7:$J$19 , ROW(A4))

So because the k value changes , the result of the formula will also change , thus giving us what we want.

Another way of changing the k value is to use the ROWS function , as follows :

D3 : =SMALL($J$7:$J$19 , ROWS(A$1:A1))

D4 : =SMALL($J$7:$J$19 , ROWS(A$1:A2))

D5 : =SMALL($J$7:$J$19 , ROWS(A$1:A3))

D6 : =SMALL($J$7:$J$19 , ROWS(A$1:A4))

=ROWS(A1:A1) returns the number of rows within the range A1:A1 ; the result is 1.

=ROWS(A2:A2) returns the number of rows within the range A2:A2 ; the result is still 1.

=ROWS(A99:A99) returns the number of rows within the range A99:A99 ; the result is still 1.

If we prefix the first row reference with the $ symbol to make it absolute , and leave the second row reference relative (without the $ symbol) , then when we copy the formula downwards , the first row reference will remain unchanged , while the second will change from one cell to the next.

=ROWS(A$1:A2) returns the number of rows within the range A1:A2 ; the result is 2.

=ROWS(A$1:A3) returns the number of rows within the range A1:A3 ; the result is 3.

=ROWS(A$1:A4) returns the number of rows within the range A1:A4 ; the result is 4.

If you have any doubts , feel free to ask.

Narayan
Click to expand...


I wish to add formula in B4. Condition that if H4 and I4 both are zero, skip the row and move to next result. It is important to have 0 in both cells. If one cell has zero and other hasn't, row should not move.

please help.

Regards.
 

Attachments

  • ACL Practice.xlsx
    39.6 KB · Views: 16
Hi ,

I am not able to understand how a formula can be added in cell B4 , which already has data in it.

Can you please mention which worksheet is involved ?

Narayan
 
NARAYANK991 said:
Hi ,

I am not able to understand how a formula can be added in cell B4 , which already has data in it.

Can you please mention which worksheet is involved ?

Narayan
Click to expand...

Sorry, I guess I attached the wrong file

Sheet named Bendigo is involved.

I wish to add formula in B4. Condition that if H4 and I4 both are zero, skip the row and move to next result. It is important to have 0 in both cells. If one cell has zero and other hasn't, row should not move.

For example: For Alanine, if H4 = 0 and I4 = 0, Alanine should not be in the table

Regards.
pirya
 

Attachments

  • ACL Practice.xlsx
    11.9 KB · Views: 12
Hi ,

See the attached file.

Note that the formula using the SMALL function is an array formula , to be entered using CTRL SHIFT ENTER.

Narayan
 

Attachments

  • Chandoo 3.xlsx
    12.4 KB · Views: 53
NARAYANK991 said:
Hi ,

See the attached file.

Note that the formula using the SMALL function is an array formula , to be entered using CTRL SHIFT ENTER.

Narayan
Click to expand...




Thank you so much :)
That worked. :)
I have rather some questions:

If the curly bracket gets removed anyhow, the formula wouldn't work?
How to go about it?
Can I drag it down?
We should press CTRL SHIFT ENTER. at the last of formula (When we are done writing it) or anywhere in middle?

Please guide.

Thanks a lot again.
 
Hi ,

For a normal non-array formula , you type in the formula and press the ENTER key.

For an array formula , after typing in , you don't press only the ENTER key ; instead you press the 3 key combination of CTRL SHIFT ENTER ; when you do this , Excel automatically inserts the curly brackets { and } around the formula. These cannot be entered by the user.

Any formula can be dragged down.

Yes , if a cell contains an array formula and you press the F2 key to edit it and then press only the ENTER key , it is now no longer going to function as an array formula ; its results will be wrong.

Narayan
 
NARAYANK991 said:
Hi ,

For a normal non-array formula , you type in the formula and press the ENTER key.

For an array formula , after typing in , you don't press only the ENTER key ; instead you press the 3 key combination of CTRL SHIFT ENTER ; when you do this , Excel automatically inserts the curly brackets { and } around the formula. These cannot be entered by the user.

Any formula can be dragged down.

Yes , if a cell contains an array formula and you press the F2 key to edit it and then press only the ENTER key , it is now no longer going to function as an array formula ; its results will be wrong.

Narayan
Click to expand...



That makes sense :)
Thanks a lot

Pirya.
 
Hey all,

I've read through and looked at the files in here and still can't get the formula to work in my excel sheet. Basically, I'm looking to automatically build two sheets from data input into one tab.

1. I would like the rows on the "CONSOLIDATE From CO's" tab that have a "complete" status and all information from that row copied automatically to the "COMPLETE | GETTING PAID" tab.

2. All other rows with other statuses with all information copied to the "BN CAIP-SDAP TRACKER" tab.

3. It would be awesome if I could have it sort the rows by "JQR Level" then "Team" automatically as well.

I think the formula discussed in this thread is what I'm looking for, but I have no real concept of what is actually happening and am quite confused. Would the same general formula work for what I'm looking to do? I'd appreciate any assistance anyone can offer. I've attached a copy of the spreadsheet in question.
 

Attachments

  • BN CAIP-SDAP Tracker v1 (no cond format).xlsx
    50.9 KB · Views: 2
Perhaps a alternative approach.
In the attached is your file with a button at cell AE1 of the CONSOLIDATE From CO's sheet.
This button calls a short macro:
Code: 
Sub blah()
Set SceData = Sheets("CONSOLIDATE From CO's").Range("A1").CurrentRegion.Resize(, 29)
Set CritRng = Sheets("Control").Range("A1:A2")
CritRng.Cells(2).Value = "Complete"
SceData.AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=CritRng, CopyToRange:=Sheets("COMPLETE | GETTING PAID").Range("A1:AG1"), Unique:=False
CritRng.Cells(2).Value = "<>Complete"
SceData.AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=CritRng, CopyToRange:=Sheets("BN CAIP-SDAP TRACKER").Range("A1:AC1"), Unique:=False
End Sub
It uses Advanced Filter (Data|Sort & Filter|Advanced on the ribbon) to copy data to your two sheets.
It uses a criteria range of 2 cells on a hidden sheet called Control.
For this to work smoothly I've moved the column NOTES in the sheet BN CAIP-SDAP TRACKER to be next to the other columns you're copying over.
 

Attachments

  • Chandoo31468BN CAIP-SDAP Tracker v1 (no cond format).xlsm
    62.1 KB · Views: 6
I'm so sorry, I think I may have broken the code when I moved some stuff around and added a column for tier. I keep getting a VBA runtime error 1004 "the exact range has a missing or invalid field name". I tried to look up some solutions for the error and everything I tried didn't fix it. I hate to bother you again for assistance, but I do greatly appreciate your help.
 

Attachments

  • Chandoo31468BN CAIP-SDAP Tracker v2.xlsm
    548 KB · Views: 4
You must log in or register to reply here.
Back
Top