I have a excel with four columns. I want the macro to update the column D with the abbreviations for column C elements. The macro will fetch abbreviations from column B. I have sample elemnts in row 3 & 4. I want similar results in Row 3,4,5 using macro. I am attaching the excel.
is the format always the same in row 3?
sorry, should have asked you
is the format always the same in column C?
e.g 2_2_3 or will it change to 12_1_999
ok this is what i got for you so far
=VLOOKUP(LEFT(C2,1),A:B,2,FALSE)&" "&VLOOKUP(LEFT(RIGHT(C2,3),1),A:B,2,FALSE)&" "&VLOOKUP(RIGHT(C2,1),A:B,2,FALSE)
paste the above code in cell d2 and copy down.
still need some more work.
paste this funtion in cell d2 and copy down
=VLOOKUP(LEFT(C2,IF(ISERROR(FIND("_",C2,1)),LEN(C2),FIND("_",C2,1)-1)),A:B,2,FALSE)&" "&VLOOKUP((TRIM(IF(ISERROR(FIND("_",C2,1)),C2,MID(C2,FIND("_",C2,1)+1,IF(IS ERROR(FIND("_",C2,FIND("_",C2,1)+2)),LEN(C2),FIND("_",C2,FIND("_",C2,1)+2))-FIND("_",C2,1)-1)))),A:B,2,FALSE)&" "&VLOOKUP(TRIM(RIGHT(C2,LEN(C2)-IF(ISERROR(FIND("_",C2,FIND("_",C2,FIND("_",C2,1)+2))),LEN(C2),FIND("_",C2, FIND("_",C2,FIND("_",C2,1)+2))*1))),A:B,2,FALSE)
took a longtime but think it'll work if not help.
I had to coerce it to get it to work as my column A was n umbers
=VLOOKUP(--LEFT(C2,IF(ISERROR(FIND("_",C2,1)),LEN(C2),FIND("_",C2,1)-1)),A:B,2,FALSE)&" "&
VLOOKUP(--(TRIM(IF(ISERROR(FIND("_",C2,1)),C2,MID(C2,FIND("_",C2,1)+1,IF(ISERROR(FIND ("_",C2,FIND("_",C2,1)+2)),LEN(C2),FIND("_",C2,FIND("_",C2,1)+2))-FIND("_",C2,1)-1)))),A:B,2,FALSE)&" "&
VLOOKUP(--TRIM(RIGHT(C2,LEN(C2)-IF(ISERROR(FIND("_",C2,FIND("_",C2,FIND("_",C2,1)+2))),LEN(C2),FIND("_",C2, FIND("_",C2,FIND("_",C2,1)+2))*1))),A:B,2,FALSE)
An alternative solution
[vba]
=VLOOKUP(--LEFT(C2,FIND("_",C2)-1),A:B,2,FALSE)&" "&
VLOOKUP(--MID(C2,FIND("_",C2)+1,FIND("_",C2,FIND("_",C2)+1)-FIND("_",C2)-1),A:B,2,FALSE)&" "&
VLOOKUP(--MID(C2,FIND("~",SUBSTITUTE(C2,"_","~",LEN(C2)-LEN(SUBSTITUTE(C2,"_",""))))+1,255),A:B,2,FALSE)
[/vba]
XLD - Your formula worked for the excel sheet provided by me but I can have something like 12_1_100 in column C so for this the formula wont work.
hi michaelm702,
have you look at my code?
i've attached a copy of the file for you.
That formula works for me, what do you get?Originally Posted by michaelm702
XLD & CCKfm both of your formulas worked thanks. XLD you had --LEFT can you pls tell me why we use "--".
As I mentioned to the other guy, 1 could be a text one, or numeric 1. Because all LEFT,RIGHT, MID functions return a string, I used -- to coerce it to a number, so the lookup works.
Try this:
=VLOOKUP(--LEFT(C2,FIND("_",C2,1)-1),data,2)&" "&VLOOKUP(--MID(C2,FIND("_",C2,1)+1,FIND("_",C2,FIND("_",C2,1)+1)-FIND("_",C2,1)-1),data,2,FALSE)&" "&VLOOKUP(--RIGHT(C2,LEN(C2)-FIND("_",C2,FIND("_",C2,1)+1)),data,2,FALSE)
It's horrendous but it does work.
The alternative is to use Data/TexttoColumns with the delimiter set as and underscore '_' and then VLOOKUP() each value found in the three columns and concatenate the result. That requires 3 extra columns but the result is easier to see how it's been achieved and can also be error checked by looking at the three helper columns (which can be hidden if required). Using this technique the formula would be:
=VLOOKUP(F2,data,2,FALSE)&" "&VLOOKUP(G2,data,2,FALSE)&" "&VLOOKUP(H2,data,2,FALSE)
If course error checking can be built in using and =IF(ISERROR(VLOOKUP()),"",VLOOKUP()) type of syntax. That will work but will make change formula from horrendous to truly horrendous for the first formula to complex for the second.
Hope all that helps. Meanwhile here's a link to a file with it all in:
http://www.asdy88.dsl.pipex.com/Expe...e/Lookup01.xls
Sorry about the insentive address but that's the sub-directory I use for my uploads to my ISP's server!
Patrick
Posting Permissions
Reply With Quote
