How can I replace each x in a column with its column header?

Monsignor

Board Regular
Joined
May 30, 2011
Messages
158
Starting with a table like this:

NAMEIllinoisTorontoMoscowBelize
Sarahx
Robertoxxx
Karenxx
Olivia
Toddxx

<tbody>
</tbody>



The desired result is:

NAMEIllinoisTorontoMoscowBelize
SarahIllinois
RobertoTorontoMoscowBelize
KarenIllinoisMoscow
Olivia
ToddMoscowBelize

<tbody>
</tbody>


I've unpivoted, outer joined, added index columns, replaced values and found several messy ways to get a result. The problems are with:
- Someone who doesn't have an x will disappear. Like Olivia in my example.
- Adding a new City column doesn't show up with "refresh"

Any help is greatly appreciated!
 

Some videos you may like

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
16,881
Office Version
  1. 2013
Platform
  1. Windows
If you want a Vba solution you can try this:
Code:
Sub City_Names()
'Modified 1-27-18 3:00 PM EST
Application.ScreenUpdating = False
Dim c As Range
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
    For Each c In Range("A2:E" & Lastrow)
        If c.Value = "x" Then c.Value = Cells(1, c.Column).Value
    Next
Application.ScreenUpdating = True
End Sub
 

Monsignor

Board Regular
Joined
May 30, 2011
Messages
158
Thanks. I appreciate the effort. However, I'm looking for a PowerBI/PowerQuery solution.
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
16,881
Office Version
  1. 2013
Platform
  1. Windows
Thanks. I appreciate the effort. However, I'm looking for a PowerBI/PowerQuery solution.
OK. I do not know how to do that. I'm sure someone else here at Mr. Excel may be able to help you.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,501
Messages
5,529,240
Members
409,857
Latest member
KailuaTown
Top