Repeting Values

dstage2006 · Post by **dstage2006** » Wed Oct 22, 2008 7:31 pm

Hi All,

I am trying to solve this problem, When ever there is a DYP in the col4 i need to replace the corresponding value in the col5 in place of COL2 like the same way COL6, COL7
same time i need to replace col3 for corresponding value for DXP.

COL1 COL2 COL3 COL4 COL5 COL6 COL7

A MMM 3
A MMM BBB
A MMM DYP XXX YYY ZZZ
A MMM DXP GGG HHH III
A MMM
A MMM
A MMM

COL1 COL2 COL3 COL4 COL5 COL6 COL7

A XXX 3
A XXX GGG
A XXX DYP XXX YYY ZZZ
A XXX DXP GGG HHH III
A XXX
A XXX
A XXX

COL1 COL2 COL3 COL4 COL5 COL6 COL7

A YYY 3
A YYY HHH
A YYY DYP XXX YYY ZZZ
A YYY DXP GGG HHH III
A YYY
A YYY
A YYY

COL1 COL2 COL3 COL4 COL5 COL6 COL7

A ZZZ 3
A ZZZ III
A ZZZ DYP XXX YYY ZZZ
A ZZZ DXP GGG HHH III
A ZZZ
A ZZZ
A ZZZ

I tried to pivot col5,col6 col7 if they are DYP then try to lookup with the same data. Please give me some suggestions.
Thanks for your sugestions.

owen3 · Post by **owen3** » Wed Oct 22, 2008 9:41 pm

First of all you have only 6 columns.
Secondly you don't have DYP at col4 in your sample data.

Based on your information, you could use file, lookup and funnel combination to solve this problem.

dstage2006 · Post by **dstage2006** » Thu Oct 23, 2008 9:14 am

Its a formatted sample text.

When ever there is a DYP in col4 then i need to replace the XXX,YYY,ZZZ in COL2 (MMM). Same time i need to replace BBB with the value of GGG,HHH,III (When ever there is a DXP in col4).

Code: Select all

CLO1   COL2   COL3        COL4   COL5   COL6   COL7 
                   
A       MMM      3             
A       MMM     BBB             
A       MMM                 DYP    XXX    YYY    ZZZ 
A       MMM                 DXP    GGG    HHH    III 
A       MMM                
A       MMM                
A       MMM                
                   
                   
Iteration1:                   
                   
CLO1   COL2   COL3          COL4   COL5   COL6   COL7 
                   
A       XXX      3             
A       XXX     GGG             
A       XXX                  DYP    XXX    YYY    ZZZ 
A       XXX                  DXP    GGG    HHH    III 
A       XXX                
A       XXX                
A       XXX                
                   
Iteration2:                   
CLO1   COL2   COL3         COL4   COL5   COL6   COL7 

A       YYY      3             
A       YYY     HHH             
A       YYY                  DYP    XXX    YYY    ZZZ 
A       YYY                  DXP    GGG    HHH    III 
A       YYY                
A       YYY                
A       YYY                

Iteration3:                   
CLO1   COL2   COL3         COL4   COL5   COL6   COL7 
                   
A       ZZZ      3             
A       ZZZ     III             
A       ZZZ                  DYP    XXX    YYY    ZZZ 
A       ZZZ                  DXP    GGG    HHH    III 
A       ZZZ                
A       ZZZ                
A       ZZZ