I have a workbook with 4 columns containing lists of names from 4 different
workbooks pasted as links to a name list in each of those workbooks, one
list per workbook. I'm trying to compare those lists using Conditional
Formatting. Ideally each column's results will match, but if they don't, I
want to know which ones don't. If I select the entire range, is there a
formula I can use to turn non-matches red?

--
David

Since you have 4 different lists, how about an alternative?

Create a new sheet (call it sheet2)
Put Name in A1
Copy the 4 lists into column A of this new sheet (one under the other)

Then select that range (A1:A###)
data|filter|advanced filter
Copy to another location
List range: (should be entered (a1:A###)
copy to: B1
Check Unique records only box

Now you have a list of unique names in column B.
Delete column A (we're done with it).

In B1, put: On List 1
In C1, put: On List 2
in D1, put: On List 3
in E1, put: On list 4

In B2, put this formula:
=isnumber(match(a2,sheet1!a:a,0))

In C2, put this:
=isnumber(match(a2,sheet1!b:b,0))

In D2:
=isnumber(match(a2,sheet1!c:c,0))

In E2:
=isnumber(match(a2,sheet1!D:D,0))

And drag down as far as column A extends.

Now select columns A:E and do Data|Filter|Autofilter.

You can filter to show the Falses in any column to see where its missing.



David wrote:

I have a workbook with 4 columns containing lists of names from 4 different
workbooks pasted as links to a name list in each of those workbooks, one
list per workbook. I'm trying to compare those lists using Conditional
Formatting. Ideally each column's results will match, but if they don't, I
want to know which ones don't. If I select the entire range, is there a
formula I can use to turn non-matches red?

--
David

--

Dave Peterson

Dave Peterson wrote

Since you have 4 different lists, how about an alternative?

Create a new sheet (call it sheet2)
Put Name in A1
Copy the 4 lists into column A of this new sheet (one under the other)

Then select that range (A1:A###)
data|filter|advanced filter
Copy to another location
List range: (should be entered (a1:A###)
copy to: B1
Check Unique records only box

Now you have a list of unique names in column B.
Delete column A (we're done with it).

In B1, put: On List 1
In C1, put: On List 2
in D1, put: On List 3
in E1, put: On list 4

In B2, put this formula:
=isnumber(match(a2,sheet1!a:a,0))

In C2, put this:
=isnumber(match(a2,sheet1!b:b,0))

In D2:
=isnumber(match(a2,sheet1!c:c,0))

In E2:
=isnumber(match(a2,sheet1!D:D,0))

And drag down as far as column A extends.

Now select columns A:E and do Data|Filter|Autofilter.

You can filter to show the Falses in any column to see where its
missing.

Well, things broke when I tried to copy the 2nd list (and presumably
would do the same with 3rd and 4th) with a #REF to cells from that 2nd
list, which references cells from a 2nd external file. I didn't pursue
things any further. Remember these lists are "built" as a result of links
to external files. If I pasted values, wouldn't I have to repeat your
proposed process each time one or more lists change (quite often)?
Basically this is a test file to insure I have not missed adding/deleting
a name from all 4 files. I can immediately see if things don't match when
length of lists differ, but then have to scan visually to see what name
starts the difference. Additionally each source range is from a different
range in each of the source workbooks.

Here's what I want:
If name is in A, but not in B or C or D, I want to know
If name is in B, but not in A or C or D, I want to know
If name is in C, but not in A or B or D, I want to know
If name is in D, but not in A or B or C, I want to know

Am I asking for 4 conditions, thereby exceeding CF's limit of 3? If so,
I'll accept 3 out of 4 <g.

--
David

Personally, I find Format|Conditional formatting very pretty--but pretty much
useless. You can't (easily) count the missing items; you can't filter by that
conditional formatting color.

If I had to do it over and over and over, I'd record a macro when I did those
steps (including the paste|special|Values). Then just rerun that whenever I
needed the info.

This seemed to work ok for me:

Option Explicit
Sub testme()
Dim CurWks As Worksheet
Dim NewWks As Worksheet
Dim iCol As Long
Dim DestCell As Range
Dim MaxCols As Long
Dim LastRow As Long

Set CurWks = Worksheets("sheet1")

Set NewWks = Worksheets.Add
Set DestCell = NewWks.Range("a2")

With CurWks
MaxCols = .Cells(1, .Columns.Count).End(xlToLeft).Column
For iCol = 1 To MaxCols
.Range(.Cells(2, iCol), .Cells(.Rows.Count, iCol).End(xlUp)).Copy
DestCell.PasteSpecial Paste:=xlPasteValues
With NewWks
Set DestCell = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0)
End With
Next iCol
End With

With NewWks
.Range("a1").Value = "Name"
.Range("A1", .Cells(.Rows.Count, "A").End(xlUp)).AdvancedFilter _
Action:=xlFilterCopy, copytorange:=.Range("B1"), unique:=True

.Range("a1").EntireColumn.Delete

.Range("a1").EntireColumn.Sort key1:=.Range("a1"), _
order1:=xlAscending, header:=xlYes

LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

For iCol = 1 To MaxCols
.Cells(1, iCol + 1).Value = "On List#" & iCol
With .Range(.Cells(2, iCol + 1), .Cells(LastRow, iCol + 1))
.Formula = "=isnumber(match(A2," _
& CurWks.Columns(iCol).Address(external:=True) & ",0))"
.Value = .Value
.Replace what:="True", replacement:="", _
lookat:=xlWhole, MatchCase:=False
.Replace what:="False", replacement:="No", _
lookat:=xlWhole, MatchCase:=False
.HorizontalAlignment = xlCenter
End With
Next iCol

.Cells(1, MaxCols + 2).Value = "Count Of No's"

With .Range(.Cells(2, MaxCols + 2), .Cells(LastRow, MaxCols + 2))
.Formula = "=countif(B2:" & _
.Parent.Cells(2, MaxCols + 1).Address(0, 0) & ",""no"")"
.Value = .Value
.HorizontalAlignment = xlCenter
End With

.Range("a1", .Cells(LastRow, MaxCols + 2)).AutoFilter

Application.Goto .Range("a1"), Scroll:=True
.Range("b2").Select
ActiveWindow.FreezePanes = True

.UsedRange.Columns.AutoFit

Set DestCell = .UsedRange 'try to reset last used cell

End With

End Sub

David wrote:

Dave Peterson wrote

Since you have 4 different lists, how about an alternative?

Create a new sheet (call it sheet2)
Put Name in A1
Copy the 4 lists into column A of this new sheet (one under the other)

Then select that range (A1:A###)
data|filter|advanced filter
Copy to another location
List range: (should be entered (a1:A###)
copy to: B1
Check Unique records only box

Now you have a list of unique names in column B.
Delete column A (we're done with it).

In B1, put: On List 1
In C1, put: On List 2
in D1, put: On List 3
in E1, put: On list 4

In B2, put this formula:
=isnumber(match(a2,sheet1!a:a,0))

In C2, put this:
=isnumber(match(a2,sheet1!b:b,0))

In D2:
=isnumber(match(a2,sheet1!c:c,0))

In E2:
=isnumber(match(a2,sheet1!D:D,0))

And drag down as far as column A extends.

Now select columns A:E and do Data|Filter|Autofilter.

You can filter to show the Falses in any column to see where its
missing.

Well, things broke when I tried to copy the 2nd list (and presumably
would do the same with 3rd and 4th) with a #REF to cells from that 2nd
list, which references cells from a 2nd external file. I didn't pursue
things any further. Remember these lists are "built" as a result of links
to external files. If I pasted values, wouldn't I have to repeat your
proposed process each time one or more lists change (quite often)?
Basically this is a test file to insure I have not missed adding/deleting
a name from all 4 files. I can immediately see if things don't match when
length of lists differ, but then have to scan visually to see what name
starts the difference. Additionally each source range is from a different
range in each of the source workbooks.

Here's what I want:
If name is in A, but not in B or C or D, I want to know
If name is in B, but not in A or C or D, I want to know
If name is in C, but not in A or B or D, I want to know
If name is in D, but not in A or B or C, I want to know

Am I asking for 4 conditions, thereby exceeding CF's limit of 3? If so,
I'll accept 3 out of 4 <g.

--
David

--

Dave Peterson

ps.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

David wrote:

Dave Peterson wrote

Since you have 4 different lists, how about an alternative?

Create a new sheet (call it sheet2)
Put Name in A1
Copy the 4 lists into column A of this new sheet (one under the other)

Then select that range (A1:A###)
data|filter|advanced filter
Copy to another location
List range: (should be entered (a1:A###)
copy to: B1
Check Unique records only box

Now you have a list of unique names in column B.
Delete column A (we're done with it).

In B1, put: On List 1
In C1, put: On List 2
in D1, put: On List 3
in E1, put: On list 4

In B2, put this formula:
=isnumber(match(a2,sheet1!a:a,0))

In C2, put this:
=isnumber(match(a2,sheet1!b:b,0))

In D2:
=isnumber(match(a2,sheet1!c:c,0))

In E2:
=isnumber(match(a2,sheet1!D:D,0))

And drag down as far as column A extends.

Now select columns A:E and do Data|Filter|Autofilter.

You can filter to show the Falses in any column to see where its
missing.

Well, things broke when I tried to copy the 2nd list (and presumably
would do the same with 3rd and 4th) with a #REF to cells from that 2nd
list, which references cells from a 2nd external file. I didn't pursue
things any further. Remember these lists are "built" as a result of links
to external files. If I pasted values, wouldn't I have to repeat your
proposed process each time one or more lists change (quite often)?
Basically this is a test file to insure I have not missed adding/deleting
a name from all 4 files. I can immediately see if things don't match when
length of lists differ, but then have to scan visually to see what name
starts the difference. Additionally each source range is from a different
range in each of the source workbooks.

Here's what I want:
If name is in A, but not in B or C or D, I want to know
If name is in B, but not in A or C or D, I want to know
If name is in C, but not in A or B or D, I want to know
If name is in D, but not in A or B or C, I want to know

Am I asking for 4 conditions, thereby exceeding CF's limit of 3? If so,
I'll accept 3 out of 4 <g.

--
David

--

Dave Peterson

Thanks very much for this code. One small detail I've tried to fiddle
with and can't get around. I'm sure it's simple. My lists on Sheet1 each
start in Row2, with Row1 being headers. How to adjust?

--
David

Dave Peterson wrote

Personally, I find Format|Conditional formatting very pretty--but
pretty much useless. You can't (easily) count the missing items; you
can't filter by that conditional formatting color.

If I had to do it over and over and over, I'd record a macro when I
did those steps (including the paste|special|Values). Then just rerun
that whenever I needed the info.

This seemed to work ok for me:

Option Explicit
Sub testme()
Dim CurWks As Worksheet
Dim NewWks As Worksheet
Dim iCol As Long
Dim DestCell As Range
Dim MaxCols As Long
Dim LastRow As Long

Set CurWks = Worksheets("sheet1")

Set NewWks = Worksheets.Add
Set DestCell = NewWks.Range("a2")

With CurWks
MaxCols = .Cells(1, .Columns.Count).End(xlToLeft).Column
For iCol = 1 To MaxCols
.Range(.Cells(2, iCol), .Cells(.Rows.Count,
iCol).End(xlUp)).Copy DestCell.PasteSpecial
Paste:=xlPasteValues With NewWks
Set DestCell = .Cells(.Rows.Count,
"A").End(xlUp).Offset(1, 0)
End With
Next iCol
End With

With NewWks
.Range("a1").Value = "Name"
.Range("A1", .Cells(.Rows.Count,
"A").End(xlUp)).AdvancedFilter _
Action:=xlFilterCopy, copytorange:=.Range("B1"),
unique:=True

.Range("a1").EntireColumn.Delete

.Range("a1").EntireColumn.Sort key1:=.Range("a1"), _
order1:=xlAscending,
header:=xlYes

LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

For iCol = 1 To MaxCols
.Cells(1, iCol + 1).Value = "On List#" & iCol
With .Range(.Cells(2, iCol + 1), .Cells(LastRow, iCol +
1))
.Formula = "=isnumber(match(A2," _
& CurWks.Columns(iCol).Address(external:=True) &
",0))"
.Value = .Value
.Replace what:="True", replacement:="", _
lookat:=xlWhole, MatchCase:=False
.Replace what:="False", replacement:="No", _
lookat:=xlWhole, MatchCase:=False
.HorizontalAlignment = xlCenter
End With
Next iCol

.Cells(1, MaxCols + 2).Value = "Count Of No's"

With .Range(.Cells(2, MaxCols + 2), .Cells(LastRow, MaxCols +
2))
.Formula = "=countif(B2:" & _
.Parent.Cells(2, MaxCols + 1).Address(0, 0) &
",""no"")"
.Value = .Value
.HorizontalAlignment = xlCenter
End With

.Range("a1", .Cells(LastRow, MaxCols + 2)).AutoFilter

Application.Goto .Range("a1"), Scroll:=True
.Range("b2").Select
ActiveWindow.FreezePanes = True

.UsedRange.Columns.AutoFit

Set DestCell = .UsedRange 'try to reset last used cell

End With

End Sub

David wrote:

Dave Peterson wrote

Since you have 4 different lists, how about an alternative?

Create a new sheet (call it sheet2)
Put Name in A1
Copy the 4 lists into column A of this new sheet (one under the
other)

Then select that range (A1:A###)
data|filter|advanced filter
Copy to another location
List range: (should be entered (a1:A###)
copy to: B1
Check Unique records only box

Now you have a list of unique names in column B.
Delete column A (we're done with it).

In B1, put: On List 1
In C1, put: On List 2
in D1, put: On List 3
in E1, put: On list 4

In B2, put this formula:
=isnumber(match(a2,sheet1!a:a,0))

In C2, put this:
=isnumber(match(a2,sheet1!b:b,0))

In D2:
=isnumber(match(a2,sheet1!c:c,0))

In E2:
=isnumber(match(a2,sheet1!D:D,0))

And drag down as far as column A extends.

Now select columns A:E and do Data|Filter|Autofilter.

You can filter to show the Falses in any column to see where its
missing.

Well, things broke when I tried to copy the 2nd list (and presumably
would do the same with 3rd and 4th) with a #REF to cells from that
2nd list, which references cells from a 2nd external file. I didn't
pursue things any further. Remember these lists are "built" as a
result of links to external files. If I pasted values, wouldn't I
have to repeat your proposed process each time one or more lists
change (quite often)? Basically this is a test file to insure I have
not missed adding/deleting a name from all 4 files. I can immediately
see if things don't match when length of lists differ, but then have
to scan visually to see what name starts the difference. Additionally
each source range is from a different range in each of the source
workbooks.

Here's what I want:
If name is in A, but not in B or C or D, I want to know
If name is in B, but not in A or C or D, I want to know
If name is in C, but not in A or B or D, I want to know
If name is in D, but not in A or B or C, I want to know

Am I asking for 4 conditions, thereby exceeding CF's limit of 3? If
so, I'll accept 3 out of 4 <g.

--
David

That's the way the code was written--using row 1 as headers and data starting in
row 2.

Did you have trouble when you tried it?

David wrote:

Thanks very much for this code. One small detail I've tried to fiddle
with and can't get around. I'm sure it's simple. My lists on Sheet1 each
start in Row2, with Row1 being headers. How to adjust?

--
David

Dave Peterson wrote

Personally, I find Format|Conditional formatting very pretty--but
pretty much useless. You can't (easily) count the missing items; you
can't filter by that conditional formatting color.

If I had to do it over and over and over, I'd record a macro when I
did those steps (including the paste|special|Values). Then just rerun
that whenever I needed the info.

This seemed to work ok for me:

Option Explicit
Sub testme()
Dim CurWks As Worksheet
Dim NewWks As Worksheet
Dim iCol As Long
Dim DestCell As Range
Dim MaxCols As Long
Dim LastRow As Long

Set CurWks = Worksheets("sheet1")

Set NewWks = Worksheets.Add
Set DestCell = NewWks.Range("a2")

With CurWks
MaxCols = .Cells(1, .Columns.Count).End(xlToLeft).Column
For iCol = 1 To MaxCols
.Range(.Cells(2, iCol), .Cells(.Rows.Count,
iCol).End(xlUp)).Copy DestCell.PasteSpecial
Paste:=xlPasteValues With NewWks
Set DestCell = .Cells(.Rows.Count,
"A").End(xlUp).Offset(1, 0)
End With
Next iCol
End With

With NewWks
.Range("a1").Value = "Name"
.Range("A1", .Cells(.Rows.Count,
"A").End(xlUp)).AdvancedFilter _
Action:=xlFilterCopy, copytorange:=.Range("B1"),
unique:=True

.Range("a1").EntireColumn.Delete

.Range("a1").EntireColumn.Sort key1:=.Range("a1"), _
order1:=xlAscending,
header:=xlYes

LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

For iCol = 1 To MaxCols
.Cells(1, iCol + 1).Value = "On List#" & iCol
With .Range(.Cells(2, iCol + 1), .Cells(LastRow, iCol +
1))
.Formula = "=isnumber(match(A2," _
& CurWks.Columns(iCol).Address(external:=True) &
",0))"
.Value = .Value
.Replace what:="True", replacement:="", _
lookat:=xlWhole, MatchCase:=False
.Replace what:="False", replacement:="No", _
lookat:=xlWhole, MatchCase:=False
.HorizontalAlignment = xlCenter
End With
Next iCol

.Cells(1, MaxCols + 2).Value = "Count Of No's"

With .Range(.Cells(2, MaxCols + 2), .Cells(LastRow, MaxCols +
2))
.Formula = "=countif(B2:" & _
.Parent.Cells(2, MaxCols + 1).Address(0, 0) &
",""no"")"
.Value = .Value
.HorizontalAlignment = xlCenter
End With

.Range("a1", .Cells(LastRow, MaxCols + 2)).AutoFilter

Application.Goto .Range("a1"), Scroll:=True
.Range("b2").Select
ActiveWindow.FreezePanes = True

.UsedRange.Columns.AutoFit

Set DestCell = .UsedRange 'try to reset last used cell

End With

End Sub

David wrote:

Dave Peterson wrote

Since you have 4 different lists, how about an alternative?

Create a new sheet (call it sheet2)
Put Name in A1
Copy the 4 lists into column A of this new sheet (one under the
other)

Then select that range (A1:A###)
data|filter|advanced filter
Copy to another location
List range: (should be entered (a1:A###)
copy to: B1
Check Unique records only box

Now you have a list of unique names in column B.
Delete column A (we're done with it).

In B1, put: On List 1
In C1, put: On List 2
in D1, put: On List 3
in E1, put: On list 4

In B2, put this formula:
=isnumber(match(a2,sheet1!a:a,0))

In C2, put this:
=isnumber(match(a2,sheet1!b:b,0))

In D2:
=isnumber(match(a2,sheet1!c:c,0))

In E2:
=isnumber(match(a2,sheet1!D:D,0))

And drag down as far as column A extends.

Now select columns A:E and do Data|Filter|Autofilter.

You can filter to show the Falses in any column to see where its
missing.

Well, things broke when I tried to copy the 2nd list (and presumably
would do the same with 3rd and 4th) with a #REF to cells from that
2nd list, which references cells from a 2nd external file. I didn't
pursue things any further. Remember these lists are "built" as a
result of links to external files. If I pasted values, wouldn't I
have to repeat your proposed process each time one or more lists
change (quite often)? Basically this is a test file to insure I have
not missed adding/deleting a name from all 4 files. I can immediately
see if things don't match when length of lists differ, but then have
to scan visually to see what name starts the difference. Additionally
each source range is from a different range in each of the source
workbooks.

Here's what I want:
If name is in A, but not in B or C or D, I want to know
If name is in B, but not in A or C or D, I want to know
If name is in C, but not in A or B or D, I want to know
If name is in D, but not in A or B or C, I want to know

Am I asking for 4 conditions, thereby exceeding CF's limit of 3? If
so, I'll accept 3 out of 4 <g.

--
David


--

Dave Peterson

Dave Peterson wrote

That's the way the code was written--using row 1 as headers and data
starting in row 2.

Did you have trouble when you tried it?

That's what it looked like to me, looking at the various .Cell references.
For some reason, A2 & F2 on Sheet2 wind up with a 0 (zero) in them. Could
it be because I have an equal number of rows of "padding" at the bottom of
the 4 columns in Sheet1? They contain formulas that evaluate to zero from
each of the linked files in case I add people to those lists.

--
David

Dave Peterson wrote

That's the way the code was written--using row 1 as headers and data
starting in row 2.

Did you have trouble when you tried it?

Ok, as an experiment, I deleted the rows that evaluated to zero and things
on Sheet2 wind up fine now. Would like to keep the padded range, though, so
I may have to live with the zero's.

--
David

If you use formulas that look like:
=sheet1!a1

You'll see that they return a 0 if A1 is empty.

I like this formula better:
=if(sheet1!a1="","",sheet1!a1)

Then my "padded" range looks empty. This won't change the results of the
macro--you'll see blanks instead of 0's, though.


David wrote:

Dave Peterson wrote

That's the way the code was written--using row 1 as headers and data
starting in row 2.

Did you have trouble when you tried it?

Ok, as an experiment, I deleted the rows that evaluated to zero and things
on Sheet2 wind up fine now. Would like to keep the padded range, though, so
I may have to live with the zero's.

--
David

--

Dave Peterson

Dave Peterson wrote

If you use formulas that look like:
=sheet1!a1

You'll see that they return a 0 if A1 is empty.

I like this formula better:
=if(sheet1!a1="","",sheet1!a1)

Then my "padded" range looks empty. This won't change the results of the
macro--you'll see blanks instead of 0's, though.

I opted for this "dirty" fix toward the end of the macro:
Application.Goto .Range("a1"), Scroll:=True
.Range("b2").Select
ActiveWindow.FreezePanes = True
.Rows(2).Delete '<---
.UsedRange.Columns.AutoFit

--
David

Or maybe to preserve integrity:

..Rows(2).Hidden = True

--
David

I think I'd check to see if that cell was empty first.

if .range("B2").value = "" then
.rows(2).delete
end if
Application.Goto .Range("a1"), Scroll:=True
.range("b2").select
ActiveWindow.FreezePanes = True
.UsedRange.Columns.AutoFit

Just in case that "padded row" was actually used.

David wrote:

Dave Peterson wrote

If you use formulas that look like:
=sheet1!a1

You'll see that they return a 0 if A1 is empty.

I like this formula better:
=if(sheet1!a1="","",sheet1!a1)

Then my "padded" range looks empty. This won't change the results of the
macro--you'll see blanks instead of 0's, though.

I opted for this "dirty" fix toward the end of the macro:
Application.Goto .Range("a1"), Scroll:=True
.Range("b2").Select
ActiveWindow.FreezePanes = True
.Rows(2).Delete '<---
.UsedRange.Columns.AutoFit

--
David

--

Dave Peterson

I'd just check, then (maybe) delete.

David wrote:

Or maybe to preserve integrity:

.Rows(2).Hidden = True

--
David

--

Dave Peterson

Good point. Thanks for all your help

--
David

Dave Peterson wrote

I think I'd check to see if that cell was empty first.

if .range("B2").value = "" then
.rows(2).delete
end if
Application.Goto .Range("a1"), Scroll:=True
.range("b2").select
ActiveWindow.FreezePanes = True
.UsedRange.Columns.AutoFit

Just in case that "padded row" was actually used.

Dave Peterson wrote

I'd just check, then (maybe) delete.

David wrote:

Or maybe to preserve integrity:

.Rows(2).Hidden = True

--
David