Hi Everyone,
I have characters that exist within a string of names that correspond with a specific skill type
Im trying to find a way to match series of characters within each name to a table reference
I attached the excel that I hope is much clearer then my explanation
Thank You
Name charactors and table reference
-
- 5StarLounger
- Posts: 1177
- Joined: 22 Jul 2013, 18:29
Name charactors and table reference
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 78577
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Name charactors and table reference
In your example, Paul Smith has T4 and G, but not J. Why does he have Skill 1?
Best wishes,
Hans
Hans
-
- 5StarLounger
- Posts: 1177
- Joined: 22 Jul 2013, 18:29
Re: Name charactors and table reference
He has |J|
-
- Administrator
- Posts: 78577
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Name charactors and table reference
Oh sorry - you left the column too narrow, so that was not visible.
=IFS(ISNUMBER(SEARCH("|T4|", A2)*SEARCH("|G|", A2)*SEARCH("|J|",A2)),"Skill 1", ISNUMBER(SEARCH("|G|", A2)*SEARCH("|J|",A2)),"Skill 2", ISNUMBER(SEARCH("|G|", A2)),"Skill 3", ISNUMBER(SEARCH("|J|",A2)),"Skill 4", TRUE, "No Skills")
=IFS(ISNUMBER(SEARCH("|T4|", A2)*SEARCH("|G|", A2)*SEARCH("|J|",A2)),"Skill 1", ISNUMBER(SEARCH("|G|", A2)*SEARCH("|J|",A2)),"Skill 2", ISNUMBER(SEARCH("|G|", A2)),"Skill 3", ISNUMBER(SEARCH("|J|",A2)),"Skill 4", TRUE, "No Skills")
Best wishes,
Hans
Hans
-
- 5StarLounger
- Posts: 1177
- Joined: 22 Jul 2013, 18:29
Re: Name charactors and table reference
Any way of creating a more dynamic code/formula that references the table? If I change the table values the skill changes accord
-
- 5StarLounger
- Posts: 1177
- Joined: 22 Jul 2013, 18:29
Re: Name charactors and table reference
Also unfort dont have the 'ifs' option
-
- 4StarLounger
- Posts: 587
- Joined: 14 Nov 2012, 16:06
Re: Name charactors and table reference
="Skill " & CHOOSE(LEN(A2)-LEN(SUBSTITUTE(A2;"|";""));2;3+NOT(ISERROR(SEARCH("|J|";A2)));2;1)
-
- Administrator
- Posts: 78577
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Name charactors and table reference
It's an unfortunate setup. I'd change Table Reference to:
In B2 on Text Example, as an array formula, confirmed with Ctrl+Shift+Enter:
="Skill " &5-MIN(SUMPRODUCT(ISNUMBER(SEARCH("|"&'Table Reference'!$C$3:$C$5&"|",'Text Example'!A2))*'Table Reference'!$D$3:$D$5),4)
Fill down.
In B2 on Text Example, as an array formula, confirmed with Ctrl+Shift+Enter:
="Skill " &5-MIN(SUMPRODUCT(ISNUMBER(SEARCH("|"&'Table Reference'!$C$3:$C$5&"|",'Text Example'!A2))*'Table Reference'!$D$3:$D$5),4)
Fill down.
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans