Fill Column based on other columns

[Kavya132017]

 I have a scenario where, I need to fill 3 columns.Attribute 1,attribute 2,attribute 3. 

I have other 3 columns Brand 1,Brand 2,Brand 3. For each item number..If Brand 1 has a value then Attribute 1,Attribute 2 ,Attribute 3 are to be filled with values from All "MF1" columns i.e MF1 Name,MF1 Number,MF1 URL respectively. If Brand 1 is null & Brand 2 has values, then Attribute 1,Attribute 2 ,Attribute 3 are to be filled with values from All "MF2" columns.

If both Brand 1 & Brand 2 are filled..then I need 2 rows for each Item number.one row filled with "MF1" values, other row filled with MF2 values.

attached the sample data & required format.

[ShankerV]

Hi @Kavya132017 

One way of doing this.

Step 1: Input

Step 2: Transpose

Step 3: 

Step 4:

Step 5:

IF [Name2]="Brand 1"
THEN [MF1 Name]
ELSEIF [Name2]="Brand 2"
THEN [MF2 Name]
ELSE [MF3 Name]
ENDIF

IF [Name2]="Brand 1"
THEN [MF1 Number]
ELSEIF [Name2]="Brand 2"
THEN [MF2 Number]
ELSE [MF3 Number]
ENDIF

IF [Name2]="Brand 1"
THEN [MF1 URL]
ELSEIF [Name2]="Brand 2"
THEN [MF2 URL]
ELSE [MF3 URL]
ENDIF

Last Step :

Many thanks

Shanker V

[Kavya132017]

@ShankerV Thanks for the solution.That kinda worked.But there's other scenario for the same case.Attached the sample data & req output file.

The requirement is , each Item Number can have 1,2 or 3 Trade Partner Numbers. These Trade partner numbers are horizontally stored in "MF Number" columns.

Attribute 1 Column in my output : should have corresponding Brand values.How do we decide which brand is for which trading partner number is based on the header of Brand columns. 

If Trading Partner Number is from "MF Number 1" column, then "Brand 1" value should be filled in Attribute 1. If Trading Partner Number is from "MF Number 2" column, then "Brand 2" value should be filled in Attribute 1. It's based on the numbers in headers.

Attribute 2 should be filled with "MF URL" values, again based on header Number.