software

One of my responsibilities is the deployment of software around our
network. I often get lists of users and their computers names but
usually not in the format I need.

For example I get

User PC
S01234 1712265
K11567 1287997
B09768 2524387

What I need is for each PC name to have it's site prefix added

User PC Prefixed Result
S01234 1712265 ST1712265
K11567 1287997 LO1287997
B09768 2524387 BS2524387

Currently I have to sort on user name and =CONCATENATE(quot;STquot;,B2) copy
that down the users beginning S =CONCATENATE(quot;LOquot;,B3) for users
beginning and so on.

Is there a way of using LEFT so that it goes something like this
IF LEFT(A2)=quot;Squot; Concatenate (quot;STquot;,B2) ELSE IF LEFT(A2)=quot;Kquot; Concatenate
(quot;LOquot;,B2) ELSE IF LEFT(A2)=quot;Bquot; Concatenate (quot;BSquot;,B2)

Thanks in adavance--
glwday
------------------------------------------------------------------------
glwday's Profile: www.excelforum.com/member.php...oamp;userid=32336
View this thread: www.excelforum.com/showthread...hreadid=520976Hi glyday,
=IF(LEFT(A2,1)=quot;Squot;,quot;STquot; amp; B2,IF(LEFT(A2,1)=quot;Kquot;,quot;LOquot; amp;
B2,IF(LEFT(A2,1)=quot;Bquot;,quot;BSquot; amp; B2,quot;quot;)))
is one version
Ken JohnsonOn Fri, 10 Mar 2006 04:19:55 -0600, glwday
gt; wrote:

gt;
gt;One of my responsibilities is the deployment of software around our
gt;network. I often get lists of users and their computers names but
gt;usually not in the format I need.
gt;
gt;For example I get
gt;
gt;User PC
gt;S01234 1712265
gt;K11567 1287997
gt;B09768 2524387
gt;
gt;What I need is for each PC name to have it's site prefix added
gt;
gt;User PC Prefixed Result
gt;S01234 1712265 ST1712265
gt;K11567 1287997 LO1287997
gt;B09768 2524387 BS2524387
gt;
gt;Currently I have to sort on user name and =CONCATENATE(quot;STquot;,B2) copy
gt;that down the users beginning S =CONCATENATE(quot;LOquot;,B3) for users
gt;beginning and so on.
gt;
gt;Is there a way of using LEFT so that it goes something like this
gt;IF LEFT(A2)=quot;Squot; Concatenate (quot;STquot;,B2) ELSE IF LEFT(A2)=quot;Kquot; Concatenate
gt;(quot;LOquot;,B2) ELSE IF LEFT(A2)=quot;Bquot; Concatenate (quot;BSquot;,B2)
gt;
gt;Thanks in adavance

Although the IF statement can be used with just three users, it becomes
increasingly cumbersome for more users, and impossible if you have more than
eight users.

Accordingly I would suggest a lookup table which would be much more easy to
extend.

For the examples you give, the lookup table (tbl) would look like:

SST
KLO
BBS

and the formula would look like:

=VLOOKUP(LEFT(User,1),tbl,2,FALSE)amp;PC

Where User and PC are named ranges referring to your data ranges; tbl is the
named range referring to the Table.--ron


Thanks guys for your help.

While I was waiting I finally got my version to work with

=IF(LEFT(A2)=quot;Squot;,CONCATENATE(quot;STquot;,B2),IF(LEFT(A2)= quot;Kquot;,CONCATENATE(quot;LOquot;,B2),IF(LEFT(A2)=quot;Bquot;,CONCATENA TE(quot;BSquot;,B2))))

Keep up the good work

GLW--
glwday
------------------------------------------------------------------------
glwday's Profile: www.excelforum.com/member.php...oamp;userid=32336
View this thread: www.excelforum.com/showthread...hreadid=520976