IMPOSSIBLE: Search and retrieve to new sheet based on adjacent merged cell value

[CDxel2011]

Hi,

I may be asking the impossible or this may have been asked already if so apologies in advance.

I am trying to create a macro that will search for all unique values within a sheet and list them on another sheet, by identifying the values based on the adjacent merged field.

Additional I am looking for a macro coded in Excel 2003 and 2010.

I'd insert an image as an example but I don't have photo bucket or anything similar.

 

[onlyadrafter]

Hello,

can you not just type in an example of some of your data, and the results you expect?

 

[CDxel2011]

Hello,

can you not just type in an example of some of your data, and the results you expect?

I shall have a go:

	A	B	C	D	E	F	G	H
1	Mall
2		Fruit Stall	Banana
3			Pear
4			Melon
5
6		Bakery	Cake		Fruit Stall	Apple
7			Bread			Lemon
8			Sandwhiches			Cherry
9
10					Bakery	Roll
11						Brown Bread
12						Cream Horn

<tbody>
</tbody>

The above is rough idea the Fruit Stall and Bakery are in merged cells next to the items so all three cells.

 

[onlyadrafter]

Hello,

so you want to copy over Banana, Pear and Melon from the first fruit stall.

If any other Fruit Stall has Banana, Pear or Melon, then you wouldn't want to copy this over.

Do you want to copy over Fruit Stall or Bakery with the item too?

 

[CDxel2011]

Hello,

so you want to copy over Banana, Pear and Melon from the first fruit stall.

If any other Fruit Stall has Banana, Pear or Melon, then you wouldn't want to copy this over.

Do you want to copy over Fruit Stall or Bakery with the item too?

Ideal all the items listed next to Fruit Stall whether in the sheet it occurs then list on another sheet.

Thanks

 

[CDxel2011]

I am guessing there isn't an easy answer to this?