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,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:
gt;
gt; I have a workbook with 4 columns containing lists of names from 4 different
gt; workbooks pasted as links to a name list in each of those workbooks, one
gt; list per workbook. I'm trying to compare those lists using Conditional
gt; Formatting. Ideally each column's results will match, but if they don't, I
gt; want to know which ones don't. If I select the entire range, is there a
gt; formula I can use to turn non-matches red?
gt;
gt; --
gt; David
--
Dave Peterson
Dave Peterson wrote
gt; Since you have 4 different lists, how about an alternative?
gt;
gt; Create a new sheet (call it sheet2)
gt; Put Name in A1
gt; Copy the 4 lists into column A of this new sheet (one under the other)
gt;
gt; Then select that range (A1:A###)
gt; data|filter|advanced filter
gt; Copy to another location
gt; List range: (should be entered (a1:A###)
gt; copy to: B1
gt; Check Unique records only box
gt;
gt; Now you have a list of unique names in column B.
gt; Delete column A (we're done with it).
gt;
gt; In B1, put: On List 1
gt; In C1, put: On List 2
gt; in D1, put: On List 3
gt; in E1, put: On list 4
gt;
gt; In B2, put this formula:
gt; =isnumber(match(a2,sheet1!a:a,0))
gt;
gt; In C2, put this:
gt; =isnumber(match(a2,sheet1!b:b,0))
gt;
gt; In D2:
gt; =isnumber(match(a2,sheet1!c:c,0))
gt;
gt; In E2:
gt; =isnumber(match(a2,sheet1!D,0))
gt;
gt; And drag down as far as column A extends.
gt;
gt; Now select columns A:E and do Data|Filter|Autofilter.
gt;
gt; You can filter to show the Falses in any column to see where its
gt; 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 quot;builtquot; 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 lt;ggt;.
--
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(quot;sheet1quot;)
Set NewWks = Worksheets.Add
Set DestCell = NewWks.Range(quot;a2quot;)
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, quot;Aquot;).End(xlUp).Offset(1, 0)
End With
Next iCol
End With
With NewWks
.Range(quot;a1quot;).Value = quot;Namequot;
.Range(quot;A1quot;, .Cells(.Rows.Count, quot;Aquot;).End(xlUp)).AdvancedFilter _
Action:=xlFilterCopy, copytorange:=.Range(quot;B1quot;), unique:=True
.Range(quot;a1quot;).EntireColumn.Delete
.Range(quot;a1quot;).EntireColumn.Sort key1:=.Range(quot;a1quot;), _
order1:=xlAscending, header:=xlYes
LastRow = .Cells(.Rows.Count, quot;Aquot;).End(xlUp).Row
For iCol = 1 To MaxCols
.Cells(1, iCol 1).Value = quot;On List#quot; amp; iCol
With .Range(.Cells(2, iCol 1), .Cells(LastRow, iCol 1))
.Formula = quot;=isnumber(match(A2,quot; _
amp; CurWks.Columns(iCol).Address(external:=True) amp; quot;,0))quot;
.Value = .Value
.Replace what:=quot;Truequot;, replacement:=quot;quot;, _
lookat:=xlWhole, MatchCase:=False
.Replace what:=quot;Falsequot;, replacement:=quot;Noquot;, _
lookat:=xlWhole, MatchCase:=False
.HorizontalAlignment = xlCenter
End With
Next iCol
.Cells(1, MaxCols 2).Value = quot;Count Of No'squot;
With .Range(.Cells(2, MaxCols 2), .Cells(LastRow, MaxCols 2))
.Formula = quot;=countif(B2:quot; amp; _
.Parent.Cells(2, MaxCols 1).Address(0, 0) amp; quot;,quot;quot;noquot;quot;)quot;
.Value = .Value
.HorizontalAlignment = xlCenter
End With
.Range(quot;a1quot;, .Cells(LastRow, MaxCols 2)).AutoFilter
Application.Goto .Range(quot;a1quot;), Scroll:=True
.Range(quot;b2quot;).Select
ActiveWindow.FreezePanes = True
.UsedRange.Columns.AutoFit
Set DestCell = .UsedRange 'try to reset last used cell
End With
End Sub
David wrote:
gt;
gt; Dave Peterson wrote
gt;
gt; gt; Since you have 4 different lists, how about an alternative?
gt; gt;
gt; gt; Create a new sheet (call it sheet2)
gt; gt; Put Name in A1
gt; gt; Copy the 4 lists into column A of this new sheet (one under the other)
gt; gt;
gt; gt; Then select that range (A1:A###)
gt; gt; data|filter|advanced filter
gt; gt; Copy to another location
gt; gt; List range: (should be entered (a1:A###)
gt; gt; copy to: B1
gt; gt; Check Unique records only box
gt; gt;
gt; gt; Now you have a list of unique names in column B.
gt; gt; Delete column A (we're done with it).
gt; gt;
gt; gt; In B1, put: On List 1
gt; gt; In C1, put: On List 2
gt; gt; in D1, put: On List 3
gt; gt; in E1, put: On list 4
gt; gt;
gt; gt; In B2, put this formula:
gt; gt; =isnumber(match(a2,sheet1!a:a,0))
gt; gt;
gt; gt; In C2, put this:
gt; gt; =isnumber(match(a2,sheet1!b:b,0))
gt; gt;
gt; gt; In D2:
gt; gt; =isnumber(match(a2,sheet1!c:c,0))
gt; gt;
gt; gt; In E2:
gt; gt; =isnumber(match(a2,sheet1!D,0))
gt; gt;
gt; gt; And drag down as far as column A extends.
gt; gt;
gt; gt; Now select columns A:E and do Data|Filter|Autofilter.
gt; gt;
gt; gt; You can filter to show the Falses in any column to see where its
gt; gt; missing.
gt;
gt; Well, things broke when I tried to copy the 2nd list (and presumably
gt; would do the same with 3rd and 4th) with a #REF to cells from that 2nd
gt; list, which references cells from a 2nd external file. I didn't pursue
gt; things any further. Remember these lists are quot;builtquot; as a result of links
gt; to external files. If I pasted values, wouldn't I have to repeat your
gt; proposed process each time one or more lists change (quite often)?
gt; Basically this is a test file to insure I have not missed adding/deleting
gt; a name from all 4 files. I can immediately see if things don't match when
gt; length of lists differ, but then have to scan visually to see what name
gt; starts the difference. Additionally each source range is from a different
gt; range in each of the source workbooks.
gt;
gt; Here's what I want:
gt; If name is in A, but not in B or C or D, I want to know
gt; If name is in B, but not in A or C or D, I want to know
gt; If name is in C, but not in A or B or D, I want to know
gt; If name is in D, but not in A or B or C, I want to know
gt;
gt; Am I asking for 4 conditions, thereby exceeding CF's limit of 3? If so,
gt; I'll accept 3 out of 4 lt;ggt;.
gt;
gt; --
gt; David
--
Dave Peterson
ps.
If you're new to macros, you may want to read David McRitchie's intro at:
www.mvps.org/dmcritchie/excel/getstarted.htm
David wrote:
gt;
gt; Dave Peterson wrote
gt;
gt; gt; Since you have 4 different lists, how about an alternative?
gt; gt;
gt; gt; Create a new sheet (call it sheet2)
gt; gt; Put Name in A1
gt; gt; Copy the 4 lists into column A of this new sheet (one under the other)
gt; gt;
gt; gt; Then select that range (A1:A###)
gt; gt; data|filter|advanced filter
gt; gt; Copy to another location
gt; gt; List range: (should be entered (a1:A###)
gt; gt; copy to: B1
gt; gt; Check Unique records only box
gt; gt;
gt; gt; Now you have a list of unique names in column B.
gt; gt; Delete column A (we're done with it).
gt; gt;
gt; gt; In B1, put: On List 1
gt; gt; In C1, put: On List 2
gt; gt; in D1, put: On List 3
gt; gt; in E1, put: On list 4
gt; gt;
gt; gt; In B2, put this formula:
gt; gt; =isnumber(match(a2,sheet1!a:a,0))
gt; gt;
gt; gt; In C2, put this:
gt; gt; =isnumber(match(a2,sheet1!b:b,0))
gt; gt;
gt; gt; In D2:
gt; gt; =isnumber(match(a2,sheet1!c:c,0))
gt; gt;
gt; gt; In E2:
gt; gt; =isnumber(match(a2,sheet1!D,0))
gt; gt;
gt; gt; And drag down as far as column A extends.
gt; gt;
gt; gt; Now select columns A:E and do Data|Filter|Autofilter.
gt; gt;
gt; gt; You can filter to show the Falses in any column to see where its
gt; gt; missing.
gt;
gt; Well, things broke when I tried to copy the 2nd list (and presumably
gt; would do the same with 3rd and 4th) with a #REF to cells from that 2nd
gt; list, which references cells from a 2nd external file. I didn't pursue
gt; things any further. Remember these lists are quot;builtquot; as a result of links
gt; to external files. If I pasted values, wouldn't I have to repeat your
gt; proposed process each time one or more lists change (quite often)?
gt; Basically this is a test file to insure I have not missed adding/deleting
gt; a name from all 4 files. I can immediately see if things don't match when
gt; length of lists differ, but then have to scan visually to see what name
gt; starts the difference. Additionally each source range is from a different
gt; range in each of the source workbooks.
gt;
gt; Here's what I want:
gt; If name is in A, but not in B or C or D, I want to know
gt; If name is in B, but not in A or C or D, I want to know
gt; If name is in C, but not in A or B or D, I want to know
gt; If name is in D, but not in A or B or C, I want to know
gt;
gt; Am I asking for 4 conditions, thereby exceeding CF's limit of 3? If so,
gt; I'll accept 3 out of 4 lt;ggt;.
gt;
gt; --
gt; 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
gt; Personally, I find Format|Conditional formatting very pretty--but
gt; pretty much useless. You can't (easily) count the missing items; you
gt; can't filter by that conditional formatting color.
gt;
gt; If I had to do it over and over and over, I'd record a macro when I
gt; did those steps (including the paste|special|Values). Then just rerun
gt; that whenever I needed the info.
gt;
gt; This seemed to work ok for me:
gt;
gt; Option Explicit
gt; Sub testme()
gt; Dim CurWks As Worksheet
gt; Dim NewWks As Worksheet
gt; Dim iCol As Long
gt; Dim DestCell As Range
gt; Dim MaxCols As Long
gt; Dim LastRow As Long
gt;
gt; Set CurWks = Worksheets(quot;sheet1quot;)
gt;
gt; Set NewWks = Worksheets.Add
gt; Set DestCell = NewWks.Range(quot;a2quot;)
gt;
gt; With CurWks
gt; MaxCols = .Cells(1, .Columns.Count).End(xlToLeft).Column
gt; For iCol = 1 To MaxCols
gt; .Range(.Cells(2, iCol), .Cells(.Rows.Count,
gt; iCol).End(xlUp)).Copy DestCell.PasteSpecial
gt; Paste:=xlPasteValues With NewWks
gt; Set DestCell = .Cells(.Rows.Count,
gt; quot;Aquot;).End(xlUp).Offset(1, 0)
gt; End With
gt; Next iCol
gt; End With
gt;
gt; With NewWks
gt; .Range(quot;a1quot;).Value = quot;Namequot;
gt; .Range(quot;A1quot;, .Cells(.Rows.Count,
gt; quot;Aquot;).End(xlUp)).AdvancedFilter _
gt; Action:=xlFilterCopy, copytorange:=.Range(quot;B1quot;),
gt; unique:=True
gt;
gt; .Range(quot;a1quot;).EntireColumn.Delete
gt;
gt; .Range(quot;a1quot;).EntireColumn.Sort key1:=.Range(quot;a1quot;), _
gt; order1:=xlAscending,
gt; header:=xlYes
gt;
gt; LastRow = .Cells(.Rows.Count, quot;Aquot;).End(xlUp).Row
gt;
gt; For iCol = 1 To MaxCols
gt; .Cells(1, iCol 1).Value = quot;On List#quot; amp; iCol
gt; With .Range(.Cells(2, iCol 1), .Cells(LastRow, iCol
gt; 1))
gt; .Formula = quot;=isnumber(match(A2,quot; _
gt; amp; CurWks.Columns(iCol).Address(external:=True) amp;
gt; quot;,0))quot;
gt; .Value = .Value
gt; .Replace what:=quot;Truequot;, replacement:=quot;quot;, _
gt; lookat:=xlWhole, MatchCase:=False
gt; .Replace what:=quot;Falsequot;, replacement:=quot;Noquot;, _
gt; lookat:=xlWhole, MatchCase:=False
gt; .HorizontalAlignment = xlCenter
gt; End With
gt; Next iCol
gt;
gt; .Cells(1, MaxCols 2).Value = quot;Count Of No'squot;
gt;
gt; With .Range(.Cells(2, MaxCols 2), .Cells(LastRow, MaxCols
gt; 2))
gt; .Formula = quot;=countif(B2:quot; amp; _
gt; .Parent.Cells(2, MaxCols 1).Address(0, 0) amp;
gt; quot;,quot;quot;noquot;quot;)quot;
gt; .Value = .Value
gt; .HorizontalAlignment = xlCenter
gt; End With
gt;
gt; .Range(quot;a1quot;, .Cells(LastRow, MaxCols 2)).AutoFilter
gt;
gt; Application.Goto .Range(quot;a1quot;), Scroll:=True
gt; .Range(quot;b2quot;).Select
gt; ActiveWindow.FreezePanes = True
gt;
gt; .UsedRange.Columns.AutoFit
gt;
gt; Set DestCell = .UsedRange 'try to reset last used cell
gt;
gt; End With
gt;
gt; End Sub
gt;
gt; David wrote:
gt;gt;
gt;gt; Dave Peterson wrote
gt;gt;
gt;gt; gt; Since you have 4 different lists, how about an alternative?
gt;gt; gt;
gt;gt; gt; Create a new sheet (call it sheet2)
gt;gt; gt; Put Name in A1
gt;gt; gt; Copy the 4 lists into column A of this new sheet (one under the
gt;gt; gt; other)
gt;gt; gt;
gt;gt; gt; Then select that range (A1:A###)
gt;gt; gt; data|filter|advanced filter
gt;gt; gt; Copy to another location
gt;gt; gt; List range: (should be entered (a1:A###)
gt;gt; gt; copy to: B1
gt;gt; gt; Check Unique records only box
gt;gt; gt;
gt;gt; gt; Now you have a list of unique names in column B.
gt;gt; gt; Delete column A (we're done with it).
gt;gt; gt;
gt;gt; gt; In B1, put: On List 1
gt;gt; gt; In C1, put: On List 2
gt;gt; gt; in D1, put: On List 3
gt;gt; gt; in E1, put: On list 4
gt;gt; gt;
gt;gt; gt; In B2, put this formula:
gt;gt; gt; =isnumber(match(a2,sheet1!a:a,0))
gt;gt; gt;
gt;gt; gt; In C2, put this:
gt;gt; gt; =isnumber(match(a2,sheet1!b:b,0))
gt;gt; gt;
gt;gt; gt; In D2:
gt;gt; gt; =isnumber(match(a2,sheet1!c:c,0))
gt;gt; gt;
gt;gt; gt; In E2:
gt;gt; gt; =isnumber(match(a2,sheet1!D,0))
gt;gt; gt;
gt;gt; gt; And drag down as far as column A extends.
gt;gt; gt;
gt;gt; gt; Now select columns A:E and do Data|Filter|Autofilter.
gt;gt; gt;
gt;gt; gt; You can filter to show the Falses in any column to see where its
gt;gt; gt; missing.
gt;gt;
gt;gt; Well, things broke when I tried to copy the 2nd list (and presumably
gt;gt; would do the same with 3rd and 4th) with a #REF to cells from that
gt;gt; 2nd list, which references cells from a 2nd external file. I didn't
gt;gt; pursue things any further. Remember these lists are quot;builtquot; as a
gt;gt; result of links to external files. If I pasted values, wouldn't I
gt;gt; have to repeat your proposed process each time one or more lists
gt;gt; change (quite often)? Basically this is a test file to insure I have
gt;gt; not missed adding/deleting a name from all 4 files. I can immediately
gt;gt; see if things don't match when length of lists differ, but then have
gt;gt; to scan visually to see what name starts the difference. Additionally
gt;gt; each source range is from a different range in each of the source
gt;gt; workbooks.
gt;gt;
gt;gt; Here's what I want:
gt;gt; If name is in A, but not in B or C or D, I want to know
gt;gt; If name is in B, but not in A or C or D, I want to know
gt;gt; If name is in C, but not in A or B or D, I want to know
gt;gt; If name is in D, but not in A or B or C, I want to know
gt;gt;
gt;gt; Am I asking for 4 conditions, thereby exceeding CF's limit of 3? If
gt;gt; so, I'll accept 3 out of 4 lt;ggt;.
gt;gt;
gt;gt; --
gt;gt; David
gt;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:
gt;
gt; Thanks very much for this code. One small detail I've tried to fiddle
gt; with and can't get around. I'm sure it's simple. My lists on Sheet1 each
gt; start in Row2, with Row1 being headers. How to adjust?
gt;
gt; --
gt; David
gt;
gt; Dave Peterson wrote
gt;
gt; gt; Personally, I find Format|Conditional formatting very pretty--but
gt; gt; pretty much useless. You can't (easily) count the missing items; you
gt; gt; can't filter by that conditional formatting color.
gt; gt;
gt; gt; If I had to do it over and over and over, I'd record a macro when I
gt; gt; did those steps (including the paste|special|Values). Then just rerun
gt; gt; that whenever I needed the info.
gt; gt;
gt; gt; This seemed to work ok for me:
gt; gt;
gt; gt; Option Explicit
gt; gt; Sub testme()
gt; gt; Dim CurWks As Worksheet
gt; gt; Dim NewWks As Worksheet
gt; gt; Dim iCol As Long
gt; gt; Dim DestCell As Range
gt; gt; Dim MaxCols As Long
gt; gt; Dim LastRow As Long
gt; gt;
gt; gt; Set CurWks = Worksheets(quot;sheet1quot;)
gt; gt;
gt; gt; Set NewWks = Worksheets.Add
gt; gt; Set DestCell = NewWks.Range(quot;a2quot;)
gt; gt;
gt; gt; With CurWks
gt; gt; MaxCols = .Cells(1, .Columns.Count).End(xlToLeft).Column
gt; gt; For iCol = 1 To MaxCols
gt; gt; .Range(.Cells(2, iCol), .Cells(.Rows.Count,
gt; gt; iCol).End(xlUp)).Copy DestCell.PasteSpecial
gt; gt; Paste:=xlPasteValues With NewWks
gt; gt; Set DestCell = .Cells(.Rows.Count,
gt; gt; quot;Aquot;).End(xlUp).Offset(1, 0)
gt; gt; End With
gt; gt; Next iCol
gt; gt; End With
gt; gt;
gt; gt; With NewWks
gt; gt; .Range(quot;a1quot;).Value = quot;Namequot;
gt; gt; .Range(quot;A1quot;, .Cells(.Rows.Count,
gt; gt; quot;Aquot;).End(xlUp)).AdvancedFilter _
gt; gt; Action:=xlFilterCopy, copytorange:=.Range(quot;B1quot;),
gt; gt; unique:=True
gt; gt;
gt; gt; .Range(quot;a1quot;).EntireColumn.Delete
gt; gt;
gt; gt; .Range(quot;a1quot;).EntireColumn.Sort key1:=.Range(quot;a1quot;), _
gt; gt; order1:=xlAscending,
gt; gt; header:=xlYes
gt; gt;
gt; gt; LastRow = .Cells(.Rows.Count, quot;Aquot;).End(xlUp).Row
gt; gt;
gt; gt; For iCol = 1 To MaxCols
gt; gt; .Cells(1, iCol 1).Value = quot;On List#quot; amp; iCol
gt; gt; With .Range(.Cells(2, iCol 1), .Cells(LastRow, iCol
gt; gt; 1))
gt; gt; .Formula = quot;=isnumber(match(A2,quot; _
gt; gt; amp; CurWks.Columns(iCol).Address(external:=True) amp;
gt; gt; quot;,0))quot;
gt; gt; .Value = .Value
gt; gt; .Replace what:=quot;Truequot;, replacement:=quot;quot;, _
gt; gt; lookat:=xlWhole, MatchCase:=False
gt; gt; .Replace what:=quot;Falsequot;, replacement:=quot;Noquot;, _
gt; gt; lookat:=xlWhole, MatchCase:=False
gt; gt; .HorizontalAlignment = xlCenter
gt; gt; End With
gt; gt; Next iCol
gt; gt;
gt; gt; .Cells(1, MaxCols 2).Value = quot;Count Of No'squot;
gt; gt;
gt; gt; With .Range(.Cells(2, MaxCols 2), .Cells(LastRow, MaxCols
gt; gt; 2))
gt; gt; .Formula = quot;=countif(B2:quot; amp; _
gt; gt; .Parent.Cells(2, MaxCols 1).Address(0, 0) amp;
gt; gt; quot;,quot;quot;noquot;quot;)quot;
gt; gt; .Value = .Value
gt; gt; .HorizontalAlignment = xlCenter
gt; gt; End With
gt; gt;
gt; gt; .Range(quot;a1quot;, .Cells(LastRow, MaxCols 2)).AutoFilter
gt; gt;
gt; gt; Application.Goto .Range(quot;a1quot;), Scroll:=True
gt; gt; .Range(quot;b2quot;).Select
gt; gt; ActiveWindow.FreezePanes = True
gt; gt;
gt; gt; .UsedRange.Columns.AutoFit
gt; gt;
gt; gt; Set DestCell = .UsedRange 'try to reset last used cell
gt; gt;
gt; gt; End With
gt; gt;
gt; gt; End Sub
gt; gt;
gt; gt; David wrote:
gt; gt;gt;
gt; gt;gt; Dave Peterson wrote
gt; gt;gt;
gt; gt;gt; gt; Since you have 4 different lists, how about an alternative?
gt; gt;gt; gt;
gt; gt;gt; gt; Create a new sheet (call it sheet2)
gt; gt;gt; gt; Put Name in A1
gt; gt;gt; gt; Copy the 4 lists into column A of this new sheet (one under the
gt; gt;gt; gt; other)
gt; gt;gt; gt;
gt; gt;gt; gt; Then select that range (A1:A###)
gt; gt;gt; gt; data|filter|advanced filter
gt; gt;gt; gt; Copy to another location
gt; gt;gt; gt; List range: (should be entered (a1:A###)
gt; gt;gt; gt; copy to: B1
gt; gt;gt; gt; Check Unique records only box
gt; gt;gt; gt;
gt; gt;gt; gt; Now you have a list of unique names in column B.
gt; gt;gt; gt; Delete column A (we're done with it).
gt; gt;gt; gt;
gt; gt;gt; gt; In B1, put: On List 1
gt; gt;gt; gt; In C1, put: On List 2
gt; gt;gt; gt; in D1, put: On List 3
gt; gt;gt; gt; in E1, put: On list 4
gt; gt;gt; gt;
gt; gt;gt; gt; In B2, put this formula:
gt; gt;gt; gt; =isnumber(match(a2,sheet1!a:a,0))
gt; gt;gt; gt;
gt; gt;gt; gt; In C2, put this:
gt; gt;gt; gt; =isnumber(match(a2,sheet1!b:b,0))
gt; gt;gt; gt;
gt; gt;gt; gt; In D2:
gt; gt;gt; gt; =isnumber(match(a2,sheet1!c:c,0))
gt; gt;gt; gt;
gt; gt;gt; gt; In E2:
gt; gt;gt; gt; =isnumber(match(a2,sheet1!D,0))
gt; gt;gt; gt;
gt; gt;gt; gt; And drag down as far as column A extends.
gt; gt;gt; gt;
gt; gt;gt; gt; Now select columns A:E and do Data|Filter|Autofilter.
gt; gt;gt; gt;
gt; gt;gt; gt; You can filter to show the Falses in any column to see where its
gt; gt;gt; gt; missing.
gt; gt;gt;
gt; gt;gt; Well, things broke when I tried to copy the 2nd list (and presumably
gt; gt;gt; would do the same with 3rd and 4th) with a #REF to cells from that
gt; gt;gt; 2nd list, which references cells from a 2nd external file. I didn't
gt; gt;gt; pursue things any further. Remember these lists are quot;builtquot; as a
gt; gt;gt; result of links to external files. If I pasted values, wouldn't I
gt; gt;gt; have to repeat your proposed process each time one or more lists
gt; gt;gt; change (quite often)? Basically this is a test file to insure I have
gt; gt;gt; not missed adding/deleting a name from all 4 files. I can immediately
gt; gt;gt; see if things don't match when length of lists differ, but then have
gt; gt;gt; to scan visually to see what name starts the difference. Additionally
gt; gt;gt; each source range is from a different range in each of the source
gt; gt;gt; workbooks.
gt; gt;gt;
gt; gt;gt; Here's what I want:
gt; gt;gt; If name is in A, but not in B or C or D, I want to know
gt; gt;gt; If name is in B, but not in A or C or D, I want to know
gt; gt;gt; If name is in C, but not in A or B or D, I want to know
gt; gt;gt; If name is in D, but not in A or B or C, I want to know
gt; gt;gt;
gt; gt;gt; Am I asking for 4 conditions, thereby exceeding CF's limit of 3? If
gt; gt;gt; so, I'll accept 3 out of 4 lt;ggt;.
gt; gt;gt;
gt; gt;gt; --
gt; gt;gt; David
gt; gt;
--
Dave Peterson
Dave Peterson wrote
gt; That's the way the code was written--using row 1 as headers and data
gt; starting in row 2.
gt;
gt; 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 amp; F2 on Sheet2 wind up with a 0 (zero) in them. Could
it be because I have an equal number of rows of quot;paddingquot; 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
gt; That's the way the code was written--using row 1 as headers and data
gt; starting in row 2.
gt;
gt; 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=quot;quot;,quot;quot;,sheet1!a1)
Then my quot;paddedquot; range looks empty. This won't change the results of the
macro--you'll see blanks instead of 0's, though.David wrote:
gt;
gt; Dave Peterson wrote
gt;
gt; gt; That's the way the code was written--using row 1 as headers and data
gt; gt; starting in row 2.
gt; gt;
gt; gt; Did you have trouble when you tried it?
gt;
gt; Ok, as an experiment, I deleted the rows that evaluated to zero and things
gt; on Sheet2 wind up fine now. Would like to keep the padded range, though, so
gt; I may have to live with the zero's.
gt;
gt; --
gt; David
--
Dave Peterson
留言列表
