Comparision of values between files
Closed
saru
-
Jun 19, 2010 at 05:51 AM
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 - Jun 20, 2010 at 12:07 PM
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 - Jun 20, 2010 at 12:07 PM
Related:
- Comparision of values between files
- Clear recent files windows 11 - Guide
- Recover my files - Download - Backup and recovery
- Show hidden files android - Guide
- Forfiles move files older than - Guide
- How to delete multiple files at once - Guide
3 responses
venkat1926
Posts
1863
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
811
Jun 19, 2010 at 07:21 AM
Jun 19, 2010 at 07:21 AM
1. please do not mention the meber who needs to answer. anybody who can give the answer will give it
2. you must give complete information. in each workbook in which column the data is entred.
I made two sample workbooks. "book3" and "book4"
the data in the first workbook in sheet1 in column A from A1 down are entries
1
2
3
4
5
in the second workbooks called "book4" in sheet1 column A the data from A1 entered are as follows
62
80
90
69
98
21
33
93
96
1
60
46
39
88
54
80
68
36
9
66
now in such pair of sample workbooks
the following macro "test" will give the result (the second macro undo will undo the result of the macro. you an park these two macros in the mdoules in the vbeditor of either of the workbooks.
KEEP BOTH THE WORKBOOKS OPEN. i PRESUME BOTH THE BOOKS ARE SAVED WORKBOOKS.
The result will be in sheet1 of ook3 entries 2,3,4,5 are colored red because they are not avaiable in book4 and also less than 5 digits.
the macros are :
========================
Sub test()
Dim r As Range, c As Range
Dim x, cfind As Range
With Workbooks("book3.xls").Worksheets("sheet1").Columns("A:A")
Set r = Range(.Range("A1"), .Range("A1").End(xlDown))
For Each c In r
x = c.Value
With Workbooks("book4.xls").Worksheets("sheet1").Columns("A:A")
Set cfind = .Cells.Find(what:=x, lookat:=xlWhole)
If Not cfind Is Nothing Then GoTo nextc
End With
If Len(c) < 5 Then c.Interior.ColorIndex = 3
nextc:
Next c
End With
End Sub
==========================
Sub undo()
With Workbooks("book3.xls").Worksheets("sheet1")
.Cells.Interior.ColorIndex = xlNone
End With
End Sub
====================
modify sutiably
your comments please
2. you must give complete information. in each workbook in which column the data is entred.
I made two sample workbooks. "book3" and "book4"
the data in the first workbook in sheet1 in column A from A1 down are entries
1
2
3
4
5
in the second workbooks called "book4" in sheet1 column A the data from A1 entered are as follows
62
80
90
69
98
21
33
93
96
1
60
46
39
88
54
80
68
36
9
66
now in such pair of sample workbooks
the following macro "test" will give the result (the second macro undo will undo the result of the macro. you an park these two macros in the mdoules in the vbeditor of either of the workbooks.
KEEP BOTH THE WORKBOOKS OPEN. i PRESUME BOTH THE BOOKS ARE SAVED WORKBOOKS.
The result will be in sheet1 of ook3 entries 2,3,4,5 are colored red because they are not avaiable in book4 and also less than 5 digits.
the macros are :
========================
Sub test()
Dim r As Range, c As Range
Dim x, cfind As Range
With Workbooks("book3.xls").Worksheets("sheet1").Columns("A:A")
Set r = Range(.Range("A1"), .Range("A1").End(xlDown))
For Each c In r
x = c.Value
With Workbooks("book4.xls").Worksheets("sheet1").Columns("A:A")
Set cfind = .Cells.Find(what:=x, lookat:=xlWhole)
If Not cfind Is Nothing Then GoTo nextc
End With
If Len(c) < 5 Then c.Interior.ColorIndex = 3
nextc:
Next c
End With
End Sub
==========================
Sub undo()
With Workbooks("book3.xls").Worksheets("sheet1")
.Cells.Interior.ColorIndex = xlNone
End With
End Sub
====================
modify sutiably
your comments please
venkat1926
Posts
1863
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
811
Jun 20, 2010 at 06:30 AM
Jun 20, 2010 at 06:30 AM
suppose the file abc-sheset1 has larged number of numbers in MANY columns
sample sheet
29 2 71 41 22
41 49 10 90 16
34 35 28 98 6
35 81 23 11 44
62 7 86 12 2
KEEP THIS FILE (WHICH IS A SAVED FILE) OPEN
inthe file def right click the tab of sheet1 and click view code
in the resuting window copy this EVENT CODE
now go to sheet1 of file def in any cell in column C type any number and see
1. if that number is availabe in sheet1 of the file abc NOTHING will happen
2. in case that number you have entered is not available in the file abc sheet1 then error message will come up
In the EVENT CODE there is a statement
If Target.Column <> 3 Then Exit Sub
the number 3 here refers to column C. If the columnin which you want to enter numbers in shseet of file def is different modify this statement.
The purpose of this statement is if you enter some number in SOME OTHER COLUMN nothing will happen whther that number is or is not available in sheet1 of abc.
sample sheet
29 2 71 41 22
41 49 10 90 16
34 35 28 98 6
35 81 23 11 44
62 7 86 12 2
KEEP THIS FILE (WHICH IS A SAVED FILE) OPEN
inthe file def right click the tab of sheet1 and click view code
in the resuting window copy this EVENT CODE
Private Sub Worksheet_Change(ByVal Target As Range)
Dim cfind As Range, r As Range, x
If Target.Column <> 3 Then Exit Sub
x = Target.Value
Set r = Workbooks("abc.xls").Worksheets("sheet1").UsedRange
Set cfind = r.Cells.Find(what:=x, lookat:=xlWhole)
If Not cfind Is Nothing Then
Exit Sub
Else
MsgBox "number not in the database"
End If
End Sub
now go to sheet1 of file def in any cell in column C type any number and see
1. if that number is availabe in sheet1 of the file abc NOTHING will happen
2. in case that number you have entered is not available in the file abc sheet1 then error message will come up
In the EVENT CODE there is a statement
If Target.Column <> 3 Then Exit Sub
the number 3 here refers to column C. If the columnin which you want to enter numbers in shseet of file def is different modify this statement.
The purpose of this statement is if you enter some number in SOME OTHER COLUMN nothing will happen whther that number is or is not available in sheet1 of abc.
venkat1926
Posts
1863
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
811
Jun 20, 2010 at 12:07 PM
Jun 20, 2010 at 12:07 PM
the event code should be parked in the sheet code where there is NO data.. the name of the file is not relevant
The other file where there is huge data should be called "abc.xls". if this name is different change in the following statement
Set r = Workbooks("abc.xls").Worksheets("sheet1").UsedRange
at present work with both the files open
It looks to get information or referring to a closed file you may have to use a function. but this method has some limitations. So it is easier to get the information from the open files. The only thing you can do is open the file and after the event code statements are invoked you can close the file automatically by statements in the same event code. I wonder why this tortuous way when it is easier to keep the file open. If you have specific difficulty in keeping the file open I can give you a few more statements to open the file and close the file . If necessary revert back to the newsgroup.
The other file where there is huge data should be called "abc.xls". if this name is different change in the following statement
Set r = Workbooks("abc.xls").Worksheets("sheet1").UsedRange
at present work with both the files open
It looks to get information or referring to a closed file you may have to use a function. but this method has some limitations. So it is easier to get the information from the open files. The only thing you can do is open the file and after the event code statements are invoked you can close the file automatically by statements in the same event code. I wonder why this tortuous way when it is easier to keep the file open. If you have specific difficulty in keeping the file open I can give you a few more statements to open the file and close the file . If necessary revert back to the newsgroup.
Jun 20, 2010 at 04:10 AM
I am getting an error when i am doing the above mentioned code.
My requirement is if in an excel file(abc) i have a base data of huge numbers
and in another excel file (def) in a particular column (ex: Column c) i enter a number so the macro should check the number entered by me in the base file (1st file) and through an error if the number does not match the base file.
The error message should pop up saying number incorrect / number not present in database.
Could you help me on this.
Jun 20, 2010 at 07:01 AM
Also to perform this activity each time do i have to keep both workbooks open?
Is there a way that i can keep the base file and the file i want to match in one folder, so that any user enteres a number if it matches the databse "OK" else it should pop up an error.
Could you help on this please