Compare two excel spread sheet with macro
Lilian,
I'll try to explain:
You have sheet1 with 947 records (the name of this sheet is "Sheet1")
You have sheet2 with 1142 records (the name of this sheet is "Sheet2")
The names have to be Sheet1 and Sheet2 or you will have to change the
formulas.
Insert 2 new Sheets:
The first will have the rows that MATCH and the other NOT MATCH
In the first new sheet, on cell A2 paste this formula:
=IF(ISERROR(INDEX(Sheet2!$A$2:$D$1142,MATCH(Sheet2 !$B2,Sheet1!$B$2:$B$1142,0),COLUMN())),"",INDEX(Sh eet2!$A$2:$D$1142,MATCH(Sheet2!$B2,Sheet1!$B$2:$B$ 1142,0),COLUMN()))
Copy this formula until column D and row 1200
In the second new sheet, on cell A2 paste this formula:
=IF(ISERROR(INDEX(Sheet2!$A$2:$D$1142,MATCH(Sheet2 !$B2,Sheet1!$B$2:$B$1142,0),COLUMN())),INDEX(Sheet 2!$A$2:$D$1142,MATCH(Sheet2!$B2,Sheet2!$B$2:$B$114 2,0),COLUMN()),"")
Copy this formula until column D and row 1200
--
Rodrigo Ferreira
Regards from Brazil
"Lillian Lian" escreveu na mensagem
...
Rodrigo,
I don't quite understand how to do this? can you explain more detail
step
by step?
Thank you for all the help
Lillian
"Rodrigo Ferreira" wrote:
You can use something like this:
Sheet3 (A2):
=IF(ISERROR(INDEX(Sheet2!$A$2:$D$1142,MATCH(Sheet2 !$B2,Sheet1!$B$2:$B$1142,0),COLUMN())),"",INDEX(Sh eet2!$A$2:$D$1142,MATCH(Sheet2!$B2,Sheet1!$B$2:$B$ 1142,0),COLUMN()))
Sheet4 (A2):
=IF(ISERROR(INDEX(Sheet2!$A$2:$D$1142,MATCH(Sheet2 !$B2,Sheet1!$B$2:$B$1142,0),COLUMN())),INDEX(Sheet 2!$A$2:$D$1142,MATCH(Sheet2!$B2,Sheet2!$B$2:$B$114 2,0),COLUMN()),"")
Copy to other cells
--
Rodrigo Ferreira
Regards from Brazil
"Lillian Lian" escreveu na
mensagem
...
I have one excel spreed that has sheet1 and sheet2, both has same like
this
ColumnA ColumnB ColumnC ColumnC
name SSN domain users
sheet1 has 947 records
sheet2 has 1142 records
I would like match SSN on columnB,
if they are match then move all the name, SSN, domain, users to sheet3.
if they are not match then write all the name, SSN, domain, users to
sheet4
thanks for all the help
Lillian
|