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.

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

V

W

X

Y

Z

Email

Name

Location

Total Devices

Device1

Device2

Device3

Device4

Device5

Device6

Device7

Number of devices MDM installed on

MDM on Device1

MDM on Device2

MDM onDevice3

MDM on Device4

MDM on Device 5

MDM on Device6

MDM on Device 7

To Install on 1

To Install n 2

To install on 3

To Install on 4

To Install on 5

To Install on 6

To Install on 7

abc@xyz.com

abc

London

3

iPad Air 2

iPhone 6s Plus

Android

2

iPhone 6s Plus

iPad Air 2

Android

cde@xyz.com

cde

New York

4

iPhone 4s

iPhone 6

iPad Mini

Android

2

Android

iPad Mini

iPhone 4s

iPhone 6

fgh@xyz.com

fgh

Dublin

2

iPhone 6

iPad Air

0

iPhone 6

iPad Air

<tbody>
</tbody>

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