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.

 

[Fluff]

How about

Fluff.xlsm
	C	D	E
10	Work Center	Dept_Code	# Trained Individuals
11	F_NAME	F	0
12	L_NAME	L	0
13	GRP_CRB	GRP	2
14	GRP_GRPH	GRP	3
15	GRP_OVEN	GRP	1
16	WV_LM	WV	3
17	WV_HM	WV	2
18	MACH_P1	MACH	2
19	MACH_P2	MACH	2
20
Data

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

 

[Pestomania]

This did great! Is there a way to remove the F_Name, L_Name, and Operator_Group? If not, that's fine but this is amazing!

 

[Fluff]

Will it always be the 1st 3 columns to ignore?

 

[Pestomania]

Will it always be the 1st 3 columns to ignore?

Unfortunately, not. The actual table has these fields at the beginning of the table:

EmployeeNo

UDF_LOCATION

Facility

HireDate

UDF_EFFICIENCY

UDF_F_NAME

UDF_L_NAME

UDF_OPERATOR_GROUP

UDF_SCHEDULE

UDF_MACHINE

UDF_TYPE

I don't suspect any new fields will be added that meet the criteria of 2 underscores. I think if I can filter those out manually, that would be fine.

 

[Fluff]

Is there a specific column name that will always be the 1st to look at?

 

[Pestomania]

Is there a specific column name that will always be the 1st to look at?

The EmployeeNo field will always be the first.

 

[Fluff]

I meant the first you want to include.

 

[Pestomania]

I meant the first you want to include.

Apologies, here we go:

UDF_DENS_CARB

 

[Fluff]

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.