combination of 1, 2 and 5 in the corresponding cell in the full dataset tab

Hi! I want to insert a function into a cell in Excel.

In my spreadhset, I have two tabs; one with a full dataset, the other one empty. I am looking to fill the empty tab by recoding the data in the full dataset tab using an Excel function in a cell on the empty tab.

In the full dataset, I have 242 rows. In each cell, the cell contains a combination of numbers from 1-6. For example, 1 or 1,2,3 or 2,4,5 . Here is an example of what the first cell in the first 10 rows look like:

1,3,4
1,4
1
1,4
6
1
1,4
1,2,3
1
5

I am interested in the numbers 1, 2 and 5. I am looking to add a function into the cells in the empty tab which tells me about the combination of 1, 2 and 5 in the corresponding cell in the full dataset tab. Here is what I am interested in, specifically, for every cell:

Functions
If the cell contains the number 1 anywhere, but not the numbers 2 or 5, return 11
If the cell contains the number 2 anywhere, but not the numbers 1 or 5, return 12
If the cell contains the numbers 1 and 2 anywhere, return the number 13
If the cell contains “5” anywhere, return the number 13
If the cell contains any other combination or is blank, return the number 20

I am looking to write these instructions into an Excel Function for the cells in the empty tab. I have had a go, but it is not right. Here is my attempt (note: the word 'CELL' here denotes the Cell number e.g., A5):

=IF(AND(CELL,*1*, CELL, “<>*2*”, CELL, “<>*5*”), 11, IF(AND(CELL,*2*, CELL, “<>*1*”,CELL, “<>*5*”), 12, IF(AND(CELL, *1*, CELL, *2*), 13, IF(CELL, *5*), 13, 20)

If you can help me find the correct full function, I would greatly appreciate it. Thanks very much.

Administrative Note:

Welcome to the forum.

We would very much like to help you with your query, however the thread title does not really convey what your request is about.

Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).

Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.

I cannot do it for you (forum trouble). Please change your title . Perhaps "combination of 1, 2 and 5 in the corresponding cell in the full dataset tab" will be more descriptive

Please try

B2

Formula:

Please Login or Register  to view this content.

Regards.

PS:
Formulation may easier if you have re-order your logic. ^_^



If the cell contains any other combination or is blank, return the number 20

@ menem

Administrative Note:

Welcome to the forum.

Sorry, but your post does not comply with Rule #6 of our Forum RULES:

Please do not ignore requests by Administrators, Moderators and senior forum members regarding forum rules.

If you are unclear about the request or instruction, then send a private message to them asking for clarification.

All Participants:

Please do not post a reply in a thread where a Moderator or Administrator has requested an action that has not yet been complied with (e.g. title change, code tags requested, etc.). Thanks.

Off topics.

Thanks to AliGW to recall my prohibited post.
I was not mean to do that (post a solution against the rules) , I was open the topic and left it open while open Excel to solve the problem,
then back to browser , post the solution (while not refresh before post). I'll try to refresh browser before post next time.

Regards.


Answer to McLeod_Research.

After I've test on my PC , I found the problem to search by using "*number*" if cell contain only one number (and data type is number , not text).
And if I use IF(...) I have to if for both of them IF( finding text-number or finding number ... ) the formula length can be imagination.^_^
But with COUNTIF, I can use a short form of Array [ {"*1*","*2*","*5*",1,2,5} ] , so formula can be shorter.
(When you use COUNTIF as Array formula , the number will be result;result;... for each of array ( {"*1*","*2*","*5*",1,2,5} )
and you can see only first of the results , so , SUM can group all result together)

Solution step is try to branch possible case in a group. Write in IF-Else form may easy to explain (in case of my English).

Please Login or Register  to view this content.

Regards.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.