Find Value in Text and "Vlookup" value

[#1]

Dear all,

I hope you had all a good start into the week... I have the following problem where I would need your brains :

I would like to find a value (forced package) in a text and "vlookup" the Code & Package value.

The text is the following for the MCode ABCDEF: "A32, W98, XXD, BCD, W11"

As a result it should show me "ABCDEF - Pack.2"

So it has to recognize the right MCode and find the forced package code in the text to give me the right Code & Package value.

MCode	Forced Package	Code & Package
ABCDEF	W94	ABCDEF - Pack.1
ABCDEF	W98	ABCDEF - Pack.2

What kind of formula do I have to use to get to this result?

Thanks for any kind of hints and solutions!!

Cheers

Martin

[#2]

No one has an idea how to solve this?

[ramoncova06]

so you first want to find the Forced Package and then based on that you want to pull the Code & Package ?

I do think that you can do this by using applymap as Daniel and Jonathan already suggested

it might be easier if you provided an example with more details

[#4]

yes exactly that is what I want to do... I will prepare an example ...

[#5]

here as promised the examples... when I select a unique code it should show one package in the left table

[Kushal_Chawda]

What if one Force Package will be there in Two PrCodes? Like in your Excel. W41 is there in two PrCodes for Mcode 111111

[#7]

    if you mean this, it should show the following:

Unique Number	PrCodes	Model Code & Package
CCCCC	1G1 1S1 1T3 3S1 3Y4 5D4 9JB B83 KA2 KP4 W41 W90	111111 - SE
DDDDD	1G1 1S1 1T3 3S1 3Y4 5D4 9JB B83 KA2 KP4 W41 W90	111111 - SE

[#8]

So the biggest challenge is to find the "Forced Package" in the "PrCodes" ... that is what gives me the headache in the whole problem

[ramoncova06 · Accepted solution]

this seems to work

[sasiparupudi1]

Hi

You could do some thing like the following.. Basically, loop through both the tables and create a mapping table programmatically using the look up value.Hope this helps,

y:

LOAD [Forced Package] as FP ,

     Mcode,

     [Model Code & Package] as MCP

FROM

C:\Packages.xls

(biff, embedded labels, table is [Sheet1$]);

x:

LOAD [Unique Number] as UniqNumber,

     PrCodes,

     Mcode

FROM

C:\NADIN.xls

(biff, embedded labels);

FOR i=0 to NoOfRows('x')-1

LET vPrCodesMcode=trim(Peek('Mcode',$(i),'x'));

LET vPrCodes=Peek('PrCodes',$(i),'x');

LET vUniqNumber=Peek('UniqNumber',$(i),'x');

TRACE $(vMcode),$(vForcedPackage);

FOR j=0 to NoOfRows('y')-1

LET vForcedPackage=peek('FP',$(j),'y');

LET vMcode=trim(peek('Mcode',$(j),'y'));

//TRACE $(vPrCodesMcode) ;

//TRACE $(vMcode);

let vMCP="";

if (substringcount('$(vPrCodes)','$(vForcedPackage)')>0) and ('$(vPrCodesMcode)'='$(vMcode)') then

   Let vMCP=peek('MCP',$(j),'y');

   exit For;

ENDIF;

TRACE $(vMCP);

NEXT j;

   Map: 

   Load 

   '$(vMCP)' As MCP ,

   '$(vMcode)' as Mcode,

   '$(vPrCodes)' as PRCodes,

   '$(vForcedPackage)' as FP,

   '$(vUniqNumber)' as vUniqNumber

   AutoGenerate(1); 

NEXT i;

drop Table x;

drop Table y;

[#11]

Thanks for that!

It is working perfectly... now the problem is that the loop takes ages for a hugh amount of data... is there a why to shorten it ?