| Home | Forums | Reviews | Articles | Register |
 |
| Thread Tools | Rate Thread |
|
Guest
Posts: n/a
|
|
|
|
||
|
|
| |
|
Guest
Posts: n/a
|
|
|
|
|
||
|
Guest
Posts: n/a
|
|
|
|
|
||
|
Guest
Posts: n/a
|
|
|
|
|
||
|
Guest
Posts: n/a
|
|
|
|
|
||
|
Guest
Posts: n/a
|
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" <(E-Mail Removed)> escreveu na mensagem news  180AFC1-F09A-43F1-9BC1-(E-Mail Removed)...> 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(Sheet2!$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(Sheet2!$A$2:$D$1142,MATCH(Sheet2!$B2,Sheet2!$B$2:$B$1142,0),COLUMN()),"") >> >> Copy this formula until column D and row 1200 >> >> >> -- >> >> Rodrigo Ferreira >> Regards from Brazil >> >> >> "Lillian Lian" <(E-Mail Removed)> escreveu na >> mensagem >> news:27C3F209-6598-4A65-B9AA-(E-Mail Removed)... >> > 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(Sheet2!$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(Sheet2!$A$2:$D$1142,MATCH(Sheet2!$B2,Sheet2!$B$2:$B$1142,0),COLUMN()),"") >> >> >> >> Copy to other cells >> >> >> >> -- >> >> >> >> Rodrigo Ferreira >> >> Regards from Brazil >> >> >> >> >> >> "Lillian Lian" <(E-Mail Removed)> escreveu na >> >> mensagem >> >> news:ABC894F4-C022-42DE-AD29-(E-Mail Removed)... >> >> >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 >> >> > >> >> >> >> >> >> >> >> >> |
|
|
|
|
|||
|
|
| |
|
| Thread Tools | |
| Rate This Thread | |
|
|
Similar Threads
|
||||
| Thread | Thread Starter | Forum | Replies | Last Post |
| Match excel spread sheet with macro | Lillian Lian | Microsoft Excel Programming | 2 | 7th Dec 2007 03:29 PM |
| Match two excel spread sheet using Macro | =?Utf-8?B?TGlsbGlhbiBMaWFu?= | Microsoft Excel Programming | 3 | 26th Sep 2007 02:48 AM |
| match two excel spread sheet using macro | =?Utf-8?B?TGlsbGlhbiBMaWFu?= | Microsoft Excel Programming | 11 | 19th Sep 2007 03:17 PM |
| can I clean old excel data format with macro on funny spread sheet | =?Utf-8?B?VG9kZCBGLg==?= | Microsoft Excel Programming | 0 | 22nd Jul 2005 09:15 PM |
| is there anyway to make it so the users of my excel spread sheet cant view the macro code w/o a password? | Daniel | Microsoft Excel Discussion | 1 | 24th Jun 2005 12:26 AM |
