Pulling two pieces of info from table into one cell

[ExcelGuy12345]

Hello All,

Is it possible to enter two pieces of text information into a cell and have it calculate an answer?

For example, I'm trying to make it so an employee can simply punch in a code word for a "job" and have it compile the total time in an adjacent cell. The problem is that I need the ability for him to enter two or more different words into the cell and have it spit out the total calculated time on the other end. I'm trying not to change too much on the sheet so I don't confuse things for him.

Currently, I have a SUMPRODUCT ----=SUMPRODUCT(SUMIF(K2:K4,B2,L2:L4)*C2)---- pointing to a coded table where "Job A" = 5 minutes, "Job B" = 10 minutes, etc. So, all he has to do is enter "Job A" into his cell and it returns a time. However, I need him to be able to enter "Job A/Job B/Job C" and have it add all the times together (in the mentioned formula, the cell would be "B2".)

Also, is there a way to make it so he doesn't have to type in the exact word to get the desired result? Sometimes, there is a typo which makes the key word unrecognizable so the formula doesn't work. I'd like to be able to make it so he doesn't have to type exactly "Job A/Job B" or whatever. I'd like that if he used a "," instead of a "/" or added an extra space somewhere, it wouldn't throw things off.

Thanks for the assistance, and if there's any more information needed, please let me know!

 

[Roderick_E]

This can be done if the user inputs a consistent delimiter between the job codes, like , or / Also you could build a hidden index table with most common nicknames for the job codes so it could result with jobA or A

 

[Roderick_E]

Is there going to be a maximum number of job codes the person will enter into cell B2?

If so, I can work on the "test" part which can count how many times the delimiter / is found and then parse the input string.

=IF(ISERROR(FIND("/",SUBSTITUTE(B2,",","/")))=TRUE,SUMPRODUCT(SUMIF(K:K,B2,L:L)*C2),[COLOR=#ff0000]"test"[/COLOR])

 

[ExcelGuy12345]

Is there going to be a maximum number of job codes the person will enter into cell B2?

If so, I can work on the "test" part which can count how many times the delimiter / is found and then parse the input string.

=IF(ISERROR(FIND("/",SUBSTITUTE(B2,",","/")))=TRUE,SUMPRODUCT(SUMIF(K:K,B2,L:L)*C2),[COLOR=#ff0000]"test"[/COLOR])

Thanks for the reply!

Currently, there are only three job codes which need to be entered. So, the maximum entered per cell would look like: "Job A/Job B/Job C". Most instances would only require one of the three, but could also be any two of them. They could be entered in any order.

 

[Roderick_E]

Well the easiest way would be just to modify your list with the potential iterations.
Job A
Job B
Job C
Job A/Job B
Job A/Job C
Job A/Job B/Job C
Job B/Job A
Job B/Job C
Job B/Job A/Job C
Job B/Job C/Job A
.....
could even add
A
B
C
A/B/C
A/B
A/C
...
But I'll look at doing the delimiter searching part if you still want.

 

[ExcelGuy12345]

Well the easiest way would be just to modify your list with the potential iterations.
Job A
Job B
Job C
Job A/Job B
Job A/Job C
Job A/Job B/Job C
Job B/Job A
Job B/Job C
Job B/Job A/Job C
Job B/Job C/Job A
.....
could even add
A
B
C
A/B/C
A/B
A/C
...
But I'll look at doing the delimiter searching part if you still want.

That's my current set-up, but it gets a little cumbersome. Thank you for the information about the delimiter, I'll research to see if I can figure what that's all about.

 

[Roderick_E]

This would process 2 jobs in the cell. Still working on 3 job...

=IF(ISERROR(FIND("/",SUBSTITUTE(B2,",","/")))=TRUE,SUMPRODUCT(SUMIF(K:K,B2,L:L)*C2),IF((LEN(SUBSTITUTE(B2,",","/"))-LEN(SUBSTITUTE(SUBSTITUTE(B2,",","/"),"/","")))/LEN("/")=1,(SUMPRODUCT(SUMIF(K:K,TRIM(MID(SUBSTITUTE(B2,",","/"),1,FIND("/",SUBSTITUTE(B2,",","/"))-1)),L:L))+SUMPRODUCT(SUMIF(K:K,TRIM(MID(SUBSTITUTE(B2,",","/"),FIND("/",SUBSTITUTE(B2,",","/"))+1,LEN(B2))),L:L)))*C2,"unable to process 3 jobs"))

 

[ExcelGuy12345]

This would process 2 jobs in the cell. Still working on 3 job...

=IF(ISERROR(FIND("/",SUBSTITUTE(B2,",","/")))=TRUE,SUMPRODUCT(SUMIF(K:K,B2,L:L)*C2),IF((LEN(SUBSTITUTE(B2,",","/"))-LEN(SUBSTITUTE(SUBSTITUTE(B2,",","/"),"/","")))/LEN("/")=1,(SUMPRODUCT(SUMIF(K:K,TRIM(MID(SUBSTITUTE(B2,",","/"),1,FIND("/",SUBSTITUTE(B2,",","/"))-1)),L:L))+SUMPRODUCT(SUMIF(K:K,TRIM(MID(SUBSTITUTE(B2,",","/"),FIND("/",SUBSTITUTE(B2,",","/"))+1,LEN(B2))),L:L)))*C2,"unable to process 3 jobs"))

That's great!!! It works just how I need it to. I've been banging my head against the desk trying to figure this out. Thanks so much.

 

[jtakw]

Hi,

To avoid entry Typos, possible different delimiters ( , / space nothing etc. e.g. A,B A/B A B AB), which makes it Very difficult to come up with a formula that's guaranteed Not to fail.

This is what I recommend, since there are Only 3 jobs, that means there are Only 7 possible unique combinations (unless the same Job is done more than once, e.g. JobA/A, then the table needs to be updated), I would create a Table (in my sample, E1:F8), Use Data Validation for the Enter Cell (in my sample, B2) as a Drop Down List referencing E2:E8, then use a simple VLOOKUP formula to calculate the Time:

Edit, will repost sample.

 

[jtakw]

Hi,

To avoid entry Typos, possible different delimiters ( , / space nothing etc. e.g. A,B A/B A B AB), which makes it Very difficult to come up with a formula that's guaranteed Not to fail.

This is what I recommend, since there are Only 3 jobs, that means there are Only 7 possible unique combinations (unless the same Job is done more than once, e.g. JobA/A, then the table needs to be updated), I would create a Table (in my sample, E1:F8), Use Data Validation for the Enter Cell (in my sample, B2) as a Drop Down List referencing E2:E8, then use a simple VLOOKUP formula to calculate the Time:

Edit, will repost sample.

OK, here's my sample:

Book1
	B	C	D	E	F
1	Entry Here	Time		Job List	Time/Minutes
2	JobC	15		JobA	5
3				JobA/B	15
4				JobA/B/C	30
5				JobA/C	20
6				JobB	10
7				JobB/C	25
8				JobC	15
Sheet30

Cell Formulas
Range	Formula
C2	=IFERROR(VLOOKUP(B2,E2:F8,2),"")