Compare two SETS of data based off of a unique identifier

[BondReborn]

I am trying to take an export from our inventory management program and compare it to a tracker that was made after we completed a wall-to-wall inventory. I would like our sheet to find the unique identifier on the export and then compare the information in the adjacent cells. If the information is not right I would like it to either highlight or mark it in some way since just my section has 1500 entries and each entry has 3 fields that I need to compare.
Example

[table="width: 500, class: grid"]
[tr]
[td]Unique Identifier[/td]
[td]Model[/td]
[td]Serial Number[/td]
[td]Location[/td]
[td]Unique Identifier[/td]
[td]Model[/td]
[td]Serial Number[/td]
[td]Location[/td]
[/tr]
[tr]
[td]001[/td]
[td]990[/td]
[td]123456[/td]
[td]RM #1301[/td]
[td]030[/td]
[td]9020[/td]
[td]123654[/td]
[td]RM #2045[/td]
[/tr]
[tr]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td]001[/td]
[td]990[/td]
[td]123456[/td]
[td]RM #1302[/td]
[/tr]
[/table]



I want our sheet to look for 001 in the right list and then compare the model, serial number, and location columns to the information that we have. When it notices that the export says that this equipment is located in 1302 instead of 1301 I would like it to highlight the cells that are different or notify me in some other way.

 

[hiker95]

BondReborn,

Welcome to the MrExcel forum.

1. What version of Excel, and, Windows are you using?

2. Are you using a PC or a Mac?

If I understand you correctly, here is a macro solution for you to consider, based on your screenshot.

You can change the raw data worksheet name in the macro.

Sample raw data:

Excel 2007
	A	B	C	D	E	F	G	H
1	Unique Identifier	Model	Serial Number	Location	Unique Identifier	Model	Serial Number	Location
2	001	990	123456	RM #1301	030	9020	123654	RM #2045
3					001	990	123456	RM #1302
4
5
Sheet1

And, after the macro:

Excel 2007
	A	B	C	D	E	F	G	H
1	Unique Identifier	Model	Serial Number	Location	Unique Identifier	Model	Serial Number	Location
2	001	990	123456	RM #1301	030	9020	123654	RM #2045
3					001	990	123456	RM #1302
4
5
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).

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.

Sub CompareHighliteDifferences()
' hiker95, 12/15/2015, ME908867
Dim r As Range, a As Range, c As Long
Application.ScreenUpdating = True
With Sheets("Sheet1")    '<-- you can change the sheet name here
  For Each r In .Range("E2", .Range("E" & Rows.Count).End(xlUp))
    Set a = .Columns(1).Find(r.Value, LookAt:=xlWhole)
    If a Is Nothing Then
      .Range("E" & r.Row).Resize(, 4).Interior.Color = vbYellow
    ElseIf Not a Is Nothing Then
      For c = 5 To 8 Step 1
        If .Cells(r.Row, c).Value <> .Cells(a.Row, c - 4).Value Then
          .Cells(r.Row, c).Interior.Color = vbYellow
          .Cells(a.Row, c - 4).Interior.Color = vbYellow
        End If
      Next c
    End If
  Next r
End With
Application.ScreenUpdating = False
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, and, answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

Then run the CompareHighliteDifferences macro.

 

[hiker95]

BondReborn,

If your raw data looked like this:

Excel 2007
	A	B	C	D	E	F	G	H
1	Unique Identifier	Model	Serial Number	Location	Unique Identifier	Model	Serial Number	Location
2	001	990	123456	RM #1301	030	9020	123654	RM #2045
3					001	990	123456	RM #1302
4	030	9020	123654	RM #2045
5
Sheet1

Then, with the same macro, the results would look like this:[/b]

Excel 2007
	A	B	C	D	E	F	G	H
1	Unique Identifier	Model	Serial Number	Location	Unique Identifier	Model	Serial Number	Location
2	001	990	123456	RM #1301	030	9020	123654	RM #2045
3					001	990	123456	RM #1302
4	030	9020	123654	RM #2045
5
Sheet1

 

[BondReborn]

I don't know what sorcery you used but I LOVE IT!

 

[hiker95]

BondReborn,

Thanks for the feedback on my sorcery.

You are very welcome. Glad I could help.

And, come back anytime.

 

[hiker95]

BondReborn,

Thanks for the Private Message, and, confidence.

I have not been able to respond to you with a Private Message.

Please provide more screenshots of your new raw data, and, what the results should look like.

After you do the above, I will see that this thread has been updated, and, I will have a look.