need to compare two lists and identify cells with same numbers in

I receive a list of numberic incident numbers every day and need to write a
macro to compare yesterdays list to todays list to identify which are new
numbers. Would also like to run it the other way round on yesterdays list to
identify which numbers are missing from todays list so that I can easily
check that calls have been closed properly.

Assuming that the data is on Sheet1 and Sheet2 in column A, this creates
conditional formatting for it

Sub Duplicates()
Dim iLastRow As Long
ActiveWorkbook.Names.Add Name:="Sheet2A", RefersToR1C1:="=Sheet2!C1"
With Worksheets("Sheet1")
iLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
With .Range("A1:A" & iLastRow)
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression,
Formula1:="=COUNTIF(Sheet2A,A1)>0"
.FormatConditions(1).Interior.ColorIndex = 3
End With
End With
ActiveWorkbook.Names.Add Name:="Sheet1A", RefersToR1C1:="=Sheet1!C1"
With Worksheets("Sheet2")
iLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
With .Range("A1:A" & iLastRow)
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression,
Formula1:="=COUNTIF(Sheet1A,A1)>0"
.FormatConditions(1).Interior.ColorIndex = 3
End With
End With
End Sub


--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

"Bockhamptoner" <Bockhamptoner@discussions.microsoft.com> wrote in message
news:0C6469F2-2442-4232-98A1-77598ABDF156@microsoft.com...
> I receive a list of numberic incident numbers every day and need to write
a
> macro to compare yesterdays list to todays list to identify which are new
> numbers. Would also like to run it the other way round on yesterdays list
to
> identify which numbers are missing from todays list so that I can easily
> check that calls have been closed properly.

Thank you Bob.
I have always considered myself a fairly ok sort of basic user in Excel but
I'm lost with the answer. I can see the gist of this will probably colour the
text differently, which would be perfect but what do I 'do' with the words
you've typed? how do I get them into the spreadsheet? and I think I can see
'counting' going on which will tell it how many iterations down the sheet to
go, is this correct?

"Bob Phillips" wrote:

> Assuming that the data is on Sheet1 and Sheet2 in column A, this creates
> conditional formatting for it
>
> Sub Duplicates()
> Dim iLastRow As Long
> ActiveWorkbook.Names.Add Name:="Sheet2A", RefersToR1C1:="=Sheet2!C1"
> With Worksheets("Sheet1")
> iLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
> With .Range("A1:A" & iLastRow)
> .FormatConditions.Delete
> .FormatConditions.Add Type:=xlExpression,
> Formula1:="=COUNTIF(Sheet2A,A1)>0"
> .FormatConditions(1).Interior.ColorIndex = 3
> End With
> End With
> ActiveWorkbook.Names.Add Name:="Sheet1A", RefersToR1C1:="=Sheet1!C1"
> With Worksheets("Sheet2")
> iLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
> With .Range("A1:A" & iLastRow)
> .FormatConditions.Delete
> .FormatConditions.Add Type:=xlExpression,
> Formula1:="=COUNTIF(Sheet1A,A1)>0"
> .FormatConditions(1).Interior.ColorIndex = 3
> End With
> End With
> End Sub
>
>
> --
> HTH
>
> Bob Phillips
>
> (remove xxx from email address if mailing direct)
>
> "Bockhamptoner" <Bockhamptoner@discussions.microsoft.com> wrote in message
> news:0C6469F2-2442-4232-98A1-77598ABDF156@microsoft.com...
> > I receive a list of numberic incident numbers every day and need to write
> a
> > macro to compare yesterdays list to todays list to identify which are new
> > numbers. Would also like to run it the other way round on yesterdays list
> to
> > identify which numbers are missing from todays list so that I can easily
> > check that calls have been closed properly.
>
>
>

This isn't a macro but you should be able to apply it ...

Assume your two lists are in columns A and D. Insert this formula is cell B1 and copy down.

=COUNTIF(D:D,A1)

That will give zero for any item on list A that is not on list B, otherwise it will give the number of times it appears on list B.

Perhaps a non-array formulas play to try as well ..

Assume data in cols A and B, from row1 down

1111 1117
1112 1113
1113 1115
1114 1116
1115 1111
1116 1119
1117 1120
1118 1114
1121
1122

where
Col A = Yesterday's data, within A1:A8 (say)
Col B = Today's data. within B1:B10 (say)

To compare col A against col B:

Put in C1:
=IF(A1="","",IF(ISNUMBER(MATCH(A1,B:B,0)),"",ROW()))

Put in D1:
=IF(ISERROR(SMALL(C:C,ROW(A1))),"",INDEX(A:A,MATCH(SMALL(C:C,ROW(A1)),C:C,0)))

Select C1:D1, fill down to the last row of data in col A

Col D returns items in col A which are not in col B,
all neatly bunched at the top, viz.:

1112
1118

Then, to compare col B against col A:

Put in E1:
=IF(B1="","",IF(ISNUMBER(MATCH(B1,A:A,0)),"",ROW()))

In F1:
=IF(ISERROR(SMALL(E:E,ROW(A1))),"",INDEX(B:B,MATCH(SMALL(E:E,ROW(A1)),E:E,0)))

Select E1:F1, fill down to the last row of data in col B

Col F will return items in col B which are not in col A,
again all neatly bunched at the top, viz:

1119
1120
1121
1122

--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Bockhamptoner" wrote:
> I receive a list of numberic incident numbers every day and need to write a
> macro to compare yesterdays list to todays list to identify which are new
> numbers. Would also like to run it the other way round on yesterdays list to
> identify which numbers are missing from todays list so that I can easily
> check that calls have been closed properly.

Assuming that my assumptions are correct (two worksheets, Sheet1 and Sheet2,
and the data in column A in both), just add this code by going into the
VBIDE (Alt-F11), insert a code module (Insert>Module), then copy my code. Go
back to Excel, then run the macro (Tools>Macro>Macros..., which brings up a
dialog, select Duplicates from this list, and hit Run). You should then see
any duplicated data in red background.

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

"Bockhamptoner" <Bockhamptoner@discussions.microsoft.com> wrote in message
news:EF64341F-82BB-441E-BC79-959C545933B6@microsoft.com...
> Thank you Bob.
> I have always considered myself a fairly ok sort of basic user in Excel
but
> I'm lost with the answer. I can see the gist of this will probably colour
the
> text differently, which would be perfect but what do I 'do' with the words
> you've typed? how do I get them into the spreadsheet? and I think I can
see
> 'counting' going on which will tell it how many iterations down the sheet
to
> go, is this correct?
>
> "Bob Phillips" wrote:
>
> > Assuming that the data is on Sheet1 and Sheet2 in column A, this creates
> > conditional formatting for it
> >
> > Sub Duplicates()
> > Dim iLastRow As Long
> > ActiveWorkbook.Names.Add Name:="Sheet2A", RefersToR1C1:="=Sheet2!C1"
> > With Worksheets("Sheet1")
> > iLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
> > With .Range("A1:A" & iLastRow)
> > .FormatConditions.Delete
> > .FormatConditions.Add Type:=xlExpression,
> > Formula1:="=COUNTIF(Sheet2A,A1)>0"
> > .FormatConditions(1).Interior.ColorIndex = 3
> > End With
> > End With
> > ActiveWorkbook.Names.Add Name:="Sheet1A", RefersToR1C1:="=Sheet1!C1"
> > With Worksheets("Sheet2")
> > iLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
> > With .Range("A1:A" & iLastRow)
> > .FormatConditions.Delete
> > .FormatConditions.Add Type:=xlExpression,
> > Formula1:="=COUNTIF(Sheet1A,A1)>0"
> > .FormatConditions(1).Interior.ColorIndex = 3
> > End With
> > End With
> > End Sub
> >
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > (remove xxx from email address if mailing direct)
> >
> > "Bockhamptoner" <Bockhamptoner@discussions.microsoft.com> wrote in
message
> > news:0C6469F2-2442-4232-98A1-77598ABDF156@microsoft.com...
> > > I receive a list of numberic incident numbers every day and need to
write
> > a
> > > macro to compare yesterdays list to todays list to identify which are
new
> > > numbers. Would also like to run it the other way round on yesterdays
list
> > to
> > > identify which numbers are missing from todays list so that I can
easily
> > > check that calls have been closed properly.
> >
> >
> >

is this function case sensitive? I am tryng to compare two directories. I
used a program to list all the files, then imported to excel. I then
formatted the data to make it comparable, but I was not case sensitive. How
do I find files that are in one directory and not the other, then subtract
another list from that?

"germullen" wrote:

>
> This isn't a macro but you should be able to apply it ...
>
> Assume your two lists are in columns A and D. Insert this formula is
> cell B1 and copy down.
>
> =COUNTIF(D:D,A1)
>
> That will give zero for any item on list A that is not on list B,
> otherwise it will give the number of times it appears on list B.
>
>
> --
> germullen
> ------------------------------------------------------------------------
> germullen's Profile: http://www.excelforum.com/member.php...o&userid=30626
> View this thread: http://www.excelforum.com/showthread...hreadid=538470
>
>

=countif() is not case sensitive.

And Chip Pearson has lots of techniques to work with duplicates:
http://www.cpearson.com/excel/duplicat.htm

recordspecialist wrote:
>
> is this function case sensitive? I am tryng to compare two directories. I
> used a program to list all the files, then imported to excel. I then
> formatted the data to make it comparable, but I was not case sensitive. How
> do I find files that are in one directory and not the other, then subtract
> another list from that?
>
> "germullen" wrote:
>
> >
> > This isn't a macro but you should be able to apply it ...
> >
> > Assume your two lists are in columns A and D. Insert this formula is
> > cell B1 and copy down.
> >
> > =COUNTIF(D:D,A1)
> >
> > That will give zero for any item on list A that is not on list B,
> > otherwise it will give the number of times it appears on list B.
> >
> >
> > --
> > germullen
> > ------------------------------------------------------------------------
> > germullen's Profile: http://www.excelforum.com/member.php...o&userid=30626
> > View this thread: http://www.excelforum.com/showthread...hreadid=538470
> >
> >

--

Dave Peterson