List/highlight differences after comparing two groups of set data.

[xlbeginnerxl]

Hi,

My excel's worksheet layout is similar to the table below, but with 7000+rows. The only entity that is unique is the email address (Col A). The table below shows employees who have multiple mobile devices and whether they have installed Mobile Device Management (MDM) on each of their mobile devices.

I'd like to know how can I do the following:

• compare columns (E to K) with columns (M to S), and then list out the differences in columns (T-Z) as well as highlight the missing values in columns (E to K)?

Any help on this would be much appreciated.

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[TD]M[/TD]
[TD]N[/TD]
[TD]O[/TD]
[TD]P[/TD]
[TD]Q[/TD]
[TD]R[/TD]
[TD]S[/TD]
[TD]T[/TD]
[TD]U[/TD]
[TD]V[/TD]
[TD]W[/TD]
[TD]X[/TD]
[TD]Y[/TD]
[TD]Z[/TD]
[/TR]
[TR]
[TD]Email[/TD]
[TD]Name[/TD]
[TD]Location[/TD]
[TD]Total Devices[/TD]
[TD]Device1[/TD]
[TD]Device2[/TD]
[TD]Device3[/TD]
[TD]Device4[/TD]
[TD]Device5[/TD]
[TD]Device6[/TD]
[TD]Device7[/TD]
[TD]Number of devices MDM installed on[/TD]
[TD]MDM on Device1[/TD]
[TD]MDM on Device2[/TD]
[TD]MDM onDevice3[/TD]
[TD]MDM on Device4[/TD]
[TD]MDM on Device 5[/TD]
[TD]MDM on Device6[/TD]
[TD]MDM on Device 7[/TD]
[TD]To Install on 1[/TD]
[TD]To Install n 2[/TD]
[TD]To install on 3[/TD]
[TD]To Install on 4[/TD]
[TD]To Install on 5[/TD]
[TD]To Install on 6[/TD]
[TD]To Install on 7[/TD]
[/TR]
[TR]
[TD]abc@xyz.com[/TD]
[TD]abc[/TD]
[TD]London[/TD]
[TD]3[/TD]
[TD]iPad Air 2[/TD]
[TD]iPhone 6s Plus[/TD]
[TD]Android[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]2[/TD]
[TD]iPhone 6s Plus[/TD]
[TD]iPad Air 2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Android[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]cde@xyz.com[/TD]
[TD]cde[/TD]
[TD]New York[/TD]
[TD]4[/TD]
[TD]iPhone 4s[/TD]
[TD]iPhone 6[/TD]
[TD]iPad Mini[/TD]
[TD]Android[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]2[/TD]
[TD]Android[/TD]
[TD]iPad Mini[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]iPhone 4s[/TD]
[TD]iPhone 6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]fgh@xyz.com[/TD]
[TD]fgh[/TD]
[TD]Dublin[/TD]
[TD]2[/TD]
[TD]iPhone 6[/TD]
[TD]iPad Air[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]iPhone 6[/TD]
[TD]iPad Air[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


















Many thanks

PS: I've tried using formula conditional formatting rule:

• =COUNTIF($m$2:$s$2,$e2)=0 for =$e$2:$e$4398
• =COUNTIF($m$2:$s$2,$f2)=0 for =$f$2:$f$4398 & etc

but, it doesn't seem to work very well.

 

[Marcelo Branco]

Try this array formula in T2
=IFERROR(INDEX($E2:$K2,SMALL(IF($E2:$K2<>"",IF(ISNA(MATCH($E2:$K2,$M2:$S2,0)),COLUMN($E2:$K2)-COLUMN($E2)+1)),COLUMNS($E2:E2))),"")

confirmed with Ctrl+Shift+Enter, not just Enter

Copy (drag) across till Z2 and down

Hope this helps

M.

 

[Marcelo Branco]

To highlight

Select E2:Kn (where n is the last row with data)

Use this formula in Conditional Formatting

=COUNTIF($M2:$S2,E2)=0

pick the format you want

M.

 

[mumps]

VBA approach:

Sub CompareLists()
    Application.ScreenUpdating = False
    Dim Rng As Range, RngList As Object
    Dim LastRow As Long
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Dim email As Range
    Dim x As Long
    x = 20
    For Each email In Range("A2:A" & LastRow)
        Set RngList = CreateObject("Scripting.Dictionary")
        For Each Rng In Range("M" & email.Row & ":S" & email.Row)
            If Not RngList.Exists(Rng.Value) Then
              RngList.Add Rng.Value, Nothing
            End If
        Next
        For Each Rng In Range("E" & email.Row & ":K" & email.Row)
            If Not RngList.Exists(Rng.Value) Then
              Rng.Font.ColorIndex = 3
              Cells(email.Row, x) = Rng
              x = x + 1
            End If
        Next
        RngList.RemoveAll
        x = 20
    Next email
    Application.ScreenUpdating = True
End Sub

 

[MickG]

Another option:-

[COLOR="Navy"]Sub[/COLOR] MG15Dec11
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Dic [COLOR="Navy"]As[/COLOR] Object
[COLOR="Navy"]Dim[/COLOR] Ac [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer,[/COLOR] K [COLOR="Navy"]As[/COLOR] Variant
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A2"), Range("A" & Rows.Count).End(xlUp))
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
[COLOR="Navy"]Set[/COLOR] Dic = CreateObject("scripting.dictionary")
Dic.CompareMode = vbTextCompare
    [COLOR="Navy"]For[/COLOR] Ac = 5 To 19
        [COLOR="Navy"]If[/COLOR] Not Ac = 12 [COLOR="Navy"]Then[/COLOR]
            [COLOR="Navy"]If[/COLOR] Not Dic.Exists(Dn(, Ac).Value) [COLOR="Navy"]Then[/COLOR]
                Dic.Add Dn(, Ac).Value, Dn(, Ac)
            [COLOR="Navy"]Else[/COLOR]
                Dic.Remove (Dn(, Ac).Value)
            [COLOR="Navy"]End[/COLOR] If
        [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]Next[/COLOR] Ac
[COLOR="Navy"]If[/COLOR] Dic.Count > 0 [COLOR="Navy"]Then[/COLOR]
    Dn(, 20).Resize(, Dic.Count) = Dic.keys
    [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] K [COLOR="Navy"]In[/COLOR] Dic.keys
        Dic(K).Font.Color = vbRed
    [COLOR="Navy"]Next[/COLOR] K
[COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick