# How can I achieve this through a formula?

##### New Member
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.

### Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
.
.

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.)

Code:
``````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
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

'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, _

'macro is finished...

MsgBox _
Prompt:="Finished.", _
Buttons:=vbInformation, _
Title:="Success"

End Sub``````

Replies
2
Views
1K
Replies
4
Views
475
Replies
6
Views
3K
Replies
8
Views
2K
Replies
1
Views
460

1,219,672
Messages
6,149,617
Members
450,904
Latest member
Gracifer

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back