How do i check if something non numeric is in a range & return'Y'

amung the ways would be conditional formatting
select the first data set
<format><Conditional Format>
Formula is
Upper(vlookup(Current cell,second range,2,false))="Y"
set the format as you want.
note I often have to go backinto conditonal format to get rid of unwanted
quote marks and verify relative absolute cell referencing is correct.

"Becs" wrote:

> I've got a massive list of phones, some of which are colour and some are not eg
> s60X
> 8810
> 530g
> 7210
> 6210
>
> I have another list which tells me which phones are colour
> Phone Colour
> 7210 Y
> 6210 Y
> g43 y
>
> How do i find out without doing it manually which phones in my first list
> are colour
>
> AM gutted that i can't work this out

Assume your first list is in column A and your second in columns J and K
in B1 put this formula and copy down........

=IF(ISNA(VLOOKUP(A1,J:K,2,FALSE)),"",VLOOKUP(A1,J:K,2,FALSE))

Vaya con Dios,
Chuck, CABGx3



"Becs" <[email protected]> wrote in message
news:[email protected]...
> I've got a massive list of phones, some of which are colour and some are
not eg
> s60X
> 8810
> 530g
> 7210
> 6210
>
> I have another list which tells me which phones are colour
> Phone Colour
> 7210 Y
> 6210 Y
> g43 y
>
> How do i find out without doing it manually which phones in my first list
> are colour
>
> AM gutted that i can't work this out

That, my friend, was the bollocks!!

Thank you so much
B

"CLR" wrote:

> Assume your first list is in column A and your second in columns J and K
> in B1 put this formula and copy down........
>
> =IF(ISNA(VLOOKUP(A1,J:K,2,FALSE)),"",VLOOKUP(A1,J:K,2,FALSE))
>
> Vaya con Dios,
> Chuck, CABGx3
>
>
>
> "Becs" <[email protected]> wrote in message
> news:[email protected]...
> > I've got a massive list of phones, some of which are colour and some are
> not eg
> > s60X
> > 8810
> > 530g
> > 7210
> > 6210
> >
> > I have another list which tells me which phones are colour
> > Phone Colour
> > 7210 Y
> > 6210 Y
> > g43 y
> >
> > How do i find out without doing it manually which phones in my first list
> > are colour
> >
> > AM gutted that i can't work this out
>
>
>

amung the ways would be conditional formatting
select the first data set
<format><Conditional Format>
Formula is
Upper(vlookup(Current cell,second range,2,false))="Y"
set the format as you want.
note I often have to go backinto conditonal format to get rid of unwanted
quote marks and verify relative absolute cell referencing is correct.

"Becs" wrote:

> I've got a massive list of phones, some of which are colour and some are not eg
> s60X
> 8810
> 530g
> 7210
> 6210
>
> I have another list which tells me which phones are colour
> Phone Colour
> 7210 Y
> 6210 Y
> g43 y
>
> How do i find out without doing it manually which phones in my first list
> are colour
>
> AM gutted that i can't work this out

Assume your first list is in column A and your second in columns J and K
in B1 put this formula and copy down........

=IF(ISNA(VLOOKUP(A1,J:K,2,FALSE)),"",VLOOKUP(A1,J:K,2,FALSE))

Vaya con Dios,
Chuck, CABGx3



"Becs" <[email protected]> wrote in message
news:[email protected]...
> I've got a massive list of phones, some of which are colour and some are
not eg
> s60X
> 8810
> 530g
> 7210
> 6210
>
> I have another list which tells me which phones are colour
> Phone Colour
> 7210 Y
> 6210 Y
> g43 y
>
> How do i find out without doing it manually which phones in my first list
> are colour
>
> AM gutted that i can't work this out

That, my friend, was the bollocks!!

Thank you so much
B

"CLR" wrote:

> Assume your first list is in column A and your second in columns J and K
> in B1 put this formula and copy down........
>
> =IF(ISNA(VLOOKUP(A1,J:K,2,FALSE)),"",VLOOKUP(A1,J:K,2,FALSE))
>
> Vaya con Dios,
> Chuck, CABGx3
>
>
>
> "Becs" <[email protected]> wrote in message
> news:[email protected]...
> > I've got a massive list of phones, some of which are colour and some are
> not eg
> > s60X
> > 8810
> > 530g
> > 7210
> > 6210
> >
> > I have another list which tells me which phones are colour
> > Phone Colour
> > 7210 Y
> > 6210 Y
> > g43 y
> >
> > How do i find out without doing it manually which phones in my first list
> > are colour
> >
> > AM gutted that i can't work this out
>
>
>

I've got a massive list of phones, some of which are colour and some are not eg
s60X
8810
530g
7210
6210

I have another list which tells me which phones are colour
Phone Colour
7210 Y
6210 Y
g43 y

How do i find out without doing it manually which phones in my first list
are colour

AM gutted that i can't work this out

amung the ways would be conditional formatting
select the first data set
<format><Conditional Format>
Formula is
Upper(vlookup(Current cell,second range,2,false))="Y"
set the format as you want.
note I often have to go backinto conditonal format to get rid of unwanted
quote marks and verify relative absolute cell referencing is correct.

"Becs" wrote:

> I've got a massive list of phones, some of which are colour and some are not eg
> s60X
> 8810
> 530g
> 7210
> 6210
>
> I have another list which tells me which phones are colour
> Phone Colour
> 7210 Y
> 6210 Y
> g43 y
>
> How do i find out without doing it manually which phones in my first list
> are colour
>
> AM gutted that i can't work this out

Assume your first list is in column A and your second in columns J and K
in B1 put this formula and copy down........

=IF(ISNA(VLOOKUP(A1,J:K,2,FALSE)),"",VLOOKUP(A1,J:K,2,FALSE))

Vaya con Dios,
Chuck, CABGx3



"Becs" <[email protected]> wrote in message
news:[email protected]...
> I've got a massive list of phones, some of which are colour and some are
not eg
> s60X
> 8810
> 530g
> 7210
> 6210
>
> I have another list which tells me which phones are colour
> Phone Colour
> 7210 Y
> 6210 Y
> g43 y
>
> How do i find out without doing it manually which phones in my first list
> are colour
>
> AM gutted that i can't work this out

That, my friend, was the bollocks!!

Thank you so much
B

"CLR" wrote:

> Assume your first list is in column A and your second in columns J and K
> in B1 put this formula and copy down........
>
> =IF(ISNA(VLOOKUP(A1,J:K,2,FALSE)),"",VLOOKUP(A1,J:K,2,FALSE))
>
> Vaya con Dios,
> Chuck, CABGx3
>
>
>
> "Becs" <[email protected]> wrote in message
> news:[email protected]...
> > I've got a massive list of phones, some of which are colour and some are
> not eg
> > s60X
> > 8810
> > 530g
> > 7210
> > 6210
> >
> > I have another list which tells me which phones are colour
> > Phone Colour
> > 7210 Y
> > 6210 Y
> > g43 y
> >
> > How do i find out without doing it manually which phones in my first list
> > are colour
> >
> > AM gutted that i can't work this out
>
>
>