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" wrote in message
...
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" wrote in message
...
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.


--
germullen
------------------------------------------------------------------------
germullen's Profile: http://www.excelforum.com/member.php...o&userid=30626
View this thread: http://www.excelforum.com/showthread...hreadid=538470

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 (InsertModule), then copy my code. Go
back to Excel, then run the macro (ToolsMacroMacros..., 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" wrote in message
...
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" wrote in
message
...
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

Bob,

How if i have a list of table(DB)which included 4 fields(A:column A,B:Column
B,C:Column C,D:Column D) in sheet 1 with title & related data in each rows.

I have created another sheet(sheet 2) with the same 4 fields(A:Column
D,B:column E,C:column F,D:column G) without data but same title.

in sheet 2, Column A,B,C is user input to match the data from sheet 1. if
found matching data, all will be duplicated/copied over to the related column
from column D-column G)

the problem i have now is i have a DB table (which stored 3 thousands plus
data)started from column A-E.
1.Country(column A)
2.Postcode(Column B)
3.City(Column C)
4.Street(column D)
5.House Number(column E)

there is another table (started column G- M)to populate result based on the
user key in. User key in column will be
1. Postcode(column G)
2. City (column H)
3. Street (Column I)

If the user keys in 'Postcode' in column G. it should lookup at column B for
matching data. If user keys in 'AB10 7FJ', then all matching postcode&data
from column B & the rows which have same postcode should be populated in the
matching result table which is
1.Postcode(Column J)
2.City(Column K)
3.Street(column L)
4.House Number(column M)

Could you advice how should this be done?


any help is highly appreciated,

thanks