Macro or formula to find code and print codes

**narendrabr** · 09-13-2012, 09:18 AM

H,

I have set of names in the column and that contains Codes like ( C,P,CP,HC,RE etc). What I have to do is,I have to find a match for these codes and write or print full form of that codes in the separate columns.I have to create two extra columns Target profile and Vertical.
You can see the comments in the workbook.
Please help me on this.
Target Profile
For Example:
C: Customer
P: Prospects
CP: Customer & Prospects

Vertical
HC=Healthcare
RT=Retail
CV=Cross Vertical
SV=Cross Vertical
PS=Public Sector
MF=Manufacturing
S&D = S&D

Please suggest me with a Macro or Formula to find this code and write proper abbreviation in the next column.

I have attached my Work book.

Thanks!

**jeffreybrown** · 09-13-2012, 09:25 AM

It appears all of the values in column A start with the Code and then a title.

If this is the case you can use

=VLOOKUP(LEFT(A2,FIND("-",A2)-2),$E$1:$F$7,2,0)

with a lookup table in E1:F7

Example: E1 = HC and F1 = Healthcare and so on down to row 7 or however far your codes go.

**narendrabr** · 09-13-2012, 10:23 AM

Hi,
No If the name like this:

HC - C - ANCC - Demo Email Invite T2 - Maderia (AprimoID: 129809 & Task: 130406)
It Should print Healthcare IN vertical column and Customer in Target column as you can see HC and C both are there.

Thanks

**jeffreybrown** · 09-13-2012, 10:44 AM

Give this a try...

**narendrabr** · 09-13-2012, 12:17 PM

Hi,
It is not showing any thing for
P - All verts- WF Matters newsletter -
It should show Prospect instead of NA.
And also wherever the code comes it gives full form.
Like for Customer & Prospect it could be CP, C&P,C/P,PC....

Thanks,

**jeffreybrown** · 09-13-2012, 12:24 PM

You are going to have to look at all the requirements and spell them out. I can't guess at everything you want returned.

As for as the Customer & Prospect and the different codes, build those requirements into the lookup table.

In the case of P - All verts, same thing, build this into the lookup table.

Try to place all your requirements into the lookup table and then if there are still some outliers then let's try to fix them.

Overall, do the formula work. My guess is yes but the error come in when the lookup table is not built to the requirements.

**narendrabr** · 09-14-2012, 07:25 AM

Hi,

I mean if the name is start with P,C,OR CP it is not working giving N/A.

Thanks

**jeffreybrown** · 09-14-2012, 07:58 AM

Can you provide what you have tried so far? What does your lookup table consist of? Are these code part of it?

**narendrabr** · 09-14-2012, 08:03 AM

Hi,

I can see that in the file you have provided. It is showing N/A for P that even though you have included in the look-up table.

Thanks,

**jeffreybrown** · 09-14-2012, 08:33 AM

Please take a look at the lookup tables. You need to add it to the table on the left.

**narendrabr** · 09-14-2012, 09:01 AM

It is the code:
=VLOOKUP(TRIM(LEFT(MID(A4,FIND("-",A4)+1,255),FIND("-",MID(A4,FIND("-",A4)+1,255))-1)),$H$2:$I$4,2,0)

I am not getting whats wrong?
Thanks

**jeffreybrown** · 09-14-2012, 09:18 AM

There are two formulas, one in column B and one in column C.

Column B >> =VLOOKUP(LEFT(A2,FIND("-",A2)-2),$E$2:$F$7,2,0) >> Finds the first characters before the first "-" mark

Column C >> =VLOOKUP(TRIM(LEFT(MID(A2,FIND("-",A2)+1,255),FIND("-",MID(A2,FIND("-",A2)+1,255))-1)),$H$2:$I$4,2,0) >> Finds the value between the "-" marks

With this in column A >> HC - C&P - ANCC - Demo Email Invite T2 - Maderia (129810)

Column B returns >> Healthcare >> because in the lookup table $E$2:$F$7 >> HC >> equals >> Healthcare

Column C returns >> Customer & prospects because in the lookup table $H$2:$I$4 >> C&P >> eqauls >> Customer & prospects

**narendrabr** · 09-14-2012, 10:19 AM

Ok Thanks.But why it is NA for P - All verts- WF Matters newsletter ?
Thanks

**jeffreybrown** · 09-14-2012, 10:21 AM

Is P Vertical?

Is All verts Target profile?

**narendrabr** · 09-14-2012, 10:27 AM

P is target profile and All verts is nothing. We should take only codes for match.

Thanks

**jeffreybrown** · 09-14-2012, 10:33 AM

Wrap the formula in IFERROR

=IFERROR(VLOOKUP(TRIM(LEFT(MID(A4,FIND("-",A4)+1,255),FIND("-",MID(A4,FIND("-",A4)+1,255))-1)),$H$2:$I$4,2,0),"")

**narendrabr** · 09-17-2012, 07:27 AM

Have you got the solution?

Thanks!

**jeffreybrown** · 09-17-2012, 07:55 AM

What did post #16 do for you?

**narendrabr** · 09-17-2012, 08:20 AM

It is giving out put as blank!

Thanks

**jeffreybrown** · 09-17-2012, 08:34 AM

How about updating your sample in post #1 as to what you have now and what is in error?

Macro or formula to find code and print codes

LinkBack

Thread Tools

Rate This Thread

Display

Macro or formula to find code and print codes

Re: Macro or formula to find code and print codes

Re: Macro or formula to find code and print codes

Re: Macro or formula to find code and print codes

Re: Macro or formula to find code and print codes

Re: Macro or formula to find code and print codes

Re: Macro or formula to find code and print codes

Re: Macro or formula to find code and print codes

Re: Macro or formula to find code and print codes

Re: Macro or formula to find code and print codes

Re: Macro or formula to find code and print codes

Re: Macro or formula to find code and print codes

Re: Macro or formula to find code and print codes

Re: Macro or formula to find code and print codes

Re: Macro or formula to find code and print codes

Re: Macro or formula to find code and print codes

Re: Macro or formula to find code and print codes

Re: Macro or formula to find code and print codes

Re: Macro or formula to find code and print codes

Re: Macro or formula to find code and print codes

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions