ow to compare data in Excel 2010

[Dawud Beale]

Hi everyone.
I have some data I need to compare in Excel 2010.
It looks something like this:
1 2 1 2 1 2
1 2 1 5 1 2
1 2 1 2 1 2
1 8 1 2 9 2
1 2 1 2 1 2
1 2 4 2 1 2
1 2 1 2 1 2
and it needs to be compared with:
1 2
1 2
1 2
1 2
1 2
1 2
1 2
These same two columns should be used to compare all 6 columns in the first piece of data. The 1 columns should be compared and the 2 columns should be compared.
As an output I need a list of all cells that don't match what they are being compared with.
Any suggestions on how I might achieve this?

 

[VoG]

Cross posted How to compare data in Excel 2010

Please read Excelguru Help Site - A message to forum cross posters

 

[hiker95]

Dawud Beale,

We do not know what cells, rows, columns, your two tables are in.

Sample raw data:

Excel 2007
	A	B	C	D	E	F	G	H	I	J	K
1	1	2	1	2	1	2			1	2
2	1	2	1	5	1	2			1	2
3	1	2	1	2	1	2			1	2
4	1	8	1	2	9	2			1	2
5	1	2	1	2	1	2			1	2
6	1	2	4	2	1	2			1	2
7	1	2	1	2	1	2			1	2
8
Sheet1

After the macro:

Excel 2007
	A	B	C	D	E	F	G	H	I	J	K
1	1	2	1	2	1	2			1	2	A1:B1,C1:D1,E1:F1
2	1	2	1	5	1	2			1	2	A2:B2,E2:F2
3	1	2	1	2	1	2			1	2	A3:B3,C3:D3,E3:F3
4	1	8	1	2	9	2			1	2	C4:D4
5	1	2	1	2	1	2			1	2	A5:B5,C5:D5,E5:F5
6	1	2	4	2	1	2			1	2	A6:B6,E6:F6
7	1	2	1	2	1	2			1	2	A7:B7,C7:D7,E7:F7
8
Sheet1

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

Where to put the macro code:
1. Open a copy of your workbook in which to store the macro code.
2. Open the Visual Basic Editor (Alt+F11).
3. Insert a standard module (Insert, Module).
4. Copy the macro code, and, paste the macro code into the module on the right where the cursor is flashing.
5. Return to Excel (Alt+Q).
6. Save your copy of the workbook.

How to use the macro code:
1. Display the Macro dialog box (Alt+F8).
2. Click and/or select the macro.
3. Click and/or select "Run".

Option Explicit
Sub CompareData()
' hiker95, 09/11/2013
' http://www.mrexcel.com/forum/excel-questions/725858-ow-compare-data-excel-2010-a.html
Dim a As Variant, o As Variant
Dim i As Long, c As Long, h As String
Columns("K:K").ClearContents
a = Cells(1, 1).CurrentRegion
o = Cells(1, 9).CurrentRegion.Resize(, 3)
For i = 1 To UBound(o, 1)
  h = ""
  For c = 1 To UBound(a, 2) Step 2
    If o(i, 1) = a(i, c) And o(i, 2) = a(i, c + 1) Then
      h = h & Range(Cells(i, c), Cells(i, c + 1)).Address & ","
    End If
  Next c
  If Right(h, 1) = "," Then h = Left(h, Len(h) - 1)
  o(i, 3) = Replace(h, "$", "")
Next i
Cells(1, 9).Resize(UBound(o, 1), UBound(o, 2)) = o
Columns("K:K").AutoFit
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 CompareData macro.