How to Return Field Names based on cell values

[csh8428]

I have a data set that looks like this

Name	Has HR	Has Ben	Has Pay
John	Y	N	N
Mike	N	Y	Y

I can't figure out how to get a new column that lists the column names for that row where the value = "N"

For example, the result should look like this

Name	Has HR	Has Ben	Has Pay	Missing
Jon	Y	N	N	Has Ben, Has Pay
Mike	N	Y	Y	Has HR

I was toying with dynamic select, but couldn't quite get it.

Any help is greatly appreciated!

Thanks,
Craig

[CharlieS · Accepted solution]

I would approach this by creating the [Missing] field, then joining that back to the original data. To create this field, I used a Transpose, Filter, and Summarize tool.

Check out the attached example and let me know if that works for you. 

[RolandSchubert]

Hi @csh8428 ,

to create the "Missing" column, I would transpose the values to rows, delct the "N" rows and concatenate using the summarize tool. Then you have to join the new column to the original data and you are done. I've attached a sample workflow. Hope this is helpful.

Best,

Roland

[csh8428]

Thanks!! that worked. I knew I would have to use the summarize function at some point in that tool-set, but couldn't' get that first part.

[koshodi]

Just out of curiosity, how could we tweak this to return just the first occurrence of Value = "N" per record? 

I'm aware a text-to-columns tool would achieve the same effect but just curious if there was another way to accomplish?