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
Solved! Go to Solution.
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
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.
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?