Find value from drop down on table and compare columns then list differences

Question

I have tried coding this myself with no luck whatsoever (I am a novice).

We have a worksheet that contains lists of users, their job titles and roles assigned to them within certain systems.

What I am trying to do is add drop downs to select a name of someone in the list (this bit is fine, no issues). Then when selected have a box in the middle that lists differences of assigned roles.

So far I have tried How to compare two columns and find differences in Excel? but due to my knowledge it was no help. I have also browsed various other answers on here and other websites but can't get to the bottom fo what I am trying to do.

I found the below code but don't know how to adapt this to my cause -

Sub divide()
Dim sh1 As Worksheet, sh2 As Worksheet, sh3 As Worksheet, lr1 As Long, lr2 As Long, rng1 As Range, rng2 As Range, c As Range
Set sh1 = Sheet1 'Edit sheet name
Set sh2 = Sheet2 'Edit sheet name
Set sh3 = Sheet3 'Edit sheet name
lr1 = sh1.Cells(Rows.Count, 1).End(xlUp).Row 'Get the last row with data for both list sheets
lr2 = sh2.Cells(Rows.Count, 1).End(xlUp).Row
Set rng1 = sh1.Range("A2:xb200" & lr1) 'Establish the ranges on both sheets
Set rng2 = sh2.Range("A2:xb200" & lr2)
With sh3 'If header not there, put them in
    If .Range("A1") = "" And .Range("B1") = "" Then
        .Range("A1") = "Extras in List 1"
        .Range("B1") = "Extras in List 2"
    End If
End With
    For Each c In rng1 'Run a loop for each list ID mismatches and paste to sheet 3.
        If WorksheetFunction.CountIf(rng2, c.Value) = 0 Then
            sh3.Cells(Rows.Count, 1).End(xlUp)(2) = c.Value
        End If
    Next
    For Each c In rng2
        If Application.CountIf(rng1, c.Value) = 0 Then
            sh3.Cells(Rows.Count, 2).End(xlUp)(2) = c.Value
        End If
    Next
End Sub

My sheet names are "usersandroles", this lists everything in seperate columns, namely - A System, B User, C Role, D Title E Key filter (I have added this column to filter out jobs with only one person as there is nothing to compare against). "Pivot Data" which does give a comparison but doesn't make for easy viewing when there is a hundred or so users in the same role. "Table" this simply lists the persons name as a column header, then all roles assigned to them in that column. "Comparison" where I want the magic to happen.

This was my poor start (and yes it is next to no code, but at least I tried)

Sub Button4_Click()

Dim lbx1 As Long
Dim lbx2 As Range
Dim rfinda As Range
Dim rfindb As Range


Set lbx1 = ("c4:h4")
Set lbx2 = ("q4:v4")
Set rfinda = .Find(what:=lbx1.Value, lookat:=xlWhole, MatchCase:=False, searchformat:=False)
Set rfindb = .Find(what:=lbx2.Value, lookat:=xlWhole, MatchCase:=False, searchformat:=False)


With "Comparison"
If lbx1 > "" Then
Activate.Worksheet ("Table")
End If
With ActiveSheet
If rfinda & lbx1 = lbx1 Then

This is a mock up of the sheet so you can see how it displays -

Worksheet

This is how I would like it to display on another sheet -

comparison

The ideal scenario is to have it list the roles against both of them under their names then have the comparison/extra roles in the middle as in the image. But I couldn't even figure out how to get that to happen without using dedicated drop down lists. However as there is over 600 users that just didn't seem to be an option.

I have tried using named ranges etc but just cannot figure it out.

I don't expect someone to code it for me, but if someone can get me going in the right direction I'm happy to plug away at it.

score 1 · Answer 1 · answered Nov 21 '18 at 13:11

No need to worry about this now. I've sussed it. It might not be as elegant as some would create but it goes like this -

C2 and N2 are used for data validation pulling the headers (users names) from the table tab.

Each data validation has a "Get Details" butt underneath with the below code for their respective search criteria -

Sub FindSecond()
Dim FindString As String
Dim Rng As Range
FindString = Range("N2")
If Trim(FindString) <> "" Then
    With Sheets("Table").Range("1:1") 'searches all of Row 1
        Set Rng = .Find(What:=FindString, _
                        After:=.Cells(.Cells.Count), _
                        LookIn:=xlValues, _
                        LookAt:=xlWhole, _
                        SearchOrder:=xlByRows, _
                        SearchDirection:=xlNext, _
                        MatchCase:=False)
        If Not Rng Is Nothing Then
            Application.Goto Rng, True 'value found
            Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Sheets("Comparison").Select
    Range("N5").Select
    ActiveSheet.Paste
        Else
            MsgBox "Nothing found" 'value not found
        End If
    End With
End If
End Sub

I have then added two columns to the middle with simple countif formula to return any values that don't match for each column, so now we have -

Current worksheet

Users just select a user, press the button to load/copy their details and boom, they have the differences they were after.

Find value from drop down on table and compare columns then list differences

1 Answers1