May 11 2023 03:44 AM - edited May 11 2023 03:46 AM
Hi all,
Thanks in advance for any help you can provide.
I have data as a call matrix where I can see the volume of calls Agents are transferring to specific lines (Agents XYZ (rows), or LinesABC(cols)).
I want to know if Agent A who works in Team A is transferring any calls to Lines A, B or C (could be one of multiple lines specified in external lookup table, based on the Team they are assigned to).
To do this I broke it down into the basics of goals: GOAL 1 - is there a value in the matrix for Agent A? GOAL 2 - is the LINE where the call is transferred to linked to the Agent A based on their Team?
Agent | Agent Team | Lines of Interest | LineA | LineB | LineC | Is There A Value? | Which Line Has The Value? | Does The Line Match Any Lines of Interest | ||
Agent A | Team A | LineA, LineB, LineC | 1 | 1 | LineA | FALSE | ||||
Agent B | Team B | LineB, , | 5 | 1 | LineB | FALSE | ||||
Agent C | Team C | LineB, LineC, | 0 | FALSE |
So far I have Agent Name in Col A
Agent Team in Col B
At the moment I have Xlookup of the concatenated Lines in Col C based on Data available in external Lookup Table (linked to Agent Team) - (in lookup table the Lines appear in separate cols).
In Row 28 I have the Lines that Agents have transferred calls to.
Within the matrix there is a value based on the number of calls transferred by Agent to whichever Line.
So far I have:
1) Identified if Agent A has transferred any calls to ANY line "=COUNTIF(D29:G29,">0")"
2) Created a very ramshackle way of identifying the name of the Line it was transferred to
"IF(D29>0,E$28,"")"
Where Row 28 refers to the name of the specific Line. This formula is repeated per column for as many cols as there are lines available within the matrix. (ideally I want to know if ANY of the cols in range (D29:G29)>0, then return the names of the specific lines in separate cols - where I can perform the lookup below on those specific).
3) I now want to perform a LOOKUP to see if any of THOSE cols contain the name of any Lines that Agent A is associated with, based on their Team.
I'm basically ignoring the Lookup table and Team Name at this time, and have:
=COUNTIF(I29:K29,"*"&C29&"*")>0
Since the data in C29 (the reference of possible Lines) is concatenated ("LineA, LineB, LineC") I have used a wildcard. BUT the data in C29 is the result of another Lookup to the external Lookup Table.
The result I'm getting at the moment is "FALSE" regardless of whether the result is actually TRUE or FALSE. I did at one time get a "TRUE" value, but this is when I did a hard search for the specific cell with the concat Lines within the lookup table, and I've since not been able to replicate this.
4) The next step for me would THEN be to identify WHICH of those Lines where the value >0 matches the data within cell C29.
Can anyone suggest
A) a more efficient way of performing this?
B) a way to resolve the issue I'm having when searching for a partial match when comparing data within a range (I29:K29) to a concatenated list C29 (where data in C29 is the result of a lookup).
I hope that this makes sense! It's so confusing to explain Excel specifics.
May 12 2023 08:50 AM
SolutionIt sounds like you're trying to do a partial match lookup on concatenated data that was created from an external lookup table. One issue you might be facing is that the data in C29 might contain spaces or other non-printable characters that could be throwing off your wildcard match.
To address this, you could try using the TRIM function to remove any extra spaces from the concatenated data in C29 before using the wildcard match.
For example:
=COUNTIF(I29:K29,""&TRIM(C29)&"")>0
Another approach you could try is to use the FILTER function to extract the columns that contain non-zero values, and then perform the partial match lookup on those columns.
For example:
=COUNTIF(FILTER(D29:G29,D29:G29>0),""&C29&"")>0
This formula first uses the FILTER function to extract the columns in D29:G29 that contain non-zero values. Then, it performs the partial match lookup on those columns using the COUNTIF function and the wildcard match.
Finally, to identify which of the lines in the extracted columns match the data in C29, you could use another FILTER function combined with the INDEX and MATCH functions.
For example:
=INDEX($E$28:$H$28,1,MATCH(TRUE,INDEX(FILTER(D29:G29,D29:G29>0),0,MATCH(""&TRIM(C29)&"",$E$28:$H$28,0))>0,0))
This formula first uses the FILTER function to extract the columns in D29:G29 that contain non-zero values, as before. Then, it uses the INDEX and MATCH functions to find the column in the extracted range that matches the data in C29. Finally, it uses the INDEX function again to return the name of the matching line from row 28. Note that this is an array formula, so you'll need to press Ctrl+Shift+Enter instead of just Enter to enter it in the cell.
The dynamic array formulas were introduced in Excel 365 and are not available in earlier versions.
It's also worth noting that some older versions of Excel may not be able to open workbooks created in later versions of Excel, especially if they use newer features or formats. So if you are working with others or sharing your workbooks, it's important to ensure compatibility across versions.
May 12 2023 09:07 AM
May 15 2023 01:10 AM
May 12 2023 08:50 AM
SolutionIt sounds like you're trying to do a partial match lookup on concatenated data that was created from an external lookup table. One issue you might be facing is that the data in C29 might contain spaces or other non-printable characters that could be throwing off your wildcard match.
To address this, you could try using the TRIM function to remove any extra spaces from the concatenated data in C29 before using the wildcard match.
For example:
=COUNTIF(I29:K29,""&TRIM(C29)&"")>0
Another approach you could try is to use the FILTER function to extract the columns that contain non-zero values, and then perform the partial match lookup on those columns.
For example:
=COUNTIF(FILTER(D29:G29,D29:G29>0),""&C29&"")>0
This formula first uses the FILTER function to extract the columns in D29:G29 that contain non-zero values. Then, it performs the partial match lookup on those columns using the COUNTIF function and the wildcard match.
Finally, to identify which of the lines in the extracted columns match the data in C29, you could use another FILTER function combined with the INDEX and MATCH functions.
For example:
=INDEX($E$28:$H$28,1,MATCH(TRUE,INDEX(FILTER(D29:G29,D29:G29>0),0,MATCH(""&TRIM(C29)&"",$E$28:$H$28,0))>0,0))
This formula first uses the FILTER function to extract the columns in D29:G29 that contain non-zero values, as before. Then, it uses the INDEX and MATCH functions to find the column in the extracted range that matches the data in C29. Finally, it uses the INDEX function again to return the name of the matching line from row 28. Note that this is an array formula, so you'll need to press Ctrl+Shift+Enter instead of just Enter to enter it in the cell.
The dynamic array formulas were introduced in Excel 365 and are not available in earlier versions.
It's also worth noting that some older versions of Excel may not be able to open workbooks created in later versions of Excel, especially if they use newer features or formats. So if you are working with others or sharing your workbooks, it's important to ensure compatibility across versions.