Conditional Transpose

[Faruk Hosen]

Hello,
i need to transpose and repeat text value from a column (range) which need to match according to their code which is given separated column.

I have attached a manual flow to understand.

Please help.

Thanks.

 

[NARAYANK991]

Hi ,

What is supposed to be displayed in the other cells H2 , I2 , J2 , K2 , H3 , I3 , J3 , K3 and so on ?

Can you fill up the entire range H2:K11 manually , so that it is clear ?

Narayan

 

[Faruk Hosen]

Hi ,

What is supposed to be displayed in the other cells H2 , I2 , J2 , K2 , H3 , I3 , J3 , K3 and so on ?

Can you fill up the entire range H2:K11 manually , so that it is clear ?

Narayan

OK. I have updated manually as you said.

Please reply

 

[NARAYANK991]

Hi ,

I am sure that your working file must be having more data ; please verify whether the formula in the uploaded file works correctly in your working file.

Narayan

 

[bosco_yip]

Hi,

Another option,

In M2, formula copy across and down :

=IF(A2="","-",INDEX($E$2:$E$11,MATCH(A2&"",INDEX(RIGHT($F$2:$F$11,2),),)))

p.s. If I changed the number in A10, B9, and C8 and noted the difference results found as per attached file comparison .

Regards
Bosco

 

[Faruk Hosen]

Hi ,

I am sure that your working file must be having more data ; please verify whether the formula in the uploaded file works correctly in your working file.

Narayan

It worked. Thanks.

But i want to understand the logic behind the solution. in your lazy time.

Can i?

Thanks again!

 

[Faruk Hosen]

Hi,

Another option,

In M2, formula copy across and down :

=IF(A2="","-",INDEX($E$2:$E$11,MATCH(A2&"",INDEX(RIGHT($F$2:$F$11,2),),)))

p.s. If I changed the number in A10, B9, and C8 and noted the difference results found as per attached file comparison .

Regards
Bosco

Thanks BOSCO!
It worked. Great!

But i want to understand the logic behind the solution.

could you please explain in your lazy time?

Thanks Again.

Thanks again!

 

[NARAYANK991]

Hi ,

The working part of the formula is :

INDEX($E$2:$E$11, MATCH(A2, INDEX($A$2:$D$11, , COLUMN(A2)), 0))

The range $E$2:$E$11 contains the names Barisal , Barguna , Amtali ,...

What we want to do is that the formula in column H should retrieve the name corresponding to the Division , the formula in column I should retrieve the name corresponding to the District , the formula in column J should retrieve the name corresponding to the Upazilla , and the formula in column K should retrieve the name corresponding to the Union.

The MATCH function is matching the code in column A with the data in column A , the code in column B with the data in column B , and so on.

So given data such as 10 , 04 , 09 and 71 , in row 10 , the code 10 is matched with the data in column A , and returns 1 ; thus H10 will display Barisal.

The code 04 is matched with the data in column B , and returns 2 , so that I10 displays Barguna.

Similarly for the remaining cells J10 and K10.

Because a single range reference of $A$2:$D$11 has been used , the COLUMN function is used to return the appropriate column ; if we use relative addressing as shown below :

A$2:A$11

then the above formula is simplified to :

INDEX($E$2:$E$11, MATCH(A2, A$2:A$11, 0))

Narayan

 

[Faruk Hosen]

Hi ,

The working part of the formula is :

INDEX($E$2:$E$11, MATCH(A2, INDEX($A$2:$D$11, , COLUMN(A2)), 0))

The range $E$2:$E$11 contains the names Barisal , Barguna , Amtali ,...

What we want to do is that the formula in column H should retrieve the name corresponding to the Division , the formula in column I should retrieve the name corresponding to the District , the formula in column J should retrieve the name corresponding to the Upazilla , and the formula in column K should retrieve the name corresponding to the Union.

The MATCH function is matching the code in column A with the data in column A , the code in column B with the data in column B , and so on.

So given data such as 10 , 04 , 09 and 71 , in row 10 , the code 10 is matched with the data in column A , and returns 1 ; thus H10 will display Barisal.

The code 04 is matched with the data in column B , and returns 2 , so that I10 displays Barguna.

Similarly for the remaining cells J10 and K10.

Because a single range reference of $A$2:$D$11 has been used , the COLUMN function is used to return the appropriate column ; if we use relative addressing as shown below :

A$2:A$11

then the above formula is simplified to :

INDEX($E$2:$E$11, MATCH(A2, A$2:A$11, 0))

Narayan

OK. Many many thanks. I am using simplified formula adding IFNA.

Thanks again