Use column header as value in another column

[jaideep9]

Hi all,

I have 100+ columns with values 0,1 or null. I want to create a new column with values same as column headers of those 100+ columns if they have value == 1.

I know this can be done using formula tool with IF condition, but i have 100+ columns here. Any idea what should be the best bet?

Thanks

[AngelosPachis · Accepted solution]

Hi @jaideep9 ,

You can use a transpose tool to convert those 100 columns into rows. In the output tool of the transpose tool, you will get one column called name (which contains all your column headers), one column called Value (which contains the values for those column headers) and then any other columns you have grouped by.

The, since you only care about column headers that have a value of 1, you can use a filter tool to filter the column Value=1. Finally, with a summarize tool you can group by the column [Name], and that will return you the distinct column headers that have a value of 1 in your dataset.

Hope that helps,

Angelos

[apathetichell · Accepted solution]

one quick addendum to what @AngelosPachis wrote -

when your columns are transposed into rows the resulting entries (in the [name] fields) are strings - so the 1 would actually be "1" -  to convert them back to numbers you can use a select, or a multi-field formula.

[Qiu · Accepted solution]

@jaideep9 

Just an illustration of idea from @AngelosPachis  and @apathetichell 

[jaideep9]

Spot on! @AngelosPachis

[ChrisTX]

To include a column in the list of column names, does every single value in  column need to equal 1?

Do you want to include the column name if a single value is not equal to 1?

[Qiu]

@jaideep9 

Thank you for the accept mark 😁