How to populate a cell with the names of columns which contain missing data.

[nthorley]

I have a database for an individual customers' details. I'm building a simple reporting tool that tells me if all data fields are filled for that customer, how many are not and which in particular are not. The last is proving difficult. I want to populate cell U18 with the column names (K9: AC9) of columns which have "(blank)" in cells K10: AC10. This way, the reporting tool will tell me which data fields are missing for that customer, not just how many. All data is on the same sheet for now. Any guidance would be much appreciated.

 

[Toadstool]

Hi Nthorley,

I think there's an easier solution using TEXTJOIN which came out in Excel 2019 but you and I are on 2016 so the only approach I can think of is a bunch of worker columns:

ntHORLEY.xlsx
	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV	AW
9	Col:11	Col:12	Col:13	Col:14	Col:15	Col:16	Col:17	Col:18	Col:19	Col:20	Col:21	Col:22	Col:23	Col:24	Col:25	Col:26	Col:27	Col:28	Col:29
10	Dog			Cat					Sheep						Goat							Col:12	Col:13		Col:15	Col:16	Col:17	Col:18		Col:20	Col:21	Col:22	Col:23	Col:24		Col:26	Col:27	Col:28	Col:29
11
12
13
14
15
16
17
18											Col:12 Col:13 Col:15 Col:16 Col:17 Col:18 Col:20 Col:21 Col:22 Col:23 Col:24 Col:26 Col:27 Col:28 Col:29
Sheet1

Cell Formulas
Range	Formula
K9:AC9	K9	="Col:"&COLUMN()
AE10:AW10	AE10	=IF(K10="",K$9&" ","")
U18	U18	=AE10&AF10&AG10&AH10&AI10&AJ10&AK10&AL10&AM10&AN10&AO10&AP10&AQ10&AR10&AS10&AT10&AU10&AV10&AW10