CODEWITHSUNDEEP
excel
psorensen
psorensen

Reputation: 827

Excel - Find and replace, set to value of column header

I have a csv with columns that include a 'yes' or null value. I would like to replace each 'yes' with the name of it's column header. I know I could go through each column and F&R, but there's about 40 columns and there will be changes in the future. I would like to streamline it with a formula, but I have no idea how to F&R with a formula.

I have this:

service_1 | service_2 | service_3
yes       | yes       | yes
          | yes       | yes

I'm looking for this:

service_1 | service_2 | service_3
service_1 | service_2 | service_3
          | service_2 | service_3

Thanks for any help!

Upvotes: 2

Views: 4670

Answers (1)

chishaku
chishaku

Reputation: 4643

If 'service_1' is in cell A1 and 'yes' is in cell A2, you can paste this formula in D2 and copy paste across and down to F3. Copy your headers from A1:C1 to D1:F1 to complete your example.

=IF(A2="yes",A$1,"")

Note the absolute reference to the first row headers, A$1.

Don't forget to copy your new data and paste the values if you want to get rid of your original three columns (in your example).

Upvotes: 4

Related Questions