Formula Across 2 Sheets

[SunRa67]

Hi there, this is my first post on this forum so sorry if i misunderstood any rules !

I am looking for help with a formula that will act between two sheets. The Formula will be on sheet two and has to:

- Add letter 'A' to 'ID'
- Display comments of 'ID' on sheet 1 beside same 'ID' on sheet 2
- Must only do this for 'flagged' 'IDs'

Sheet 3 on the attached file shows my expected results when this formula is working. Here is the file:

www.dropbox.com/scl/fi/r5yj9usn997o76ow6qm5s/Help.xlsx?dl=0&rlkey=a1qwtm8we2twctnfvs9qoefyh

sheet 1:

sheet2:

Sheet3 (result):

Thank you in advance to anyone who is kind enough to help

Jack

 

[maabadi]

Try this:

Sub GetUniques()
Dim d As Object, c As Variant, i As Long, lr As Long, ws As Worksheet, lr2 As Long, ws2 As Worksheet
Dim L As Long, j As Long, E As String, F As String, G As String, H As String, k As Long, M As Long, N As Long
Set ws = Sheets("Sheet1")
Set ws2 = Sheets("Sheet2")
Set d = CreateObject("Scripting.Dictionary")
lr = ws.Cells(Rows.Count, 1).End(xlUp).Row
c = ws.Range("A2:A" & lr)
For i = 1 To UBound(c, 1)
  d(c(i, 1)) = 1
Next i
ws2.Range("A2").Resize(d.Count).Value = Application.Transpose(d.keys)
lr2 = ws2.Cells(Rows.Count, 1).End(xlUp).Row
N = 2
For i = 2 To lr2
F = ""
H = ""
k = 0
L = Application.WorksheetFunction.CountIfs(ws.Range("A2:A" & lr), ws2.Range("A" & i))
M = N + L - 1
For j = N To M
If ws.Range("B" & j) = "Flagged" Then
E = ws.Range("C" & j)
If F = "" Then
F = E
Else
F = F & ", " & E
End If
ElseIf ws.Range("B" & j) = "X" Then
G = "'" & ws.Range("C" & j) & "'"
If H = "" Then
H = G
k = k + 1
Else
H = H & " and " & G
k = k + 1
End If
End If
Next j
ws2.Range("B" & i) = F
N = j
If k = 1 Then
ws2.Range("C" & i) ="Note: '" & H & " isn't included in comments for " & ws2.Range("A" & i)
Else
ws2.Range("C" & i) = "Note: '" & H & " aren't included in comments for " & ws2.Range("A" & i)
End If
Next i
ws2.Range("A2").Resize(d.Count).Value = ws2.Range("A2").Resize(d.Count).Value & "A"
End Sub

 

[Fluff]

Hi & welcome to MrExcel.
How about

=TEXTJOIN(", ",,IF((Sheet1!$A$2:$A$10&"A"=A2)*(Sheet1!$B$2:$B$10="Flagged"),Sheet1!$C$2:$C$10,""))