Vlookup for two sheets in same workbook
Ok. I am desparate. I've working on this for hours;
I have VIN numbers in sheet 1 in the range I2:I5521 I have VIN numbers in sheet 2 in the range G2:G4717. I need to see if the VIN numbers in sheet 2 are on sheet 1, of so highlight the cell or delete the VIN number. I'd really like to delete the duplicate. |
Roberta
select column G in the second worksheet and name it "ColumnG" without the quotes. Then, select cells I2 down to I5521 in the first worksheet and choose Format Conditional Formatting... Change Condition 1 to: Formula: =VLOOKUP(I2,ColumnG,1,FALSE)=I2 choose a suitable background colour to highlight the cell. You can then manually delete the cells/rows where the duplicate exists. Alternatively, you could just use the lookup in another column and then filter for TRUE on that column. Regards Trevor "Roberta" wrote in message ... Ok. I am desparate. I've working on this for hours; I have VIN numbers in sheet 1 in the range I2:I5521 I have VIN numbers in sheet 2 in the range G2:G4717. I need to see if the VIN numbers in sheet 2 are on sheet 1, of so highlight the cell or delete the VIN number. I'd really like to delete the duplicate. |
Hi Trevor. I intentionally placed a duplicate VIN no in sheet 2 and followed
your instructions and nothing showed up as duplicate. In the conditional formatting where the Formula Is: =vlookup(I2,ColumnG,1,False)=I2 Its stating look in I2 column and Column G, if data matches? Not sure I can interpret the formula. Should False be true? Why just I2? "Trevor Shuttleworth" wrote: Roberta select column G in the second worksheet and name it "ColumnG" without the quotes. Then, select cells I2 down to I5521 in the first worksheet and choose Format Conditional Formatting... Change Condition 1 to: Formula: =VLOOKUP(I2,ColumnG,1,FALSE)=I2 choose a suitable background colour to highlight the cell. You can then manually delete the cells/rows where the duplicate exists. Alternatively, you could just use the lookup in another column and then filter for TRUE on that column. Regards Trevor "Roberta" wrote in message ... Ok. I am desparate. I've working on this for hours; I have VIN numbers in sheet 1 in the range I2:I5521 I have VIN numbers in sheet 2 in the range G2:G4717. I need to see if the VIN numbers in sheet 2 are on sheet 1, of so highlight the cell or delete the VIN number. I'd really like to delete the duplicate. |
Ok...I replied too soon. I got it. What I did was selected the entire
column G vs the cells in Col G that had data. Thanks for saving me. "Trevor Shuttleworth" wrote: Roberta select column G in the second worksheet and name it "ColumnG" without the quotes. Then, select cells I2 down to I5521 in the first worksheet and choose Format Conditional Formatting... Change Condition 1 to: Formula: =VLOOKUP(I2,ColumnG,1,FALSE)=I2 choose a suitable background colour to highlight the cell. You can then manually delete the cells/rows where the duplicate exists. Alternatively, you could just use the lookup in another column and then filter for TRUE on that column. Regards Trevor "Roberta" wrote in message ... Ok. I am desparate. I've working on this for hours; I have VIN numbers in sheet 1 in the range I2:I5521 I have VIN numbers in sheet 2 in the range G2:G4717. I need to see if the VIN numbers in sheet 2 are on sheet 1, of so highlight the cell or delete the VIN number. I'd really like to delete the duplicate. |
All times are GMT +1. The time now is 09:10 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com