Copying the column heading into all non-empty cells

[LizzyD]

Hello!

I hope someone might be able to help me with this one. I have a ver
simple but big spreadsheet (120 columns and 3000 rows).

The first row is a header row and then all the cells in each column ar
either blank or they have a Y in them. (The headings all represent hote
amenities eg. pool, creche, tennis courts, the 3000 rows are 300
hotels, and the Y indicates if each hotel has that amenity or not.)

All I want to do is find a quick way of replacing all the Y's wit
their respective column headings/amenities. i.e. in the "Pool" column
I want to have Pool written everywhere that a Y is currently written.

Does anyone know of a quick and easy way to do this? The best I ca
come up with so far is to go through each of the 120 columns and us
the Find and Replace method. However, as I'll be getting a ne
spreadsheet full of hotels each week I'd like to find a les
time-consuming method if possible!

Any ideas would be very gratefully received!! Thanks!

Lizzy

 

[Gord Dibben]

Lizzy

One method.........

Assuming you have your current data on Sheet1

Copy the header row only to a new worksheet.

In new worksheet A2 enter =IF(Sheet1!A2="y",A$1)

NOTE: the A$1.......the $ sign must be entered.

Drag/copy across 120 columns and down 3000 rows.

Copy the full range and in place Paste Special>Values>OK>Esc then delete
Sheet1.

You could record a macro while doing this and assign the macro to a button.

Gord Dibben Excel MVP