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

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
17,027
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
17,027
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,114,551
Messages
5,548,707
Members
410,866
Latest member
StuartAllison
Top