Vlookup for two sheets in same workbook

[Roberta]

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.

[boo_hiss_boo]

Roberta... try this

in the cell in your second sheet type this

ISERROR(VLOOKUP(A4,Sheet1!$I$1:$I,1,FALSE))

and then copy your formula down, this should give you true or false all the way down

then in the cell next to that type

=(IF(H1=FALSE,"",H1))

this will then give you a remaining list in your 3rd column to copy & paste special and become a replacement list to you original one

Hope this helps

Boo

[Trevor Shuttleworth]

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" <[email protected]> wrote in message
news:[email protected]...
> 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]

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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]

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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.
>
>
>