can't figure out the formula to use

[Katie]

Hello,

I have a SS with 3 columns that look something like this:

ANAME) BAgency[Vlookup Result]) CFormula Result)
Jones, Joe Agency1 Agency1 A-M
Smith, Bob Agency2 Agency2 N-Z
Kid, Gregg Agency3 Agency3
Rohl, Jim Agency4 Agency4

**column B is a result of another sheet in the workbook...

I am trying to find a formula that would follow this
logic:

if the 1st letter in A1 is between A-M, and B1
is "Agency1" or "Agency2", then C1 will be 'B1&"A-M"'. if
the first letter in A1 is between N-Z, and B1 is "Agency1"
or "Agency2", then C1 will be 'B1&"N-Z"'. If B1 equals
anything BUT "Agency1" or "Agency2", then the result will
only be 'B1'.

I appreciate the help. Thanks...

 

[Sandy Mann]

There may be better ways but for your specific example:

=B2&IF(--(RIGHT(B2,1))<3,IF(LEFT(A2,1)<"N"," A-M"," N-Z"),"")

seems to work.

HTH

Sandy

 

[Katie]

Unfortunately that didn't work. Any other ideas???

Thanks!
Katie

-----Original Message-----
There may be better ways but for your specific example:

=B2&IF(--(RIGHT(B2,1))<3,IF(LEFT(A2,1)<"N"," A-M"," N- Z"),"")

seems to work.

HTH

Sandy

--
to e-mail direct replace @mailintor.com with @tiscali.co.uk

Hello,

I have a SS with 3 columns that look something like this:

ANAME) BAgency[Vlookup Result]) CFormula Result)
Jones, Joe Agency1 Agency1 A-M
Smith, Bob Agency2 Agency2 N-Z
Kid, Gregg Agency3 Agency3
Rohl, Jim Agency4 Agency4

**column B is a result of another sheet in the workbook...

I am trying to find a formula that would follow this
logic:

if the 1st letter in A1 is between A-M, and B1
is "Agency1" or "Agency2", then C1 will be 'B1&"A-M"'. if
the first letter in A1 is between N-Z, and B1 is "Agency1"
or "Agency2", then C1 will be 'B1&"N-Z"'. If B1 equals
anything BUT "Agency1" or "Agency2", then the result will
only be 'B1'.

I appreciate the help. Thanks...

.

 

[Katie]

To clarify, the "Agency1", "Agency2" names were only
examples. There are actual agencies that will be in
there, so I wouldn't be able to look up via the RIGHT
function...

-----Original Message-----
There may be better ways but for your specific example:

=B2&IF(--(RIGHT(B2,1))<3,IF(LEFT(A2,1)<"N"," A-M"," N- Z"),"")

seems to work.

HTH

Sandy

--
to e-mail direct replace @mailintor.com with @tiscali.co.uk

Hello,

I have a SS with 3 columns that look something like this:

ANAME) BAgency[Vlookup Result]) CFormula Result)
Jones, Joe Agency1 Agency1 A-M
Smith, Bob Agency2 Agency2 N-Z
Kid, Gregg Agency3 Agency3
Rohl, Jim Agency4 Agency4

**column B is a result of another sheet in the workbook...

I am trying to find a formula that would follow this
logic:

if the 1st letter in A1 is between A-M, and B1
is "Agency1" or "Agency2", then C1 will be 'B1&"A-M"'. if
the first letter in A1 is between N-Z, and B1 is "Agency1"
or "Agency2", then C1 will be 'B1&"N-Z"'. If B1 equals
anything BUT "Agency1" or "Agency2", then the result will
only be 'B1'.

I appreciate the help. Thanks...

.

 

[Casey]

Katie,
Try the following:
=IF(OR(B1="Agency 1",B1="Agenc
2"),IF(CODE(A1)<=77,B1&"A-M",B1&"N-Z"),B1)
Which assumes that the Names are in Column A, the Agency 1/Agency 2
other is in column C and the concatenation formula is in column C
Late add Katie: But all you should need to do to my formula is t
substitute your real agency names for the "agency 1" "agency 2" i

Hope this help

 

[Kate]

Fabulous. It worked!!! Thanks!

 

[Gabor]

-----Original Message-----
Hello,

I have a SS with 3 columns that look something like this:

ANAME) BAgency[Vlookup Result]) CFormula Result)
Jones, Joe Agency1 Agency1 A-M
Smith, Bob Agency2 Agency2 N-Z
Kid, Gregg Agency3 Agency3
Rohl, Jim Agency4 Agency4

**column B is a result of another sheet in the workbook...

I am trying to find a formula that would follow this
logic:

if the 1st letter in A1 is between A-M, and B1
is "Agency1" or "Agency2", then C1 will be 'B1&"A-M"'. if
the first letter in A1 is between N-Z, and B1 is "Agency1"
or "Agency2", then C1 will be 'B1&"N-Z"'. If B1 equals
anything BUT "Agency1" or "Agency2", then the result will
only be 'B1'.

I appreciate the help. Thanks...

.Hello,

You are running out on the bottom of the board,so I rush:
(There maybe shorter formulae but no time!)
In C1:

if(and(or(left(a1,1)="A",left(a1,1)="B",.....left(a1,1)
="M"),Mid(B1,1,6)="agency"),"B1&A-M",
if(and(or(left(a1,1)="N",.....left(a1,1)="Z"),Mid(b1,1,6)
="agency")," B1&N-Z","B1"))
if first letter a-m in column a and column B="Agency"
then:"B1&A-M",if first letters in col. A n-z and col b
first five letters are agency then:B1&N-z else B1.

hope this helps!
CHECK PARENTHESIS !!!!