I have a data set that looks like this NameHas HRHas BenHas PayJohnYNNMikeNYY 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 NameHas HRHas BenHas PayMissingJonYNNHas Ben, Has PayMikeNYYHas HR I was toying with dynamic select, but couldn't quite get it. Any help is greatly appreciated! Thanks, Craig

How to Return Field Names based on cell values

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

Datasets

Preparation

Accepted answers

CharlieS

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.

20200114-AddField.yxmd

All comments

CharlieS

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.

20200114-AddField.yxmd

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

ReturnFieldNames.yxmd

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?

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703