# Compare Lists on Two Worksheets Then Flag and Link Matching Location

Need to compare all the Point Name values in sheet LIST-A with Point Name values in sheet LIST-B.
If the Name in LIST-A has a unique match (should be unique) anywhere in LIST-B then Flag LIST-A sheet Column B "IN ICS" with Y else N  and then provide a hyper-link (or cell reference if H-Link not possible) to the matching cell location in LIST-B on LIST-A in Column C "LINK".

Both lists will have variable lengths going forward.

Reference Attached Workbook.

Thanks!
20120329-EE-Compare-Lists.xlsx
###### Who is Participating?

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Commented:
Put this in column B in List-A and copy down:

[B2]=IF(COUNTIF('LIST-B'!\$B\$2:\$B\$34575,\$A2)=1,"Y","N")

Put this in column C in List-A and copy down:

Its a big lookup, so it will take a moment to calculate.  You can turn calculations to manual and then hit F9 when you want an update.  You can also keep the first row of formulas and then convert the rest to values by selecting the rest, copy/pastespecial values, and it will be fast, but you'll have to update the formulas again to update the link.

The only other alternative is to write a vba macro that does this step for you in a button.

Dave
Commented:
I've written a vba script to do this for the entire sheet, or just rows you select, so you can update only a few rows and it runs significantly faster when just updating a few rows:

``````Option Explicit

Dim wksA As Worksheet
Dim wksB As Worksheet
Dim firstRowA As Long
Dim lastRowA As Long
Dim lastRowB As Long
Dim rng As Range
Dim r As Range
Dim xCalc As Long
Dim xMsg As Long

xCalc = Application.Calculation
Application.Calculation = xlCalculationManual

Set wksA = ThisWorkbook.Sheets("LIST-A")
Set wksB = ThisWorkbook.Sheets("LIST-B")

xMsg = MsgBox("Referesh entire sheet, or just selected rows?", vbYesNo, "YES for Entire Sheet, NO for selected rows")

lastRowB = wksB.Range("A" & wksB.Rows.Count).End(xlUp).Row

If xMsg = vbYes Then
firstRowA = 2
lastRowA = wksA.Cells(wksA.Rows.Count, 1).End(xlUp).Row
Else
firstRowA = Selection.Cells(1, 1).Row
lastRowA = Selection.Offset(Selection.Rows.Count - 1, 0).Resize(1, 1).Row
End If

Set rng = wksA.Range("B" & firstRowA, "B" & lastRowA)

rng.Formula = "=IF(COUNTIF('LIST-B'!\$B\$" & firstRowA & ":\$B\$" & lastRowB & ",\$A" & firstRowA & ")=1,""Y"",""N"")"

rng.Offset(, 1).Formula = "=IF(\$B" & firstRowA & "=""Y"",HYPERLINK(""#"" & ADDRESS(MATCH(\$A" & firstRowA & ",'LIST-B'!\$B\$" & firstRowA & ":\$B\$" & lastRowB & ",0),2,,,""List-B""),""Link to LIST-B""),""Cell reference to link in LIST-B not possible"")"

rng.Resize(rng.Rows.Count, 1).Value = rng.Resize(rng.Rows.Count, 1).Value

'Application.Calculate
Application.Calculation = xCalc
End Sub
``````

See attached.

Dave
20120329-EE-Compare-Lists-r1.xlsm
Commented:
A slight error - the formula in column C should be:

Attached, please find the r1 version of the VBA enabled workbook with this correction.

Also, I'm rewriting the VBA script in hopes of speeding up the process.  Give me a moment.

Dave
20120329-EE-Compare-Lists-r1.xlsm
Commented:
This version takes a bit to run because its creating an embedded hyperlink on every row with unique link, but you are not encumbered with the HYPERLINK formula updating every time the workbook calculates (assuming you're doing other stuff with the workbook).

Let me know which version you prefer and if you have any issues.

``````Option Explicit

Dim wksA As Worksheet
Dim wksB As Worksheet
Dim firstRowA As Long
Dim lastRowA As Long
Dim lastRowB As Long
Dim rng As Range
Dim r As Range
Dim xCalc As Long
Dim xMsg As Long

Application.ScreenUpdating = False

xCalc = Application.Calculation
Application.Calculation = xlCalculationManual

Set wksA = ThisWorkbook.Sheets("LIST-A")
Set wksB = ThisWorkbook.Sheets("LIST-B")

xMsg = MsgBox("Referesh entire sheet, or just selected rows?", vbYesNo, "YES for Entire Sheet, NO for selected rows")

lastRowB = wksB.Range("A" & wksB.Rows.Count).End(xlUp).Row

If xMsg = vbYes Then
firstRowA = 2
lastRowA = wksA.Cells(wksA.Rows.Count, 1).End(xlUp).Row
Else
firstRowA = Selection.Cells(1, 1).Row
lastRowA = Selection.Offset(Selection.Rows.Count - 1, 0).Resize(1, 1).Row
End If

Set rng = wksA.Range("B" & firstRowA, "B" & lastRowA)

For Each r In rng
r.Formula = "=IF(COUNTIF('LIST-B'!\$B\$2:\$B\$" & lastRowB & ",\$A" & r.Row & ")=1,""Y"",""N"")"
If r.Value = "Y" Then
r.Offset(, 1).Clear
Else
r.Offset(, 1).Value = "Cell reference to link in LIST-B not possible"
End If
r.Value = r.Value
Next r

Application.ScreenUpdating = True
Application.Calculation = xCalc
End Sub
``````

See attached.

Dave
20120329-EE-Compare-Lists-r2.xlsm

Experts Exchange Solution brought to you by

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Zone Technology ExpertAuthor Commented:
Sorry for the delay getting back to my question and your responses - I'm going to go over the responses now... Thanks!
Zone Technology ExpertAuthor Commented:
Dave,

This is certainly an improvement over the previous version you offered and it was prescient of you to anticipate I would be using this solution as a component of a larger Workbook where the Hyperlink formula recalcs would have been cumbersome. Where's the extra credit check box on this form - you deserve the extra mile award.

I look forward to reverse engineering your method to incorporate into my production Workbook and augmenting my ongoing education for applying Excel VBA automation. If I encounter any issues with the code/implementation I'll post a new question. Thank you for such an elegant solution to add to my Knowledge Base.

Brian
###### It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.