Table Efficiency Cleanup

[Pestomania]

Hi everyone!

I have the below data that I am hoping to clean up in the final table. We will use this table for supervisors and managers to identify how many employees are trained in each area. I added "Notes" in G11 about what I am hoping to get to, but have been unable to understand how to get to what I want (the highlighted green C14:E20). I have about 400 columns x 300 rows in this table that I am hoping to get cleaned up into a table like this. A pivot table didn't work because the count of columns does not work and I cannot figure out how to get down to a pivot table that looks similar to the table below.

Prestons Playground for Modeling.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X
1	EmployeeNo	UDF_F_NAME	UDF_L_NAME	UDF_GRP_CRB	UDF_GRP_GRPH	UDF_GRP_OVEN	UDF_WV_LM	UDF_WV_HM	UDF_MACH_P1	UDF_MACH_P2	UDF_GRP_CRB_CERT_DATE	UDF_GRP_CRB_CERT_LEVEL	UDF_GRP_GRPH_CERT_DATE	UDF_GRP_GRPH_CERT_LEVEL	UDF_GRP_OVEN_CERT_DATE	UDF_GRP_OVEN_CERT_LEVEL	UDF_WV_LM_CERT_DATE	UDF_WV_LM_CERT_LEVEL	UDF_WV_HM_CERT_DATE	UDF_WV_HM_CERT_LEVEL	UDF_MACH_P1_CERT_DATE	UDF_MACH_P1_CERT_LEVEL	UDF_MACH_P2_CERT_DATE	UDF_MACH_P2_CERT_LEVEL
2	1	Person 1	Person 1 L	Y	Y	N	N	Y	N	Y	N	N	N	N	Y	Y	N	Y	N	Y	N	N	N	N
3	2	Person 2	Person 2 L	Y	Y	N	Y	N	Y	N	Y	N	N	Y	Y	Y	N	Y	Y	N	Y	N	Y	N
4	3	Person 3	Person 3 L	N	Y	N	Y	Y	Y	Y	N	N	N	Y	N	N	N	Y	N	N	Y	N	N	N
5	4	Person 4	Person 4 L	N	N	Y	Y	N	N	N	N	N	Y	Y	Y	Y	Y	N	N	N	Y	Y	N	N
6
7
8
9
10			Work Center	Dept_Code	# Trained Individuals		Notes:
11			#VALUE!	#VALUE!	#VALUE!		I only want to show the Work Centers that start with "UDF_" and does not have more than 2 "_" in the statement (highlighted green). It would be great to make the table auto-update if a new column in the "Headers" was added that meet the guidelines. The table above is a SQL connection that gets refreshed regularly.
12			F_NAME	F	0
13			L_NAME	L	0
14			GRP_CRB	GRP	2
15			GRP_GRPH	GRP	3
16			GRP_OVEN	GRP	1
17			WV_LM	WV	3
18			WV_HM	WV	2
19			MACH_P1	MACH	2
20			MACH_P2	MACH	2
21			GRP_CRB_CERT_DATE	GRP	1
22			GRP_CRB_CERT_LEVEL	GRP	0
23			GRP_GRPH_CERT_DATE	GRP	1
24			GRP_GRPH_CERT_LEVEL	GRP	3
25			GRP_OVEN_CERT_DATE	GRP	3
26			GRP_OVEN_CERT_LEVEL	GRP	3
27			WV_LM_CERT_DATE	WV	1
28			WV_LM_CERT_LEVEL	WV	3
29			WV_HM_CERT_DATE	WV	1
30			WV_HM_CERT_LEVEL	WV	1
31			MACH_P1_CERT_DATE	MACH	3
32			MACH_P1_CERT_LEVEL	MACH	1
33			MACH_P2_CERT_DATE	MACH	1
34			MACH_P2_CERT_LEVEL	MACH	0
Sheet8

Cell Formulas
Range	Formula
C11:C34	C11	=TRANSPOSE(CHOOSEROWS(UNIQUE(MID(JT_Employee[#Headers],FIND("_",JT_Employee[#Headers])+1,LEN(JT_Employee[#Headers])-4)),ROWS($E$11:$E11)))
D11:D34	D11	=LEFT(C11,FIND("_",C11)-1)
E11:E34	E11	=COUNTIFS(INDIRECT("JT_Employee[UDF_"&SUBSTITUTE(C11,"-","_")&"]"),"Y")
Dynamic array formulas.

 

[Pestomania]

Ok, how about

Fluff.xlsm
	C	D	E
10	Work Center	Dept_Code	# Trained Individuals
11	DENS_CARB	DENS	2
12	GRP_GRPH	GRP	3
13	GRP_OVEN	GRP	1
14	WV_LM	WV	3
15	WV_HM	WV	2
16	MACH_P1	MACH	2
17	MACH_P2	MACH	2
18
Data

Cell Formulas
Range	Formula
C11:C17	C11	=LET(h,TOCOL(UNIQUE(DROP(TEXTAFTER(JT_Employee[#Headers],"_"),,XMATCH("udf_dens_carb",JT_Employee[#Headers])-1)),2),FILTER(h,LEN(h)-LEN(SUBSTITUTE(h,"_",""))=1))
D11:D17	D11	=TEXTBEFORE(C11#,"_")
E11:E17	E11	=COUNTIFS(INDIRECT("JT_Employee[UDF_"&C11#&"]"),"Y")
Dynamic array formulas.

Is there a way to say if it has greater than 1 underscore, but does not end with the words date or level? I changed the filter to greater than 1 but am at a lost on the date or level words.

=LET(h,TOCOL(UNIQUE(DROP(TEXTAFTER(JT_Employee[#Headers],"_"),,XMATCH("udf_dens_carb",JT_Employee[#Headers])-1)),2),FILTER(h,LEN(h)-LEN(SUBSTITUTE(h,"_",""))>1))

 

[Fluff]

Can you post some sample data.

 

[Pestomania]

Hi, here are a few of the column names. I would only want to return any that do not end with "data" or "level". It would be great if for some reason in the future, there are other words to ignore, I could add those.

Training Cert Excel Export.xlsx
	CD	CE	CF	CG	CH	CI
1	UDF_PLA_XRAY	UDF_EOP_READING	UDF_WVE_PLA_CERT_DATE	UDF_WVE_PLA_CERT_LEVEL	UDF_EOP_LIND_CERT_DATE	UDF_EOP_READING_CERT_DATE
JT_Employee_Data_Table

 

[Fluff]

That doesn't make sense if you are excluding columns with more than 1 underscore after the UDF_ as they will be excluded any way.

 

[Pestomania]

Sorry, I didn't add any of the new columns. Our safety department added these fields that need to be in there but because of the number of underscores, it doesn't work. That is why I was thinking if I can filter out items that have certain endings, that would bypass the underscore issue.

Training Cert Excel Export.xlsx
	HA	HB	HC	HD	HE	HF	HG	HH
1	UDF_SAFE_LASER_CLSS	UDF_SAFE_LASER_HNDS_ON	UDF_SAFE_LOTO_CLSS	UDF_SAFE_LOTO_HNDS_ON	UDF_SAFE_LOTO_SRL_NM	UDF_SAFE_PWR_TRCK_CLSS	UDF_SAFE_PWR_TRCK_HNDS_ON	UDF_SAFE_RAD_SFTY_CLSS
JT_Employee_Data_Table

 

[Fluff]

How about

=LET(h,TOCOL(UNIQUE(DROP(TEXTAFTER(JT_Employee[#Headers],"_"),,XMATCH("udf_dens_carb",JT_Employee[#Headers])-1)),2),FILTER(h,(TEXTAFTER(h,"_",-1)<>"Date")*(TEXTAFTER(h,"_",-1)<>"level")))

 

[Pestomania]

Worked like a dream!

 

[Fluff]

You're welcome & thanks for the feedback.