hi!

in sheet1 i'm having the following data thru A1:C6

old_ac--------------new_ac----------name
08765001111------10455------------ AAA
07654002222------10613------------ BBB
0356700111100----10489------------AAA
0258900111101----10356------------AAA
0987500222200----10856------------BBB

in sheet2 thru A1:C3

old_cus----------- -new_cus--------------name
001111------------82055614-------------AAA
002222------------82056763-------------BBB

the point is the *six digits* starting from 6th digit in the "old_ac"
always represents the "old_cus",
and the "old_ac" can't have more than 13 digits.

what i want is to extract the five fields "old_cus", "new_cus",
"old_ac", "new_ac" & "name"
in sheet3 for all the records in sheet1.

the columns "old_ac" & "old_cus" has been formatted as text..!


any hlp pl..??

-via135


--
via135
------------------------------------------------------------------------
via135's Profile: http://www.excelforum.com/member.php...o&userid=26725
View this thread: http://www.excelforum.com/showthread...hreadid=546847

Hi,
Not certain that I fully understand your question, but....

In C1 of Sheet3 enter: =MID(Sheet1!A2,6,6)
In C2 of Sheet3 enter: =VLOOKUP(C1, Sheet2!$A$2:$C$3, 2, False)
In C3 of Sheet3 enter: =Sheet1!A2
In C4 of Sheet3 enter: =Sheet1!B2
In C5 of Sheet3 enter: =Sheet1!C2

Copy the formulas in Sheet3 down as many rows as are in Sheet1.

Is this what you wanted?


--
Ken Hudson


"via135" wrote:


hi!

in sheet1 i'm having the following data thru A1:C6

old_ac--------------new_ac----------name
08765001111------10455------------ AAA
07654002222------10613------------ BBB
0356700111100----10489------------AAA
0258900111101----10356------------AAA
0987500222200----10856------------BBB

in sheet2 thru A1:C3

old_cus----------- -new_cus--------------name
001111------------82055614-------------AAA
002222------------82056763-------------BBB

the point is the *six digits* starting from 6th digit in the "old_ac"
always represents the "old_cus",
and the "old_ac" can't have more than 13 digits.

what i want is to extract the five fields "old_cus", "new_cus",
"old_ac", "new_ac" & "name"
in sheet3 for all the records in sheet1.

the columns "old_ac" & "old_cus" has been formatted as text..!


any hlp pl..??

-via135


--
via135
------------------------------------------------------------------------
via135's Profile: http://www.excelforum.com/member.php...o&userid=26725
View this thread: http://www.excelforum.com/showthread...hreadid=546847