Help with OR Function or Better Function if there's any!

[Kamote.Que]

Hi All,

I quite newbie with excel and I'm really having hard time making formulas, especially with this one.

I'm working with 4000+ data's so I need to use formulas as much as possible.

Here's the scenario:
A1 = apple
B1 = banana
C1 = Mark
D1 = mango
E1 = Jeff

I want to achieve if the cell contains apple or banana or mango the result should be Fruits, while if its Mark or Jeff the result should be Person.

Can anyone help with the OR function? or advise instead of there's a better one.

Thanks in advance.

Kamote.Que

[sktneer]

Hi Kamote,
Welcome to the forum.

There are multiple possible ways to achieve what you are asking. Would you provide some more information as you said you are dealing with 4000+ rows.
I would suggest that you create two lookup tables one for fruit and another for person, that will make your task much easier.

So better if you attach a sample workbook. Make sure there is just enough data to demonstrate your need. 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 shown, mock them up manually if necessary.

Remember to desensitize the data.

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

View Pic

[ganeshkumar]

Hi here am used if condition formula, drag the formula in row as you needed,

[samba_ravi]

Create a table with two columns
1 for name (Apple, Banana, Mark, Mango, Jeff etc., ) and 2nd for the name in 1st column which it belongs (Fruit, Fruit, Perosn, Fruit, Person ...)
and use Vlookup formula

[Kamote.Que]

@ganeshkumar - the problem that i'm actually facing is the list of users. The inactive users were accidentally mixed with the active users, not only that some users have also duplicate names. So all in all i have 4K+ of records.

I was able to identify at least 20 inactive users and 100 active users, and I want to create a formula that would search the records and mark those 20 accounts as inactive and 100 accounts as Active.

So result is either Active and inactive, but I just dont how to create the formula.

@nflsales - could you tell me how?

[vlady]

Welcome to the forum Kabayang kamote.que

it is better to post a sample file for the members to work with. you can put fake data to illustrate the problem.\

To attach a sample workbook. Make sure there is just enough data to demonstrate your need. 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 shown, mock them up manually if necessary.

Remember to desensitize the data.

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

View Pic

Regards,
vlady

[Kamote.Que]

Thanks for replying...

I want to search the cells if its contains a texts..

Example:
Column A:
briones kamote
arnie m
keno
MMJ
Art
kamote
test
mj
te arnie
te brioneS
te keno


Search each cell if it's contains "keno", "mj", "briones", "arnie"...etc...
I've tried below formula, but its just too long..

=IF(ISNUMBER(SEARCH("keno",B2)),"Active",IF(ISNUMBER(SEARCH("mj",B2)),"Active")).........etc...

[samba_ravi]

pls attach a sample file with which words you are looking for and where you want to look

[Kamote.Que]

Please see attached.

I want all texts in Sheet2 able to search each cell in sheet1.

If it matches, Column B on Sheet1 will display Active or Inactive.

[vlady]

try

Please Login or Register  to view this content.

drag formula down using fill handle
is this what you want?

Note that this depends on your data like

Briones kamote
Briones saging
they have both the word briones and both will return active

[Kamote.Que]

Did some test and it works really great... KUDOS vlady!!! really appreciate it.

[vlady]

Walang anuman kabayan.

Regards,
Vlady

Note: You can mark your thread solved.

How?
New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save

Note:
You can also thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given.
By doing so you can add to the reputation(s) of those who helped and shared their time in helping you.