Return Multiple Column Headers Based On Specific Row Content THEN Search Based On Returned Values

Dancing Fish

New Member
Joined
Nov 1, 2021
Messages
6
Office Version
  1. 365
Platform
  1. Windows
I'm trying to translate matrix tables into a search in order to simplify what is on the tables. These are tracking what users are assigned to specific job codes, then what job codes are assigned to which training. The matrix tables are a couple hundred rows.

My first challenge is to get a list of what job codes a user might have and is laid out like below. A user can have multiple job codes and each applicable one is indicated with an "x".

Table 1: User's Job Codes
Job Code 1Job Code 2Job Code 3Job Code 4
User 1xxx
User 2x
User 3xx

If I wanted to search by a specific user ex: User 1, how would I get a list of all job codes applicable to them?

Output desired:
User Name entered hereReturned Job Codes
(User 1)Job Code 1
Job Code 2
Job Code 3



My second challenge is then to search these returned job codes for their assigned training courses. Another matrix table contains this relationship in a similar format. I can invert the table if needed.

Table 2: Training Courses' Job Codes
Job Code 1Job Code 2Job Code 3Job Code 4
Training Course 1xx
Training Course 2x
Training Course 3xx

Looking for the same type of output, just a list of all the training courses for the user.

Output desired:
User Name entered here (User 1)Returned Job CodesReturned Training Courses
Job Code 1Training Course 1
Job Code 2Training Course 3
Job Code 3
 
How about
+Fluff 1.xlsm
ABCDEFGH
1Job Code 1Job Code 2Job Code 3Job Code 4
2User 1xxxJob Code 1User 1
3User 2xJob Code 2User 1
4User 3xxJob Code 3User 1
5Job Code 3User 2
6Job Code 1User 3
7Job Code 3User 3
8
Master
Cell Formulas
RangeFormula
G2:G7G2=FILTERXML("<k><m>"&TEXTJOIN("</m><m>",,IF(B2:E4="x",B1:E1,""))&"</m></k>","//m")
H2:H7H2=FILTERXML("<k><m>"&TEXTJOIN("</m><m>",,IF(B2:E4="x",A2:A4,""))&"</m></k>","//m")
Dynamic array formulas.
Thanks for the quick reply, I'm getting a #CALC! error with TEXTJOIN: Text too long. Do you know if this refers to the actual text in the formula or the data it is parsing?
 
Upvote 0

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
Pretty sure you would only get a #CALC error with the dynmaic array functions, so check that you using filterxml, rather than filter?
 
Upvote 0
Did a bit of testing and I had two issues:

#1 - Some of my job codes included "&", it did not like that so those were removed.
#2 - It seems the limit of this function is to 71 columns, after that I was was getting the #CALC error. It appears I'll just have to do this in chunks.

I appreciate the help again, the formula works as intended when the above is fixed!
 
Upvote 0
Glad you sorted it & thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,947
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top