Solved

Compare Lists on Two Worksheets Then Flag and Link Matching Location

Posted on 2012-03-29
6
252 Views
Last Modified: 2012-03-31
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
0
Comment
Question by:BrianEsser
  • 4
  • 2
6 Comments
 
LVL 41

Expert Comment

by:dlmille
ID: 37785212
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:
[C2]=IF($B2="Y",HYPERLINK("#" &ADDRESS(MATCH($A2,'LIST-B'!$B$2:$B$34575,0),2,,,"List-B"),"Link to LIST-B"),"Cell reference to link in LIST-B not possible")

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
0
 
LVL 41

Expert Comment

by:dlmille
ID: 37785250
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

Sub setupMatchAndHyperlink()
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

Open in new window


See attached.

Dave
20120329-EE-Compare-Lists-r1.xlsm
0
 
LVL 41

Expert Comment

by:dlmille
ID: 37785319
A slight error - the formula in column C should be:

[C2]=IF($B2="Y",HYPERLINK("#" & ADDRESS(MATCH($A2,'LIST-B'!$B$1:$B$34575,0),2,,,"List-B"),"Link to LIST-B"),"Cell reference to link in LIST-B not possible")

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
0
ScreenConnect 6.0 Free Trial

At ScreenConnect, partner feedback doesn't fall on deaf ears. We collected partner suggestions off of their virtual wish list and transformed them into one game-changing release: ScreenConnect 6.0. Explore all of the extras and enhancements for yourself!

 
LVL 41

Accepted Solution

by:
dlmille earned 500 total points
ID: 37785385
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

Sub setupMatchAndHyperlink()
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
Dim vLink As Variant

    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
            vLink = Evaluate("=ADDRESS(MATCH($A$" & r.Row & ",'LIST-B'!$B$1:$B" & lastRowB & ",0),2,,,""List-B"")")
            r.Offset(, 1).Hyperlinks.Add anchor:=r.Offset(, 1), Address:="", SubAddress:=vLink, TextToDisplay:="Link to LIST-B"
        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

Open in new window


See attached.

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

Author Comment

by:BrianEsser
ID: 37789963
Sorry for the delay getting back to my question and your responses - I'm going to go over the responses now... Thanks!
0
 

Author Closing Comment

by:BrianEsser
ID: 37789981
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
0

Featured Post

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question