Returning Merged Cell Value

[UM4R91]

I'm looking for a formula that will help me return the merged cell value for every question that is next to a particular merged cell. See image below. I have huge sets of questions and areas sheets that I get sent. And just looking to use formula to help me arrange the data the way I need

Thanks

 

[DanteAmor]

Hi and welcome to MrExcel:

Perform the following steps:

1. Select all the cells in column A that you are going to process:

2. Select Unmerge cells

3. With the cells still selected, press F5 key

4. Click the Special... button in the 'Go To' dialog. Then select the Blanks option in the 'Go To Special' dialog and click OK

Now, only the blank cells are selected:

5. Next, press the equals sign (=) key, then press the 'up arrow' key once. This will create a formula reference to the cell immediately above the Active Cell:

​

6. Now hold down the CTRL key and press Enter. This will fill each of the selected (blank) cells with a formula:

7. Finally select all the cells in column A, copy and paste as values.

 

[Peter_SSs]

just looking to use formula to help me arrange the data the way I need

Then could it be this that you want?

BTW, I suggest that you update your forum profile (click your user name at the top right of the forum, then ‘Account details’) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

24 01 13.xlsm
	A	B	C	D	E
1	Current			Wanted
2	Area	Questions		Area	Questions
3	Personal	Your name?		Personal	Your name?
4		Your age?		Personal	Your age?
5	Preferences	Your fav colour?		Preferences	Your fav colour?
6		Your fav food?		Preferences	Your fav food?
From Merged

Cell Formulas
Range	Formula
D2:D6	D2	=LOOKUP("zzz",A$2:A2)
E2:E6	E2	=B2