Comparison of Data between Worksheets Same Workbook

[jumpmanz5]

I'm not an excel power user by any means, so i'm having trouble getting a column in one worksheet to compare against the same column in another worksheet.

I have two different worksheets in the same workbook each sheet contains a different data with the exception of one column. The columns contains a five digit number and between the two columns the cells in the row may or may not be the match and not be in sequential order.

What I would like to preform as an example would be the comparison of Column A in worksheet 1 to Column A (or whatever column the data is in) of worksheet 2. Two options in returning value would be fine although one is preferred.

Example
Worksheet 1.

Rows Column A
Row 1 12345
Row 2 67890
Row 3 09876
Row 4 54321

Worksheet 2

Rows Column A
Row 1 12345 – Matches Worksheet 1 Row 1
Row 2 75984
Row 3 09876
Row 4 67890 – Matches Worksheet 1 Row 2
Row 5 15935
Row 6 09876 – Matches Worksheet 1 Row 3
Row 7 78963
Row 8 67890 – Matches Worksheet 1 Row 2
Row 9 09876 – Matches Worksheet 1 Row 3
Row 10 78963
Row 11 54321 – Matches Worksheet 1 Row 4


Desired Result 1 (Preferred) – Any item in Worksheet 2 Column A that matched a number in Worksheet 1 Column A, the entire record (entire row of data) would be pulled from Worksheet 2 and put into a new worksheet 3.

Worksheet 3 – Would like look this; only the rows that matched a number in worksheet 1 pulled over. When I say entire row I mean columns A through M work of data.

Rows Column A
Row 1 12345 – Matched Worksheet 1 Row 1
Row 4 67890 – Matched Worksheet 1 Row 2
Row 6 09876 – Matched Worksheet 1 Row 3
Row 8 67890 – Matched Worksheet 1 Row 2
Row 9 09876 – Matched Worksheet 1 Row 3
Row 11 54321 – Matched Worksheet 1 Row 4

Desired Result 2 – Any item in Worksheet 2 Column A that matched a number in Worksheet 1 Column A, the number in Worksheet 2 Column A would highlight.

Worksheet 3 – Would like look this

Rows Column A
Row 1 12345 – Number would highlight because of match to Worksheet 1 Row 1
Row 2 75984
Row 3 09876
Row 4 67890 – Number would highlight because of match to Row 2
Row 5 15935
Row 6 09876 – Number would highlight because of match to Row 3
Row 7 78963
Row 8 67890 – Number would highlight because of match to Row 2
Row 9 09876 – Number would highlight because of match to Row 3
Row 10 78963
Row 11 54321 – Number would highlight because of match to Row 4


I don't know if this needs to be a conditional formatting item or VBA, etc. I would greatly apprecaite any help anyone could provided. I'm staring at a wall.

Jump

 

[hiker95]

jumpmanz5,

What version of Excel are you using?

If this was a problem where one needed to use your data, anyone trying to help you would have to enter the data manually. That makes no sense.

Can you post a screenshot of the three worksheets, and, post a screenshot of the worksheet results (manually formatted by you) that you are looking for?

To post your data, you can download and install one of the following two programs:
Excel Jeanie
MrExcel HTML Maker


If you are not able to give us screenshots:
You can upload your workbook to Box Net,
sensitive changed
mark the workbook for sharing
and provide us with a link to your workbook.

 

[jumpmanz5]

I'm using Microsoft 2010.

I'm not concerned that anyone using my data, as the results will be the only information I send to anyone.

Essentially, I have a workbook that 1) contains every contract records (thousands) within my company (Worksheet 1), which can be sorted by business group (BG) (we have eight BG), and 2) a list of every subcontract record within the company(Worksheet 2).

What I'm looking to do is sort data by BG and then take that data (which would be Column A info) and compare against all the subcontract data information. The columns A would be the only column within each worksheet that would match.

I'm at work at the moment, so i'm not able to install any software, but should be able to do it one i get home a little later this evening.

If this helps at all the data would be similar to this.

Columns – Worksheet 1
A | B | C | D | E | F
01985 | ABC Company | $7,000 | 8/12/2013 | 12/31/2013 | Yes
32165 | XYZ Company | $3,000 | 8/11/2013 | 12/31/2013 | Yes
35468 | DFE Company | $8,000 | 8/6/2013 | 12/31/2013 | Yes
15963 | UYX Company | $4,000 | 8/1/2013 | 12/31/2013 | Yes


Columns – Worksheet 2
A | B | C | D | E | F
01985 | 951 Company | $9,000 | 8/5/2013 | 12/31/2013 | Yes
32165 | 753 Company | $2,000 | 8/9/2013 | 12/31/2013 | Yes
35468 | DFE Company | $7,000 | 8/7/2013 | 12/31/2013 | Yes
15963 | 342 Company | $6,000 | 8/3/2013 | 12/31/2013 | Yes

Results to Worksheet 3
A | B | C | D | E | F
35468 | DFE Company | $8,000 | 8/6/2013 | 12/31/2013 | Yes

The results would only be one record, since only the 35468 matched between the two other sheets.

The number in Column A is a project number. All other data between the two sheets are completely different with the exception of that one column a 5 digit numbers. I’m pulling each report from different systems and putting them into the same workbook (different worksheets). Just looking for an easier way to pull out all the records for subcontracts info that matches that project number from the contract info.

I hope that adds a little clarification.

 

[hiker95]

jumpmanz5,

It would appear that you are looking for matches in Sheet1 columns A&B to Sheet2 columns A&B.

I'm at work at the moment, so i'm not able to install any software, but should be able to do it one i get home a little later this evening.

I would prefer not to have to split your dataset, so, I will wait until later.

 

[jumpmanz5]

Hiker95

Here are is a sample of the worksheet data.

Worksheet 1 - The highlight show the vehicle record that match to records within Sheet 2.

Excel 2010
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Purchase Order	PO Release Number	PO Status Type	Buyer	PO Total Amount	Vendor Name	Start Date	End Date	Vehicle	Prime Contract No.	Program Name	PM	Vendor Sales Order
2	APG-123597	0	O	Smith	$2,860,900.00	Subcontractor 1	9/4/2012	1/17/2014	05872	Prime Contract 11	Program 55	PM 1
3	APG-123599	0	O	Smith	$644,800.00	Subcontractor 2	9/11/2012	1/17/2014	09169	Prime Contract 12	Program 56	PM 2	1XXXXXC
4	APG-123601	0	O	Smith	$330,000.00	Subcontractor 3	9/28/2012	1/17/2014	05577	Prime Contract 13	Program 57	PM 3	1XXXXXC
5	APG-123601	0	O	Smith	$330,000.00	Subcontractor 4	9/28/2012	1/17/2014	INDIR	Prime Contract 14	Program 58	PM 4	1XXXXXC
6	APG-123602	0	O	Smith	$2,412,840.00	Subcontractor 5	9/11/2012	1/17/2014	09169	Prime Contract 15	Program 59	PM 5	1XXXXXC*
7	APG-125330	0	O	Smith	$382,800.00	Subcontractor 6	9/4/2012	1/17/2014	05577	Prime Contract 16	Program 60	PM 6	1XXXXXC
8	APG-125429	0	O	Smith	$308,625.00	Subcontractor 7	9/10/2012	1/17/2014	09169	Prime Contract 17	Program 61	PM 7	1XXXXXC
Sheet 1

Worksheet 2 - The highlights show the vehicle records that match in Worksheet 1

Excel 2010
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1	Vehicle #	Proj/BPA #	Call Order #	Name	Veh Type	CACI Company	Prime	Prime Contract #	SubK #	Cust Level 1	Vehicle Manager	Project Manager	Contracts Manager	SubK Manager	POP (Start)	POP (End)	Final Opt End Dt	Potential	Ceiling	Funded	Stage
2	04773	0587		Product 1	Contract Type 1	Company 1	MY Company	Contract 1		Customer 1		PM 1	CM 1
3	05411			Product 2	Contract Type 2	Company 2	MY Company	Contract 2		Customer 2		PM 2	CM 2
4	05518			Product 3	Contract Type 3	Company 3	MY Company	Contract 3		Customer 3		PM 3	CM 3
5	05577	4100	4101	Product 4	Contract Type 4	Company 4	MY Company	Contract 4		Customer 4		PM 4	CM 4
6	05577	4500	4502	Product 5	Contract Type 5	Company 5	MY Company	Contract 5		Customer 5		PM 5	CM 5
7	05751			Product 6	Contract Type 6	Company 6	MY Company	Contract 6		Customer 6		PM 6	CM 6
8	05872	0010		Product 7	Contract Type 7	Company 7	MY Company	Contract 7		Customer 7		PM 7	CM 7
Sheet2

Worksheet 3 - Results - This sheet would only show records from Worksheet 1 where the vehicle matched a vehicle number in Worksheet 2.

Excel 2010
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Purchase Order	PO Release Number	PO Status Type	Buyer	PO Total Amount	Vendor Name	Start Date	End Date	Vehicle	Prime Contract No.	Program Name	PM	Vendor Sales Order
2	APG-123597	0	O	Smith	$2,860,900.00	Subcontractor 1	9/4/2012	1/17/2014	05872	Prime Contract 11	Program 55	PM 1
3	APG-123601	0	O	Smith	$330,000.00	Subcontractor 3	9/28/2012	1/17/2014	05577	Prime Contract 13	Program 57	PM 3	1XXXXXC
4	APG-125330	0	O	Smith	$382,800.00	Subcontractor 6	9/4/2012	1/17/2014	05577	Prime Contract 16	Program 60	PM 6	1XXXXXC
Sheet3

I really how this helps clarify what i'm looking to compare and how i would like the results to return.

Please let me know if you need more information.

Thank You

 

[hiker95]

jumpmanz5,

Thanks for the screenshots.

Sample worksheets:

Excel 2007
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Purchase Order	PO Release Number	PO Status Type	Buyer	PO Total Amount	Vendor Name	Start Date	End Date	Vehicle	Prime Contract No.	Program Name	PM	Vendor Sales Order
2	APG-123597	0	O	Smith	$2,860,900.00	Subcontractor 1	9/4/2012	1/17/2014	5872	Prime Contract 11	Program 55	PM 1
3	APG-123599	0	O	Smith	$644,800.00	Subcontractor 2	9/11/2012	1/17/2014	9169	Prime Contract 12	Program 56	PM 2	1XXXXXC
4	APG-123601	0	O	Smith	$330,000.00	Subcontractor 3	9/28/2012	1/17/2014	5577	Prime Contract 13	Program 57	PM 3	1XXXXXC
5	APG-123601	0	O	Smith	$330,000.00	Subcontractor 4	9/28/2012	1/17/2014	INDIR	Prime Contract 14	Program 58	PM 4	1XXXXXC
6	APG-123602	0	O	Smith	$2,412,840.00	Subcontractor 5	9/11/2012	1/17/2014	9169	Prime Contract 15	Program 59	PM 5	1XXXXXC*
7	APG-125330	0	O	Smith	$382,800.00	Subcontractor 6	9/4/2012	1/17/2014	5577	Prime Contract 16	Program 60	PM 6	1XXXXXC
8	APG-125429	0	O	Smith	$308,625.00	Subcontractor 7	9/10/2012	1/17/2014	9169	Prime Contract 17	Program 61	PM 7	1XXXXXC
9
Sheet1

Excel 2007
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Vehicle #	Proj/BPA #	Call Order #	Name	Veh Type	CACI Company	Prime	Prime Contract #	SubK #	Cust Level 1	Vehicle Manager	Project Manager	Contracts Manager
2	4773	587		Product 1	Contract Type 1	Company 1	MY Company	Contract 1		Customer 1		PM 1	CM 1
3	5411			Product 2	Contract Type 2	Company 2	MY Company	Contract 2		Customer 2		PM 2	CM 2
4	5518			Product 3	Contract Type 3	Company 3	MY Company	Contract 3		Customer 3		PM 3	CM 3
5	5577	4100	4101	Product 4	Contract Type 4	Company 4	MY Company	Contract 4		Customer 4		PM 4	CM 4
6	5577	4500	4502	Product 5	Contract Type 5	Company 5	MY Company	Contract 5		Customer 5		PM 5	CM 5
7	5751			Product 6	Contract Type 6	Company 6	MY Company	Contract 6		Customer 6		PM 6	CM 6
8	5872	10		Product 7	Contract Type 7	Company 7	MY Company	Contract 7		Customer 7		PM 7	CM 7
9
Sheet2

The output sheet, Sheet3, rows 2 to the last used row will be cleared first, then the resulting data will be posted:

Excel 2007
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Purchase Order	PO Release Number	PO Status Type	Buyer	PO Total Amount	Vendor Name	Start Date	End Date	Vehicle	Prime Contract No.	Program Name	PM	Vendor Sales Order
2	APG-123597	0	O	Smith	$2,860,900.00	Subcontractor 1	9/4/2012	1/17/2014	5872	Prime Contract 11	Program 55	PM 1
3	APG-123601	0	O	Smith	$330,000.00	Subcontractor 3	9/28/2012	1/17/2014	5577	Prime Contract 13	Program 57	PM 3	1XXXXXC
4	APG-125330	0	O	Smith	$382,800.00	Subcontractor 6	9/4/2012	1/17/2014	5577	Prime Contract 16	Program 60	PM 6	1XXXXXC
5
Sheet3

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code
2. Open your NEW workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Option Explicit
Sub FindMatchingVehicles()
' hiker95, 08/31/2013
' http://www.mrexcel.com/forum/excel-questions/723481-comparison-data-between-worksheets-same-workbook.html
Dim a As Variant, r As Long, lr As Long, n As Long, fr As Long, nr As Long
Application.ScreenUpdating = False
With Sheets("Sheet3")
  lr = .Cells(Rows.Count, 1).End(xlUp).Row
  If lr > 1 Then
    .Range("A2:M" & lr).ClearContents
  End If
End With
With Sheets("Sheet1")
  a = .Cells(1).CurrentRegion
  lr = .Cells(Rows.Count, 1).End(xlUp).Row
  .Range("A2:M" & lr).Sort key1:=.Range("I2"), order1:=2
  For r = 2 To lr
    n = Application.CountIf(.Columns(9), .Cells(r, 9).Value)
    fr = 0
    On Error Resume Next
    fr = Application.Match(.Cells(r, 9), Sheets("Sheet2").Columns(1), 0)
    On Error GoTo 0
    If fr > 0 Then
      nr = Sheets("Sheet3").Range("A" & Rows.Count).End(xlUp).Offset(1).Row
      Sheets("Sheet3").Range("A" & nr & ":M" & nr).Resize(n) = .Range("A" & r & ":M" & r + n - 1).Value
    End If
    r = r + n - 1
  Next r
  .Cells(1).CurrentRegion = a
End With
With Sheets("Sheet3")
  .Range(.Cells(2, 5), .Cells(nr, 5)).NumberFormat = "$#,##0.00_);[Red]($#,##0.00)"
  .Range(.Cells(2, 7), .Cells(nr, 8)).NumberFormat = "m/d/yyyy"
  .Range(.Cells(2, 2), .Cells(nr, 4)).HorizontalAlignment = xlCenter
  .Range(.Cells(2, 6), .Cells(nr, 9)).HorizontalAlignment = xlCenter
  .Columns.AutoFit
  .Activate
End With
Application.ScreenUpdating = True
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the FindMatchingVehicles macro.

 

[jumpmanz5]

Hiker95... thank you so much. I'll try this on Tuesday once i'm in the office and report back on how it worked. I really do appreciate it.

Jump

 

[jumpmanz5]

Hiker 95...

I can't think you enough for putting that together... Worked like a champ.

My only question is in regards to the three sheet that is created by the macro. Is it an easy tweak to the macro if i wanted the sheet created to include more columns. For example: When I ran the macro it in the new sheet it produced info for columns A-M. Would it be possible to extend that so the results would be say A-R. Only reason i'm asking, as I can see using the macro in other spreadsheet with the same data, but pulling quick reports with more info.

Thanks
Jump

 

[hiker95]

jumpmanz5,

I can't think you enough for putting that together... Worked like a champ.

Thanks for the feedback.

You are very welcome. Glad I could help.

Would it be possible to extend that so the results would be say A-R.

Be back in a little while.

 

[hiker95]

jumpmanz5,

Would it be possible to extend that so the results would be say A-R.

Give the following a try:

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Option Explicit
Sub FindMatchingVehiclesV2()
' hiker95, 09/04/2013
' http://www.mrexcel.com/forum/excel-questions/723481-comparison-data-between-worksheets-same-workbook.html
Dim a As Variant, r As Long, lr As Long, n As Long, fr As Long, nr As Long
Application.ScreenUpdating = False
With Sheets("Sheet3")
  lr = .Cells(Rows.Count, 1).End(xlUp).Row
  If lr > 1 Then
    .Range("A2:R" & lr).ClearContents
  End If
End With
With Sheets("Sheet1")
  a = .Cells(1).CurrentRegion
  lr = .Cells(Rows.Count, 1).End(xlUp).Row
  .Range("A2:R" & lr).Sort key1:=.Range("I2"), order1:=2
  For r = 2 To lr
    n = Application.CountIf(.Columns(9), .Cells(r, 9).Value)
    fr = 0
    On Error Resume Next
    fr = Application.Match(.Cells(r, 9), Sheets("Sheet2").Columns(1), 0)
    On Error GoTo 0
    If fr > 0 Then
      nr = Sheets("Sheet3").Range("A" & Rows.Count).End(xlUp).Offset(1).Row
      Sheets("Sheet3").Range("A" & nr & ":R" & nr).Resize(n) = .Range("A" & r & ":R" & r + n - 1).Value
    End If
    r = r + n - 1
  Next r
  .Cells(1).CurrentRegion = a
End With
With Sheets("Sheet3")
  .Range(.Cells(2, 5), .Cells(nr, 5)).NumberFormat = "$#,##0.00_);[Red]($#,##0.00)"
  .Range(.Cells(2, 7), .Cells(nr, 8)).NumberFormat = "m/d/yyyy"
  .Range(.Cells(2, 2), .Cells(nr, 4)).HorizontalAlignment = xlCenter
  .Range(.Cells(2, 6), .Cells(nr, 9)).HorizontalAlignment = xlCenter
  .Columns.AutoFit
  .Activate
End With
Application.ScreenUpdating = True
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the FindMatchingVehiclesV2 macro.