Combining Data from two reports

[emcgaffin]

Every week I pull a report from a CRM (Report 1) and then a separate report from a program that records our internet activity (report 2).

Report 1: Shows a customers name, stock number, deal stage, date created and date of last engagement.


[TABLE="width: 500"]
<tbody>[TR]
[TD]Customer[/TD]
[TD]Stock #[/TD]
[TD]Stage[/TD]
[TD]Created[/TD]
[TD]Engaged[/TD]
[/TR]
[TR]
[TD]John S[/TD]
[TD]456789[/TD]
[TD]Visit[/TD]
[TD]9/1/17[/TD]
[TD]9/5/17[/TD]
[/TR]
[TR]
[TD]Dwight R[/TD]
[TD]TMK425[/TD]
[TD]Proposal[/TD]
[TD]9/2/17[/TD]
[TD]9/4/17[/TD]
[/TR]
</tbody>[/TABLE]


Report 2: Shows the stock number, Make, Model, VDP Views, avg VDP views per day

[TABLE="width: 500"]
<tbody>[TR]
[TD]Stock #[/TD]
[TD]Make[/TD]
[TD]Model[/TD]
[TD]VDP Views[/TD]
[TD]Avg VDP Per Day[/TD]
[/TR]
[TR]
[TD]456789[/TD]
[TD]BMW[/TD]
[TD]3[/TD]
[TD]3[/TD]
[TD]1.50[/TD]
[/TR]
[TR]
[TD]TMK425[/TD]
[TD]Chevy[/TD]
[TD]Impala[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
</tbody>[/TABLE]


End Goal: Combine the two reports so that I can sort the data by using a simple pivot table. Is there a way for me to look at the column containing the stock number in report 1 (column B) and if there is a match (there will be multiple matches for the same stock number) I would like data in columns B,C,D,E from report 2 to be added to columns F,G,H,I in Report 1?

[TABLE="width: 500"]
<tbody>[TR]
[TD]Customer[/TD]
[TD]Stock #[/TD]
[TD]Stage[/TD]
[TD]Created[/TD]
[TD]Engaged[/TD]
[TD]Make[/TD]
[TD]Model[/TD]
[TD]VDP Views[/TD]
[TD]AVG VDP Per Day[/TD]
[/TR]
[TR]
[TD]John S[/TD]
[TD]456789[/TD]
[TD]Visit[/TD]
[TD]9/1/17[/TD]
[TD]9/5/17[/TD]
[TD]BMW[/TD]
[TD]3[/TD]
[TD]3[/TD]
[TD]1.5[/TD]
[/TR]
[TR]
[TD]Dwight R[/TD]
[TD]TMK425[/TD]
[TD]Proposal[/TD]
[TD]9/2/17[/TD]
[TD]9/4/17[/TD]
[TD]Chevy[/TD]
[TD]Impala[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
</tbody>[/TABLE]

Thank you for the help. Please let me know if this is unclear

 

[JLGWhiz]

Are the reports in separate workbooks (files)? If so, what are the workbook names, with file extensions? What is the worksheet name for each report?

 

[emcgaffin]

The reports are in separate workbooks. I usually just cut and paste one into the other. Report 1 is called CRM Customers.xls and report 2 is called VDPs.xls.

 

[Joe4]

All things equal, if you have the resources available to you, this task is pretty easy to do in Microsoft Access. What you are describing is a relational database structure, and that is what Access was built for.

 

[JLGWhiz]

This is untested but what it should do is walk down column b of your customer workbook and look for each stock number in the VDP workbook. If found, it will copy the last four columns of VDP to Customer beginning in column F of the corresponding row for the stock number.

Sub t()
Dim wb1 As Workbook, wb2 As Workbook, sh1 As Worksheet, sh2 As Worksheet, fn As Range
Set wb1 = Workbooks("CRM Customers.xls")
Set wb2 = Workbooks("VDPs.xls")
Set sh1 = wb1.Sheets(1)
Set sh2 = wb2.Sheets(1)
    For Each c In sh1.Range("B2", sh1.Cells(Rows.Count, 2).End(xlUp))
        If c <> "" Then
            Set fn = sh2.Range("A2", sh2.Cells(Rows.Count, 1).End(xlUp)).Find(c.Value, , xlValues, xlWhole)
                If Not fn Is Nothing Then
                    fn.Offset(, 1).Resize(1, 4).Copy c.Offset(, 4)
                End If
        End If
    Next
End Sub

You stated that the stock number may occur more than once but did not specify in which sheet, of if in both, so that might need some attention.
In the mean time, you might want to pursue the suggestion by Joe4 to switch to Access.

 

[emcgaffin]

Hi Joe4, I would love Access. The company I work for now doesn't have a need yet but I'm working on that haha!

 

[emcgaffin]

Hi JLGWhiz!!! This is amazing. That is exactly what I needed. I also realized that I need to spend a considerable amount of time learning VBA! Have a great day!

 

[JLGWhiz]

Hi JLGWhiz!!! This is amazing. That is exactly what I needed. I also realized that I need to spend a considerable amount of time learning VBA! Have a great day!

Glad you could use it. Been having a little hurricane problem here, just got back on line.
Regards, JLG