|
Compare two excel spread sheet with macro
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 |
Compare two excel spread sheet with macro
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 |
Compare two excel spread sheet with macro
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 |
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 |
Compare two excel spread sheet with macro
Rodrigo,
How to copy this formula until column D and row 1200, I hight light the A2 to D2 and all the way down to 1200 row, and copy and paste, but nothing happen, am I doing something wrong? Lillian "Rodrigo Ferreira" wrote: 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 |
Compare two excel spread sheet with macro
Copy only A2 and paste to all the others cells (B2, C2, D2, B3, C3, ...,
D1200) The blank rows in the first new sheet will indicate that that row not match (this row is in the second sheet) The blank rows in the second new sheet will indicate that that row match (this row is in the first sheet) -- Rodrigo Ferreira Regards from Brazil "Lillian Lian" escreveu na mensagem ... Rodrigo, How to copy this formula until column D and row 1200, I hight light the A2 to D2 and all the way down to 1200 row, and copy and paste, but nothing happen, am I doing something wrong? Lillian "Rodrigo Ferreira" wrote: 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 |
| All times are GMT +1. The time now is 11:36 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com