if formulas in an excel help

[osamfirescu]

Hi everyone,

i need a formula that does the following:

If column A contains a certain text and coulumn B countain certain text then display custom text in column B,
In column A will be displayed a case number like OR-13-666456-JUD ... the formula should be if column A contains "JUD" and if Column B contains for example "Event" then return a certain Name (Alex for example).

Could you please help me with this?

Thanks in advance,
Ovidiu

[:) Sixthsense :)]

May be this...

=IF(AND(ISNUMBER(SEARCH("JUD",A2)),ISNUMBER(SEARCH("Event",B2))),"Alex","")

[osamfirescu]

Hey,

It's fantastic but i have 1000 rows .... how can i change the formula that it works without entering the formula for each row?
something like this:

=IF(AND(ISNUMBER(SEARCH("JUD",A1...A1000)),ISNUMBER(SEARCH("Event",B1...B1000))),"Alex",""

and thanks for your quick response ... i really appreciate it!

[:) Sixthsense :)]

May be this...

=IF(AND(SUM(COUNTIF(A1:A1000,"*JUD*")),SUM(COUNTIF(B1:B1000,"*event*"))),"Alex","")

[osamfirescu]

And where do i enter this formula so it applies to the entire column where i want to display the results (let's say column C)?

[:) Sixthsense :)]

Please attach a sample workbook with enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

Note: Please don't attach documents containing confidential data like (address, telephone, ID#s, etc.).

[osamfirescu]

that's the thing, i can't attach the excel because it contains confidential information :|

[osamfirescu]

example.xlsx

I've made and attached an example. What i want is:

=IF(AND(SUM(COUNTIF(F2:F5,"*JUD*")),SUM(COUNTIF(G2:G5,"*event1*"))),"Alex","")

AND

=IF(AND(SUM(COUNTIF(F2:F5,"*JUD*")),SUM(COUNTIF(G2:G5,"*event2*"))),"Dan","")

What i want to do when i insert another row of info or 100 rows is to automatically run the above code and display the name in Column I without having to insert the code for every row

Thanks

[osamfirescu]

anyone?
no one can help me?

[Ace_XL]

Perhaps create a table..??

http://office.microsoft.com/en-001/e...010359200.aspx

[osamfirescu]

it seems i need an OR function between the above two functions ... how do i get both in the same cell so that if option 1 is available then return the value from the first function else return the value from the second function ...

[osamfirescu]

I have something like this:

=IF(AND(SUM(COUNTIF(F3:F6,"*JUD*")),SUM(COUNTIF(G3:G6,"*event1*"))),"Alex","")

=IF(AND(SUM(COUNTIF(F3:F6,"*JUD*")),SUM(COUNTIF(G3:G6,"*event2*"))),"Dan","")

=IF(AND(SUM(COUNTIF(F3:F6,"*JUD*")),SUM(COUNTIF(G3:G6,"*event3*"))),"Ben","")

...

=IF(AND(SUM(COUNTIF(F3:F6,"*JUD*")),SUM(COUNTIF(G3:G6,"*eventx*"))),"Jack","")

and i want all those function in one cell ... how do i make that?

Thanks in advance

[Ace_XL]

How about..

=IF(COUNTIF(F3:F6,"*JUD*"),IF(COUNTIF(G3:G6,"*event1*"),"Alex",IF(COUNTIF(G3:G6,"*event2*"),"Dan",IF(COUNTIF(G3:G6,"*event3*"),"Ben",IF(COUNTIF(G3:G6,"*event4*"),"Jack","")))),"")

[osamfirescu]

Thaaanks !!! it works

Now i have another dileme:

=IF(COUNTIF(F2,"*JUD*"),IF(COUNTIF(G2,"*Step1*"),"Alex",IF(COUNTIF(G2,"*Step2*"),"Dan",IF(COUNTIF(G2,"*Step3*"),"Ben",IF(COUNTIF(G2,"*Step4*"),"Jack","")))),"")

The above formula is the one that works for me, but i want to add more steps to alex ... how do i do that?

=IF(COUNTIF(F2,"*JUD*"),IF(COUNTIF(G2,"*Step1* or *Step2* or Step3*"),"Alex",IF(COUNTIF(G2,"*Step2*"),"Dan",IF(COUNTIF(G2,"*Step3*"),"Ben",IF(COUNTIF(G2,"*Step4*"),"Jack","")))),"")

how do i do that?