Help with function to organise some data

Hello Snowtoad,

Firstly list your all unique locations in Sheet2. Select the Site column, Go to Advanced Filter >> Copy to other location, click Unique Records Only.

Now insert this VBA code (I am not an owner of this code, got it through search)

Function CONCATIF(ByVal compareRange As Range, ByVal xCriteria As Variant, Optional ByVal stringsRange As Range, _
            Optional Delimiter As String, Optional NoDuplicates As Boolean) As String
    ' code base by Mike Rickson, MrExcel MVP
    ' used as exactly like SUMIF() with two additional parameters
    ' of delimiter and "no duplicates" as TRUE/FALSE if concatenated values
    ' might include duplicates  ex. =ConcatIf($A$1:$A$10,C1,$B$1:$B$10,", ",True)

Dim i As Long, j As Long

With compareRange.Parent
    Set compareRange = Application.Intersect(compareRange, Range(.UsedRange, .Range("a1")))
End With

If compareRange Is Nothing Then Exit Function
If stringsRange Is Nothing Then Set stringsRange = compareRange
Set stringsRange = compareRange.Offset(stringsRange.Row - compareRange.Row, _
                                    stringsRange.Column - compareRange.Column)
    
    For i = 1 To compareRange.Rows.Count
        For j = 1 To compareRange.Columns.Count
            If (Application.CountIf(compareRange.Cells(i, j), xCriteria) = 1) Then
                If InStr(CONCATIF, Delimiter & CStr(stringsRange.Cells(i, j))) <> 0 Imp Not (NoDuplicates) Then
                    CONCATIF = CONCATIF & Delimiter & CStr(stringsRange.Cells(i, j))
                End If
            End If
        Next j
    Next i
    CONCATIF = Mid(CONCATIF, Len(Delimiter) + 1)
End Function

then use Sheet2!B2, copy down,

=CONCATIF(Sheet1!$B$2:$B$1439,$A2,Sheet1!$A$2:$A$1439,", ",TRUE)

C2, copy down.

=INDEX(Sheet1!C:C,MATCH(A2,Sheet1!B:B,0))

See the attached.

Awesome, thanks, that seems to work! Thank you both so much for your efforts! This will be really useful

EDIT:
Just one more thing...
Say if there were two studies at one site, but both by the same Author and year.
Instead of just displaying the single Author-year e.g.


2 Studies Koram-2008

Would it be possible for it to realize when the author/year is duplicated and make it like:


2 Studies (2)Koram-2008

and


3 Studies Batty-1998, de Vries-2000


to


3 Studies (2)Batty-1998, de Vries-2000

Cheers!