Vrftjkry

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.

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.