Excel Wizards,

I have a list:

Name Device
-------- ---------
Bill
Bill
Sue
Sue
Randy
Randy

I need to lookup, yes or no, if each unique user has registered any text
device. I know that the device can be registered from 7 different carriers
(vtext.com, txt.att.net, etc).

How to do this in a formula so to get:

Name Text Device
-------- ---------------
Bill No
Sue Yes
Randy Yes

Many thanks, Mark

Give the range of your text providers a range name. I'll use the
range name "data".

The list of your names or in Column A

col a Col b

Bill {=IF(ISNUMBER(FIND(data,C1,1)),"yes","no")}

Make sure that you hit Cntrl-Alt-Enter to make it an array formula




On Jan 15, 4:53*pm, MarkMcG wrote:
Excel Wizards,

I have a list:

Name * * * * * Device
-------- * * * *---------
Bill * * * * * * *
Bill * * * * * * *
Sue * * * * * *
Sue * * * * * *
Randy * * * * *
Randy * * * * *

I need to lookup, yes or no, if each unique user has registered any text
device. *I know that the device can be registered from 7 different carriers
(vtext.com, txt.att.net, etc).

How to do this in a formula so to get:

Name * * * * * Text Device
-------- * * * *---------------
Bill * * * * * * * * *No
Sue * * * * * * * *Yes
Randy * * * * * * Yes

Many thanks, Mark

Sorry.. Cntrl-Shift-Enter for array formula.


On Jan 15, 5:06*pm, willwonka wrote:
Give the range of your text providers a range name. *I'll use the
range name "data".

The list of your names or in Column A

col a * *Col b

Bill * * {=IF(ISNUMBER(FIND(data,C1,1)),"yes","no")}

Make sure that you hit Cntrl-Alt-Enter to make it an array formula

On Jan 15, 4:53*pm, MarkMcG wrote:



Excel Wizards,

I have a list:

Name * * * * * Device
-------- * * * *---------
Bill * * * * * * *
Bill * * * * * * *
Sue * * * * * *
Sue * * * * * *
Randy * * * * *
Randy * * * * *

I need to lookup, yes or no, if each unique user has registered any text
device. *I know that the device can be registered from 7 different carriers
(vtext.com, txt.att.net, etc).

How to do this in a formula so to get:

Name * * * * * Text Device
-------- * * * *---------------
Bill * * * * * * * * *No
Sue * * * * * * * *Yes
Randy * * * * * * Yes

Many thanks, Mark- Hide quoted text -

- Show quoted text -

Expand this list to your full seven options, don't forget the asterisk
wildcard at the beginning of each one:

=IF(SUM(COUNTIF(A1,{"*zmail.biz","*vtext.com","*tx t.att.net"}))0,"Yes","No")

A1 = first email address to check.
--
"Actually, I *am* a rocket scientist." -- JB

Your feedback is appreciated, click YES if this post helped you.


"MarkMcG" wrote:

Excel Wizards,

I have a list:

Name Device
-------- ---------
Bill
Bill
Sue
Sue
Randy
Randy

I need to lookup, yes or no, if each unique user has registered any text
device. I know that the device can be registered from 7 different carriers
(vtext.com, txt.att.net, etc).

How to do this in a formula so to get:

Name Text Device
-------- ---------------
Bill No
Sue Yes
Randy Yes

Many thanks, Mark

Thanks will,

This is close.

I have two sheets. One sheet (user with devices) contains the data
(multiple occurrences of a name with several devices) and the other (users)
is just the names. I need to lookup the name in (users) in the list in (user
with devices) and return if the user has registered a text device.

Thanks, Mark

If you post all seven to check, I can adjust this non-array-required formula
to work, else expand it yourself by adding more entries in the section where
I've already put your sample three:

Don't forget the asterisk wildcard at the beginning of each one:

=IF(SUM(COUNTIF(A1,{"*zmail.biz","*vtext.com","*tx t.att.net"}))0,"Yes","No")

A1 = first email address to check.

--
"Actually, I *am* a rocket scientist." -- JB

Your feedback is appreciated, click YES if this post helped you.


"MarkMcG" wrote:

Thanks will,

This is close.

I have two sheets. One sheet (user with devices) contains the data
(multiple occurrences of a name with several devices) and the other (users)
is just the names. I need to lookup the name in (users) in the list in (user
with devices) and return if the user has registered a text device.

Thanks, Mark