How to consolidate records based on a key, and create semicolon delimited list from ID fields

[tynachua]

<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>Hi,

Would love some help, will buy first automation that works lunch!

I am trying to go from List A like this:
<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>[TABLE="width: 0"]
<colgroup><col style="width: 157px"><col width="148"><col width="100"><col width="144"></colgroup><tbody>[TR]
[TD]Name[/TD]
[TD]ID[/TD]
[TD]Type[/TD]
[TD]Key[/TD]
[/TR]
[TR]
[TD]Test person 1[/TD]
[TD]980234980393A1[/TD]
[TD]Leader[/TD]
[TD]AMERRegion1SR1ALL[/TD]
[/TR]
[TR]
[TD]Test person 2[/TD]
[TD]980234980393A2[/TD]
[TD]Leader[/TD]
[TD]AMERRegion1SR1ALL[/TD]
[/TR]
[TR]
[TD]Test person 3[/TD]
[TD]980234980393A3[/TD]
[TD]Leader[/TD]
[TD]AMERRegion1SR1ALL[/TD]
[/TR]
[TR]
[TD]Test person 4[/TD]
[TD]980234980393A4[/TD]
[TD]Leader[/TD]
[TD]LACARegion4SR4[/TD]
[/TR]
[TR]
[TD]Test person 5[/TD]
[TD]980234980393A5[/TD]
[TD]Leader[/TD]
[TD]LACARegion4SR4[/TD]
[/TR]
[TR]
[TD]Test person 6[/TD]
[TD]980234980393A6[/TD]
[TD]Leader[/TD]
[TD]LACARegion4SR4[/TD]
[/TR]
[TR]
[TD]Test person 7[/TD]
[TD]980234980393A7[/TD]
[TD]Leader[/TD]
[TD]LACARegion4SR4[/TD]
[/TR]
</tbody>[/TABLE]

to List B like this:

<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>[TABLE="width: 0"]
<colgroup><col style="width: 157px"><col width="148"><col width="100"></colgroup><tbody>[TR]
[TD]Key[/TD]
[TD]IDs[/TD]
[TD]Names[/TD]
[/TR]
[TR]
[TD]AMERRegion1SR1ALL[/TD]
[TD]980234980393A1;980234980393A2;980234980393A3[/TD]
[TD]Test person 1;Test person 2;Test person 3[/TD]
[/TR]
[TR]
[TD]LACARegion4SR4[/TD]
[TD]980234980393A4;980234980393A5;980234980393A6;980234980393A7[/TD]
[TD]Test person 4;Test person 5;Test person 6;Test person 7[/TD]
[/TR]
</tbody>[/TABLE]

While I can concat ID's manually, I want functionality where when I add a new row in List A, the IDs and names from this new row will be appended to the existing semi-colon delimited list, where the key matches. I'm also on a Mac which puts some additional limits to what I can do i.e. no VBA functionality it seems.

Thank you!

[TABLE="width: 0"]
<colgroup><col style="width: 157px"><col width="148"><col width="100"><col width="100"><col width="100"><col width="100"><col width="100"><col width="100"><col width="144"></colgroup><tbody></tbody>[/TABLE]

 

[Takae]

Assuming columnA to columnD of Sheet1 has data which will be added data manualy.

Sheet Module of Sheet1.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim ws2 As Worksheet
Dim FC As Range
Set ws2 = Sheets("Sheet2")
With ws2
    If Intersect(Target, Range("A:D")) Is Nothing Then Exit Sub
    If WorksheetFunction.CountA(Range(cells(Target.row, 1), cells(Target.row, 4))) <> 4 Then Exit Sub
    
    Set FC = .Range("A:A").Find(What:=cells(Target.row, 4))
    If FC Is Nothing Then
        LR = .cells(Rows.count, 1).End(xlUp).row + 1
        .cells(LR, 1).Value = cells(Target.row, 4).Value
        .cells(LR, 2).Value = cells(Target.row, 2).Value
        .cells(LR, 3).Value = cells(Target.row, 1).Value
    Else
        .cells(FC.row, 2).Value = .cells(FC.row, 2).Value & ";" & cells(Target.row, 2).Value
        .cells(FC.row, 3).Value = .cells(FC.row, 3).Value & ";" & cells(Target.row, 1).Value
    End If
End With
End Sub

If ColumnA to ColumnD of shet1 of each data has blank, it does not work.
Hope this helps.

 

[Mike Szczesny]

Can you use this? Assume simple example has ID in A1, A1:A8 has ID numbers. Assume Key is in B1. and B1:B8 has key letters. Assume Criteria cell DI has A. Place formula in E1. In addition to a concatenation and transposing, I add a substitute part to remove "FALSE". When you create your full formula, you need to highlight the transpose(part of total formula all the way to the 2nd ) after " ; " with your mouse. Hit F9. Remove both { and }. Hit enter
The formula is =substitute(concatenate(transpose(if(b2:b8=d1,a2:a8)&" ; "))),"FALSE","")

[TABLE="width: 345"]
<colgroup><col span="3"><col><col></colgroup><tbody>[TR]
[TD]ID[/TD]
[TD]Key[/TD]
[TD][/TD]
[TD]A[/TD]
[TD]1 ; 2 ; 3 ; 4 ; [/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]A[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]A[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]A[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]A[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]B[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]B[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]b[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]