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 -
This is how I would like it to display on another sheet -
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.