Nesting IF statements

[figgylynn1023]

I'm trying to nest IF statements so I don't have to program a macro to do a million sorts for value lookups and code another column accordingly.

There are four formulas I'd like to combine into one in the cells in column A, but everytime I try (I'm kind of new to Excel), it pops up with an error.
I have tested these all individually, but sometimes they seem to work and other times they don't.

1st formula:
If the value in C2 is "PDD", mark "PDD" in column A.
=IF(C2="PDD","PDD","")

2nd formula:
If the value in E2 is "BF", "RI", or "IA" AND the value in C2 is NOT "PDD", mark A2 the same as E2.
=IF(AND(OR(E2="BF")(E2="RI")(E2="IA"))*(C2<>"PDD"),E2,"")

3rd formula:
If the value in C2 is nothing, mark A2 same as E2.
=IF(C2="",E2,"")

4th formula:
If the value of C2 is "SDD" AND the value of B2 is NOT "Y"), mark A2 the same as E2.
=IF(AND(C2="SDD")*(B2<>"Y"),E2,"")

Essentially I want the single formula to be able to say IF the first formula is true do that, IF the second formula is true do that, IF the third formula is true do that, IF the fourth formula is true do that, and if none are true mark nothing.

Any help with the formulas...and particularly nesting them together would be much appreciated!! Thank you in advance!

 

[FKoenig]

Nevermind.... your 2nd IF statement is erroneous. I'll fix it and get back to you.

 

[Scott Huish]

Try this:

=IF(C2="PDD","PDD",IF(OR(E2={"bf","ri","ia"},C2="",AND(C2="sdd",B2<>"y")),E2,""))

 

[FKoenig]

Hot has beaten me to it... and has written a more economical formula compared to my =IF(C2="PDD","PDD",IF(OR(E2="BF",E2="RI",E2="IA"),E2,IF(C2="",E2,IF(AND(C2="SDD",B2<>"Y"),E2,""))))

I am bested. Ok, Ok, that's not ahrd to do.

 

[figgylynn1023]

Thank you thank you! That statement worked brilliantly, leaving open only the cells that refer to same day duplicates that occur on different reports.

But now I'm wondering if it would be possible to bring that into this statement as well...not that I've found anything that has given me what I'm really looking for.

Basically, for whatever cells have been left blank by the previous statement- they should all reference ColumnC="SDD", ColumnB="Y"
But what I would like for those empty cells is something like this:

If the values of column F are equal (above and/or below) that row (and ONLY if they are equal), then look at the values (of those matching rows) in column E and reference back to the Hierachy list found on Sheet 2 (range A2:A9), and label the empty cells of column A (that correspond to matching ColumnF cells) the SAME code- the one that is the highest on the hierarchy list.

The formulas I came up with to find the Y in column B (that yes, they are on different reports) was
=IF(OR(AND((E2<>E3),(F2=F3)),AND((E2<>E1),(F2=F1))),"Y","")

A coworker recommended replacing the value if true "Y" with a MATCH function to lookup the hierarchy, but it still did not yield the results I wanted.

Is there a way to do this that actually works...and perhaps link it into the first formula so the whole column A can get labeled at the same time?

Thank you again for all your help so far! It is extremely appreciated!

 

[figgylynn1023]

Update:
Trying to go through the blank cells and figure out a formula that might work, I discovered that there could still be a few blank cells after figuring out the formula of:
IF (Column F being equal and Column E being different) look at hierarchy for column E and label in column A.

The blank cells usually refer to cells that get labeled in column D as Same Day Duplicates "SDD" and labeled in column B as "Y"- yes the duplicates are on different reports. But their duplicate is a BF, RI, or IA that get labeled in a previous step of the formula.
Granted, there should never be too many of these, but if it's possible to build that in a process as well...perhaps at the end of the MATCH/hierarchy statement of- if it wasn't a match/hierarchy is true statment that it should then = the value that is in E2?

 

[figgylynn1023]

Sorry for the multitude of updates, but I wanted to clarify.

This is the ENTIRE list of what I'd like the formula to do (with the addition of all lately realized problem areas):

1. If Column C="PDD", column A should = "PDD"
2. If Column E= "BF", "IA" or "RI", column A should = column E
3. If Column C= "", column A should = column E
4. If Column C= "SDD" AND Column B<>"Y", column A should = Column E

These have already been addressed, much thanks again, but here's what I hope is a slightly clearer statement of what I'd like the new steps to do, and what I'm having trouble working out.

New:
5. If (Column C="SDD" and Column B="Y") AND (Column F- current cell= cell above and/or below in column F), then MATCH Column E in those rows to the hierarchy list (Sheet 2, Range A2:A9), and all those rows in Column A should = the highest value from those rows in column E

6. If Column A is still blank, column should = Column E

Everything I've tried for step 5 even standing on its own has not worked for me, so I'm really getting to the end of my rope on it. Is it even possible to put this in with the other statement?

 

[Scott Huish]

Excel Workbook
	A
1	Can you post a sample and expected results?
Sheet1

 

[figgylynn1023]

I unfortunately cannot download Excel Jeanie to my work computer, but I can try to post a sample version of what I want the formula to do from my home computer later.

In the meantime...here's the best I can do- I want the column A data to fill in like below based on the other columns:
A B C D E F
Report Assign to Diff Rep. PDD/SDD Hier. Code Report ID #
PDD "" PDD A1 BF ###0339
DF "" SDD C3 DF ###9743
DF "" SDD C3 DF ###9743
DA Y SDD C2 DA ###7953
DA Y SDD C3 DF ###7953
DL "" "" B1 DL ###8470
PDD Y PDD C3 DF ###1377
PDD Y PDD C4 DI ###1377
BF Y SDD A1 BF ###6863
DI Y SDD C4 DI ###6863
RI Y SDD A1 RI ###2841
DA Y SDD C2 DA ###2841
DA Y SDD C3 DF ###2841

The Hierarchy Code on Sheet 2 looks like this, starting in Cell A2 and going to A9

DL
DQ
DP
DA
DF
DI
MB
QM

Hope this helps! Otherwise I'll make a sample version and upload it from home this weekend! Thank you!

 

[figgylynn1023]

Report Assign to: Diff Rep.: PDD/SDD: Hier. Code: Report: ID #:
PDD: : PDD: A1: BF: ###0339:
DF: : SDD: C3: DF: ###9743:
DF: : SDD: C3: DF: ###9743:
DA: Y: SDD: C2: DA: ###7953:
DA: Y: SDD: C3: DF: ###7953:
DL: : : B1: DL: ###8470:
PDD: Y: PDD: C3: DF: ###1377:
PDD: Y: PDD: C4: DI: ###1377:
BF: Y: SDD: A1: BF: ###6863:
DI: Y: SDD: C4: DI: ###6863:
RI: Y: SDD: A1: RI: ###2841:
DA: Y: SDD: C2: DA: ###2841:
DA: Y: SDD: C3: DF: ###2841:

Sorry- tried to make an "edit" when I realized my plan didn't work, but it had timed out.
This new version you can copy/paste to a new sheet and then use "Text to Columns" and delimit it by using the : symbol (in "other"), then you should be able to see the appropriate columns.
The formula already written out would label almost everything- if the formatting works correctly, I have highlighted in blue the ones that the match formula would identify and highlighted in orange the one that would not get a label because it is a duplicate but it's duplicate got labeled by another part of the formula.