compare data between seperate data ranges

[CYDSRQ]

Hello - I am relatively new to analyzing data in Excel - using Windows 7 and Excel 2010. I have two sets of data and am trying to validate data between them. Data sets have columns titled FirstName, LastName, Ethnicity, Gender. I am trying to compare the Ethnicity and Gender to determine that the data set2 info is correct (matches data set 1). I am familiar with Index: Match, but don't think that applies here? Thanks for any help on this! Karen

 

[hiker95]

CYDSRQ,

Just to clarify - the macro highlighted cells when data in Data Set 2 was not the same as Data Set 1? That's what it appears to be....

I am not sure what is possible- can the cell in data set 2 be updated with correct info from set 1, and highlighted in yellow to show that it has been updated? If not, then highlighting the incorrect information in data set 2 would be helpful.

I missed this part - be back in a little while.

Can you suggest ways to learn about building macros, and/or VBA ?

See the below link for my most up to date list:

Training / Books / Sites as of 1/30/2014
http://www.mrexcel.com/forum/excel-...umber-statement-end-statment.html#post3706277

 

[hiker95]

CYDSRQ,

Sample raw data worksheets:

Excel 2007
	A	B	C	D	E
1	first	last	studentid	gender	ethnicity
2	jane	doe	123456	f	w
3	mike	williams	234567	m	b
4	jose	lobaton	345678	m	h
5	tom	smith	456789	m	w
6
Data Set 1

Excel 2007
	A	B	C	D
1	first	last	gender	ethnicity
2	sandy	oates	f	w
3	jane	doe	h
4	mike	williams	m	w
5
Data Set 2

After the new macro in worksheet Data Set 2:

Excel 2007
	A	B	C	D
1	first	last	gender	ethnicity
2	sandy	oates	f	w
3	jane	doe	f	w
4	mike	williams	m	b
5
Data Set 2

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 CompareDatasetsV2()
' hiker95, 02/25/2014, ME759423
Dim lr1 As Long, lr2 As Long
Dim nrng As Range, c As Range
Application.ScreenUpdating = False
With Sheets("Data Set 1")
  lr1 = .Cells(Rows.Count, 1).End(xlUp).Row
  With Range("A1:E" & lr1)
    .Value = Evaluate("IF(ISTEXT(" & .Address & "),TRIM(" & .Address & "),REPT(" & .Address & ",1))")
  End With
  With .Range("F2:F" & lr1)
    .FormulaR1C1 = "=RC[-5]&RC[-4]"
    .Value = .Value
  End With
End With
With Sheets("Data Set 2")
  lr2 = .Cells(Rows.Count, 1).End(xlUp).Row
  With Range("A1:D" & lr2)
    .Value = Evaluate("IF(ISTEXT(" & .Address & "),TRIM(" & .Address & "),REPT(" & .Address & ",1))")
  End With
  With .Range("E2:E" & lr1)
    .FormulaR1C1 = "=RC[-4]&RC[-3]"
    .Value = .Value
  End With
End With
With Sheets("Data Set 2")
  For Each c In .Range("E2:E" & lr2)
    Set nrng = Sheets("Data Set 1").Range("F2:F" & lr1).Find(c, LookAt:=xlWhole)
    If Not nrng Is Nothing Then
      If .Cells(c.Row, 3) <> Sheets("Data Set 1").Cells(nrng.Row, 4) Then
        .Cells(c.Row, 3) = Sheets("Data Set 1").Cells(nrng.Row, 4)
        .Cells(c.Row, 3).Interior.Color = 65535
      End If
      If .Cells(c.Row, 4) <> Sheets("Data Set 1").Cells(nrng.Row, 5) Then
        .Cells(c.Row, 4) = Sheets("Data Set 1").Cells(nrng.Row, 5)
        .Cells(c.Row, 4).Interior.Color = 65535
      End If
    End If
  Next c
End With
Sheets("Data Set 1").Range("F2:F" & lr1).ClearContents
Sheets("Data Set 2").Range("E2:E" & lr2).ClearContents
With Sheets("Data Set 2")
  .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 CompareDatasetsV2 macro.

 

[CYDSRQ]

Thank you hiker95! That worked very well! I also appreciate the link to the VBA info....

 

[hiker95]

CYDSRQ,

Thanks for the feedback.

You are very welcome. Glad I could help.

And, come back anytime.