Conditional Formatting 4 Columns

D

David

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?
 
D

Dave Peterson

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.
 
D

David

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.
Click to expand...

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>.
 
D

Dave Peterson

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
 
D

David

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?
 
D

Dave Peterson

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?
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?
Click to expand...
 
D

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?
Click to expand...

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.
 
D

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?
Click to expand...

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.
 
D

Dave Peterson

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.
 
D

David

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.
Click to expand...

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
 
D

Dave Peterson

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.
 
D

David

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.
Click to expand...

Dave Peterson wrote
 
Top