Can anyone explain how I can achieve the following in Excel, preferably in a formula without using VBA:

Assume there is one Excel workbook, with 2 worksheets.

Worksheet1 – properties

Assume two columns, A and B

A B
Id Files
1
2
3
4
Etc
Etc


Worksheet 2 – property files

Assume two columns, A and B
A B
Id Filename
1 123.jpg
2 abc.jpg
2 def.jpg
2 ghi.jpg
3 aaa.jpg
3 bbb.jpg
4 ccc.jpg
Etc
Etc

The idea is that the ‘Id’ is the id of a property, and worksheet 2 holds all the filenames associated with a particular property on multiple rows. So, for example, property 2 has 3 files associated with it (abc.jpg, def.jpg and ghi.jpg).

What I want to achieve, is populate the ‘Files’ cell on worksheet 1 (properties) with the following result:

A B
Id Files
1 123.jpg
2 abc.jpg¬def.jpg¬ghi.jpg
3 aaa.jpg¬bbb.jpg
4 ccc.jpg
Etc
Etc

There can be a variable number of rows in worksheet 2 containing filenames for a particular property. I want to concatenate the filename values, with a ¬ delimiter, only on records that match on id, but the concatenation is within a single cell (ie the Files cell).

So a formula is required that I can copy down the ‘Files’ column, to automatically set the concatenated filenames on worksheet 1.