how to make nested if statement for multiple criteria and ignore false's

[kevinu]

I'm trying to pull some data from cells in another sheet but ignore some of it. I'm using this but its not working.

Please Login or Register  to view this content.

If i just have one IF it works fine but nested is not working.

Also this formula simply returns a blank cell. Is there a way to have it skip the cell and not return anything?

[rcm]

Could you send a nonsensitive sample of the source of data and the desired results?

[FlameRetired]

I'm trying to pull some data from cells in another sheet but ignore some of it. I'm using this but its not working.

Please Login or Register  to view this content.

Yes just as rcm says. We'll need context. Please no pics or screenshots. It saves retyping data.

If you do not know how to do this:


To attach a file to your post,

• be sure to desensitize the data
• click “Go Advanced” (next to Post Quick Reply – bottom right),
• scroll down until you see “Manage Attachments”, click that,
• click “Browse”.
• select your file(s)
• click “Upload”
• click “Close window”
• click “Submit Reply”

The file name will appear at the bottom of your reply.

[Skiptomylou]

As the others already said, it's tough without context. I am assuming though you want a blank cell for the values you have listed such as "location", "average", and "combination, and if it is something else to return that cell value.

If that is the case then use this:

Please Login or Register  to view this content.

On the other hand, if its the opposite as your title suggests then just replace each "" with the cell reference and the final cell reference with ""

I'm trying to pull some data from cells in another sheet but ignore some of it. I'm using this but its not working.

Please Login or Register  to view this content.

If i just have one IF it works fine but nested is not working.

Also this formula simply returns a blank cell. Is there a way to have it skip the cell and not return anything?

[kevinu]

Could you send a nonsensitive sample of the source of data and the desired results?

Im out of town but will reply witht that asap. Rhanks for looking at this.

[kevinu]

Here is an example. I'm pulling data from a source (in this case its columns a,b and c. or f, g, and h, I need to get just the numbers and have the results look like columns n, o and p. I dont care about where its placed I just need the numbers only with no spaces above or below or in between.

The problem is that the data i'm pulling from has 2 different sources and it imports it differently so sometimes there are no words like "location" or "average" and sometimes there are. My current work-round is to have 2 separate versions. 1 for the version with words and one version for without. I'd like to figure out how to pull the data for both on the same version.

[JeteMc]

If I understand correctly then the following array entered formula* will do what you want (yields the same values that were manually placed in columns N:P)

Please Login or Register  to view this content.

Instructions:
1) Select cell N3,
2) Paste the formula into the formula bar,
3) Simultaneously press the Ctrl, Shift and Enter keys,
4) Drag the fill handle down to row 26 (or further),
5) While the range N3:N25 is still selected drag the fill handle over to P26.
Let us know if you have any questions.

[kevinu]

Jet, I put the formula in the example and it only gave me blank cells. Can you put it in the example and load it up? I followed your instructions but somehow it didnt work correctly.

[Sam Capricci]

well going off your first post and not checking your attachment (can't open it from work) it seems you could take this...
=IF('Import data'!BQ2="location",if('Import data'!BQ2="average",if('import data"!BQ2="combination")),"",'Import data'!BQ2)
and change it to this and it should function at least based on what you wrote.
=IF(OR('Import data'!BQ2="location",'Import data'!BQ2="average",'import data"!BQ2="combination"),"",'Import data'!BQ2)
and if they are all in the same sheet then this...
=IF(OR(BQ2="location",BQ2="average",BQ2="combination"),"",BQ2)

[JeteMc]

Here is a copy of the file with the array entered formula applied.
Let us know if you have any questions.

[kevinu]

Jet that works great with thesecond set of data but not the first set (a,b,c). There are 2 exceptions to the no letters rule. The example list A,B,C you'll see there are the words AmyL, AmyR, HipL and HipR that neet to be copied over as well. there are no other letters or words that needs to be seen. My example didnt have that one in there sorry for that.

[FlameRetired]

If I now understand correctly try array entering (Ctrl + Shift + Enter) this in N2, fill down and across until you get blanks.

Please Login or Register  to view this content.

[JeteMc]

Try modifying the array entered formula* so that it reads:

Please Login or Register  to view this content.

*Remember to simultaneously press the Ctrl, Shift and Enter keys.
In the attached file the modified formula is applied to both ranges.
Let us know if you have any questions.

[kevinu]

perfect. Thanks

[JeteMc]

You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.

[kevinu]

Jete, Can you look at this example. I see that I'm using a two step method to get what I want and want to see if I can make it one step. In the example I have 2 sets of data im pulling from. I have 2 because the data i get comes in 2 different formats. The main difference is where the data is placed and some extra words inbetween. I've labeled it data set 1 and data set 2. I want the formula to work with either sets. On the example sheet data set 1 is column A, data set 2 is column F. I import that by copying rows starting at "loreta absolute power" on down to the end to my workbook - in this case to column M. From M there is a row of helper cells in N that number them from 1 to 5. 1 is those cells after the words "loreta absolute power", 2 is after "loreta coherence", 3 is after "loreta phase", 4 is after "loreta shift" and 5 is after "loreta lock". I want to separate these 5 sections to use in other parts of the workbook. Once they are numbered a formula in O separates the one row in to 5 rows.

This used to work fine but thats when the program that gave me the data changed is output format. Now the new format has more words in it and is further down the rows. To fix this I used your formula in column W. I'd like to see if I can find a formula that does it in one step. I need the formula to work with both sets of data and give me results like I see in w-y which is just the locations or numbers words AmyL, HipL, AmyR, AmyL in it.

Again what I have works great and maybe I should let it be, but being an organizer freak I'd like to see if it can be done in one step

[JeteMc]

I don't see that going from a two step (actually seems like three steps) to one would be a benefit.
However, I believe that the first step of getting the data into column H could be automated.
I am guessing that the data is either sent in the form of 'Data set 1' or 'Data set 2' and not both and to that end I put them on separate sheets.
The formula that populates H2 and down is:

Please Login or Register  to view this content.

Let us know if you have any questions.

[kevinu]

Sorry my explanation was bad. The data as an excel sheet that looks like data set's 1 and 2. I import them and they look like column H. I get either one or the other not both at the same time. So If I get something like set 2 then what I have works fine. But if i get something like set 1 then I have extra stuff in there like "average", "combination", and "location" that are not filtered out. I need the code to work with both in case i get one or the other. So I'm looking for a formula to look at H (which changes depending on the data set) and give me R-V skipping the step of J-O

[FlameRetired]

If I now understand correctly the working data feed will be in column M. Whether it comes from Data set 1 or Data set 2 it will always be the section of either Data set that follows "Collection hardware:"

With those understandings find these two helper formulas in K:L.

Please Login or Register  to view this content.

and

Please Login or Register  to view this content.

Then this array formula in O1 filled down and across.

Please Login or Register  to view this content.

To demonstrate that the helper columns work on either Data set see helpers B:C and G:H as well as the array outputs in V:X and AA:AC.
BTW: the helpers in B:C and G:H factor out the line "LORETA Neurofeedback ... " but not necessary in K:L.

(I had to color code them to keep from going dizzy ... )