pulling data!
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 |
pulling data!
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 |
All times are GMT +1. The time now is 07:59 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com