Potential combination of LookUp and DeDupe to create multiple values in a single cell

Thanks Mick. I am not sure I explained just right what I am looking for. I have pasted your original code into my macro and my table now looks like:

Sheet 1 in example.

So now I want to look up column A as I did previously, but instead of taking the offset of 1 on "Sheet2" I want to take offset 6 say, and insert the list in a new column C. As per below:

Sheet 2 in attached example

Does that make sense or should we start again?

Thanks in advance for all your help!