Excel/Replacing a blank adjoining cell with a value
How do I FIND values in one cell and REPLACE in an adjoining cell whilst maintaining the integrity of cell 1?
So, my colleague has set up a spreadsheet, but included only values in 1 column and not the reference value in the next cell along the row.
Each column contains different values on each row going downwards.
A similar set of values then appears (in a random order) in Column 3, then 5, then 7 etc...
Each value in Column 1,3,5.. needs the same reference value across the sheet entering in the adjoining row cell (the even numbered column in this case).
So, for example, I need to FIND
Column 1 Column2
and replace with
Column 1 Column2
Hilton Hotels 31
Marriott Hotels 48
In the example (diagram1) column 2 is blank. That column needs populating but with the corresponding number for each hotel group. There are 30 different hotel gorups each with their own number reference
assume you have a list of the entries you will have in ODD columns and corresponding group number in a separate location such as in columns Y and Z
Select column A1:N20 (assume you data is in A, C, . . . , K, M in rows 1:20
hit F5 to show the goto dialog. Select special and then choose Blank Cells
this should select all the cells that need to be filled with B1 as the activecell. Go to the formula bar and enter a formula like
and end with Ctrl+Enter rather than just enter
now select Columns A:M and do a copy, then pastevalues.
That assumes you have the table of hotels and group numbers. in another location (could be on another sheet, say sheet2 in columns A:B so the formula would be
But using the find and replace built in functionality will not do what you want. The other alternative I can think of is to use a macro.
Adjust my description based on my stated assumptions with your actual situation.