How can I achieve this through a formula?

[Excel Advanced Learner]

I have two columns in Workbook1 - "Issues" and "Regions":

Issues	Regions
4455	Atlantic
2555	Central
7777	Midwestern
3333	Central
4444	Central
6333	Midwestern

<tbody>
</tbody>

I like to list all Issues according to Regions with their corresponding Issues in second Workbook or Excel File such as below:

Regions	Issues
Atlantic	4455
Central	2555, 3333, and 4444
Midwestern	7777 and 6333

<tbody>
</tbody>

Could you show me a formula to do this and how? Thanks.

 

[ParamRay]

.
.

Place the following macro into a standard code module in your workbook. It will create a new sheet containing the desired output.

(Make sure to change indicated line indicated first.)

Sub ConvertData()

    'For original sheet:
    Dim sht1 As Worksheet
    Dim cnt1 As Long
    Dim rng As Range
    
    'For new sheet:
    Dim sht2 As Worksheet
    Dim cnt2 As Long
    
    'For regions & issues:
    Dim reg1 As Range
    Dim reg2 As Range
    Dim iss As String
    
    'Set sht1 as original sheet
    'and sht2 as new sheet...
    
    With ThisWorkbook
        Set sht1 = .Worksheets("Sheet1")    'change as necessary
        Set sht2 = .Worksheets.Add
    End With
    
    'Count rows in original sheet and
    'set range containing regions...
    
    With sht1
        cnt1 = .Range("B" & .Rows.Count).End(xlUp).Row
        Set rng = .Range("B2:B" & cnt1)
    End With
    
    'Insert column headings
    'into new sheet...
    
    With sht2
        .Range("A1").Value = "Regions"
        .Range("B1").Value = "Issues"
    End With
    
    'Loop through regions and concatenate
    'issues when a match is found...
    
    cnt2 = 1
    For Each reg1 In rng
        cnt2 = cnt2 + 1
        iss = vbNullString
        For Each reg2 In rng
            If reg2.Value = reg1.Value Then
                Select Case Len(iss)
                    Case Is = 0: iss = reg2.Offset(0, -1).Value
                    Case Else: iss = iss & ", " & reg2.Offset(0, -1).Value
                End Select
            End If
        Next reg2
        
        'Place concatenated data
        'into new sheet...
        
        With sht2
            .Range("A" & cnt2).Value = reg1.Value
            With .Range("B" & cnt2)
                .NumberFormat = "@"
                .Value = iss
            End With
        End With
    Next reg1
    
    'Remove duplicate entries
    'from new sheet...
    
    sht2.Range("A1").CurrentRegion.RemoveDuplicates _
        Columns:=1, _
        Header:=xlYes
        
    'Notification that
    'macro is finished...
    
    MsgBox _
        Prompt:="Finished.", _
        Buttons:=vbInformation, _
        Title:="Success"

End Sub