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

 

[sijpie]

You are a bit sparce in the infromation in your tables. How are the laid out, etc.

You should be able to get a long with with match & index, matching on name nad then comparing the column with the gender or ethnicity.

 

[hiker95]

CYDSRQ,

Can we have a screenshot of each dataset?

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

Or, when using Internet Explorer, just put borders around your data in Excel and copy those cells into your post.
See reply #2 the BLUE text in the following link:
http://www.mrexcel.com/forum/about-board/444901-how-create-table-like-aladin.html#post2198045


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

 

[CYDSRQ]

hi - thanks for your response. Data looks like this:Data Set 1
first last studentid gender ethnicity
jane doe 123456 f w
mike williams 234567 m b
jose lobaton 345678 m h
tom smith 456789 m w

Data set 2
first last gender ethnicity
sandy oates f w
jane doe h
mike williams m w

 

[CYDSRQ]

Thanks Hiker95 I wondered how to do that! See what I posted above - is that still unclear?

 

[hiker95]

CYDSRQ,

Your text display does not really help. I can not tell what worksheet, cells, rows, columns, your raw data is in.

1. What is the worksheet name?

2. What cell in Data Set 1 contains the title first?

3. What cell in Data set 2 contains the title first?

I am trying to compare the Ethnicity and Gender

4. And, how do you want me to indicate the results?

 

[CYDSRQ]

hi hiker95 - thanks for your help. I am having difficulty with the shortcuts mentioned above - spam blocker, errors in macros etc, so not able to do screenshots.... I will try to clarify here:

Data Set 1
Column A first name
Col B last name
Col C studentID
Col D Gender
Col E ethnicity

Data Set 2
Column A First
Col B Last
Col C Gender
Col D Ethnicity

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.

Thank you!

 

[hiker95]

CYDSRQ,

Are Data Set 1, and, Data Set 2, two separate worksheets?

 

[hiker95]

CYDSRQ,

I assume that Data Set 1, and, Data Set 2, are two separate worksheets.

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 macro in both 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

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 CompareDatasets()
' 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 1")
  For Each c In .Range("F2:F" & lr1)
    Set nrng = Sheets("Data Set 2").Range("E2:E" & lr2).Find(c, LookAt:=xlWhole)
    If Not nrng Is Nothing Then
      If .Cells(c.Row, 4) <> Sheets("Data Set 2").Cells(nrng.Row, 3) Then
        .Cells(c.Row, 4).Interior.Color = 65535
        Sheets("Data Set 2").Cells(nrng.Row, 3).Interior.Color = 65535
      End If
      If .Cells(c.Row, 5) <> Sheets("Data Set 2").Cells(nrng.Row, 4) Then
        .Cells(c.Row, 5).Interior.Color = 65535
        Sheets("Data Set 2").Cells(nrng.Row, 4).Interior.Color = 65535
      End If
    End If
  Next c
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).Interior.Color = 65535
        Sheets("Data Set 1").Cells(nrng.Row, 4).Interior.Color = 65535
      End If
      If .Cells(c.Row, 4) <> Sheets("Data Set 1").Cells(nrng.Row, 5) Then
        .Cells(c.Row, 4).Interior.Color = 65535
        Sheets("Data Set 1").Cells(nrng.Row, 5).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
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 CompareDatasets macro.

 

[CYDSRQ]

Thank you hiker95! 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....

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

CYDSRQ