Oct 02 2019 03:24 PM - edited Oct 02 2019 03:27 PM
Oct 02 2019 10:25 PM
It is hard to suggest any solution without looking at the layout of your data in the reports.
Why not upload the sample file and mock up the desired output in another sheet manually to show us the end result you are trying to achieve?
Oct 03 2019 02:43 AM - edited Oct 03 2019 02:44 AM
Thanks, please see below.
The membership report & service report are both in separate workbooks.
Oct 03 2019 03:19 AM
Instead of posting the image of the data, please upload the Excel Workbooks.
Click on the paper clip icon to open the upload window and upload the files. Refer to the following image.
Btw are you comfortable with the Macro Solution? And what Excel version you are using, Excel 2010?
Oct 03 2019 03:59 AM
Thanks I have attached the sample files as requested.
I am have some basic knowledge of Macro but i have never used this. I am currently using excel 2010.
Many thanks
Kerrie
Oct 03 2019 04:52 AM
SolutionPlease find the attached with the code on Module1 and a button called "Generate Combined Membership Report" on Combined Report Sheet to run the code.
The code will open the file picker dialog box for you to pick the Membership Report first and then Service Report, once you pick both the files, the code will generate the combined report on the Combined Report Sheet.
Code on Module1:
Public Sub ExtractMembershipInfo()
Dim wbCombined As Workbook
Dim wbMembership As Workbook
Dim wbService As Workbook
Dim wsCombined As Worksheet
Dim wsMember As Worksheet
Dim wsService As Worksheet
Dim strFilePath As String
Dim x As Variant
Dim y() As Variant
Dim dict As Object
Dim i As Long
Application.ScreenUpdating = False
Set wbCombined = ThisWorkbook
Set wsCombined = wbCombined.Worksheets("Combined Report")
With Application.FileDialog(msoFileDialogFilePicker)
.Title = "Select Membership Report File!"
.AllowMultiSelect = False
.ButtonName = "Open Membership Report"
.Filters.Clear
.Filters.Add "Excel Files", "*.xlsx"
If .Show = -1 Then
strFilePath = .SelectedItems(1)
Else
MsgBox "You didn't select any Membership Report.", vbExclamation
End
End If
End With
Set wbMembership = Workbooks.Open(strFilePath)
Set wsMember = wbMembership.Worksheets(1)
With Application.FileDialog(msoFileDialogFilePicker)
.Title = "Select Service Report File!"
.AllowMultiSelect = False
.ButtonName = "Open Service Report"
.Filters.Clear
.Filters.Add "Excel Files", "*.xlsx"
If .Show = -1 Then
strFilePath = .SelectedItems(1)
Else
MsgBox "You didn't select any Service Report.", vbExclamation
End
End If
End With
Set wbService = Workbooks.Open(strFilePath)
Set wsService = wbService.Worksheets(1)
Set dict = CreateObject("Scripting.Dictionary")
x = wsMember.Range("A1").CurrentRegion.Value
For i = 2 To UBound(x, 1)
dict.Item(x(i, 1)) = x(i, 2) & "_" & x(i, 3)
Next i
wbMembership.Close False
wsCombined.Cells.Clear
wsService.Range("A1").CurrentRegion.Copy wsCombined.Range("A1")
wbService.Close False
x = wsCombined.Range("A1").CurrentRegion.Value
ReDim y(1 To UBound(x, 1), 1 To 2)
wsCombined.Cells(1, UBound(x, 2) + 1) = "Membership Type"
wsCombined.Cells(1, UBound(x, 2) + 2) = "Membership Status"
For i = 2 To UBound(x, 1)
If dict.exists(x(i, 1)) Then
y(i - 1, 1) = Split(dict(x(i, 1)), "_")(0)
y(i - 1, 2) = Split(dict.Item(x(i, 1)), "_")(1)
End If
Next i
wsCombined.Cells(2, UBound(x, 2) + 1).Resize(UBound(x, 1), 2).Value = y
With wsCombined.Range("A1").CurrentRegion
.Rows(1).Font.Bold = True
.Rows(1).Font.Size = 13
.Columns.AutoFit
.Borders.Color = vbBlack
End With
Application.ScreenUpdating = True
End Sub
Oct 03 2019 05:41 AM
OMG this is amazing, it worked on the sample sheets but not on the original reports, I assume this is because the samples did not include all data as per the original report.
I did however match the fields and it seemed worked... so I have just sort the list ascending by name and copy and paste on to the original report.
Thank you so much for your help, I really appreciate it.
Kindest regards
K
Oct 03 2019 06:00 AM - edited Oct 03 2019 06:01 AM
You're welcome! Glad it worked as desired and you were able to tweak it as per your need.
Please take a minute to select the post with the proposed solution as a Best Response/Answer to mark your question as Solved.
Oct 03 2019 04:52 AM
SolutionPlease find the attached with the code on Module1 and a button called "Generate Combined Membership Report" on Combined Report Sheet to run the code.
The code will open the file picker dialog box for you to pick the Membership Report first and then Service Report, once you pick both the files, the code will generate the combined report on the Combined Report Sheet.
Code on Module1:
Public Sub ExtractMembershipInfo()
Dim wbCombined As Workbook
Dim wbMembership As Workbook
Dim wbService As Workbook
Dim wsCombined As Worksheet
Dim wsMember As Worksheet
Dim wsService As Worksheet
Dim strFilePath As String
Dim x As Variant
Dim y() As Variant
Dim dict As Object
Dim i As Long
Application.ScreenUpdating = False
Set wbCombined = ThisWorkbook
Set wsCombined = wbCombined.Worksheets("Combined Report")
With Application.FileDialog(msoFileDialogFilePicker)
.Title = "Select Membership Report File!"
.AllowMultiSelect = False
.ButtonName = "Open Membership Report"
.Filters.Clear
.Filters.Add "Excel Files", "*.xlsx"
If .Show = -1 Then
strFilePath = .SelectedItems(1)
Else
MsgBox "You didn't select any Membership Report.", vbExclamation
End
End If
End With
Set wbMembership = Workbooks.Open(strFilePath)
Set wsMember = wbMembership.Worksheets(1)
With Application.FileDialog(msoFileDialogFilePicker)
.Title = "Select Service Report File!"
.AllowMultiSelect = False
.ButtonName = "Open Service Report"
.Filters.Clear
.Filters.Add "Excel Files", "*.xlsx"
If .Show = -1 Then
strFilePath = .SelectedItems(1)
Else
MsgBox "You didn't select any Service Report.", vbExclamation
End
End If
End With
Set wbService = Workbooks.Open(strFilePath)
Set wsService = wbService.Worksheets(1)
Set dict = CreateObject("Scripting.Dictionary")
x = wsMember.Range("A1").CurrentRegion.Value
For i = 2 To UBound(x, 1)
dict.Item(x(i, 1)) = x(i, 2) & "_" & x(i, 3)
Next i
wbMembership.Close False
wsCombined.Cells.Clear
wsService.Range("A1").CurrentRegion.Copy wsCombined.Range("A1")
wbService.Close False
x = wsCombined.Range("A1").CurrentRegion.Value
ReDim y(1 To UBound(x, 1), 1 To 2)
wsCombined.Cells(1, UBound(x, 2) + 1) = "Membership Type"
wsCombined.Cells(1, UBound(x, 2) + 2) = "Membership Status"
For i = 2 To UBound(x, 1)
If dict.exists(x(i, 1)) Then
y(i - 1, 1) = Split(dict(x(i, 1)), "_")(0)
y(i - 1, 2) = Split(dict.Item(x(i, 1)), "_")(1)
End If
Next i
wsCombined.Cells(2, UBound(x, 2) + 1).Resize(UBound(x, 1), 2).Value = y
With wsCombined.Range("A1").CurrentRegion
.Rows(1).Font.Bold = True
.Rows(1).Font.Size = 13
.Columns.AutoFit
.Borders.Color = vbBlack
End With
Application.ScreenUpdating = True
End Sub