How to covert rows in a table to multiple columns.

[madhu_61244]

Experts,

I've a table with 3 columns as shown below.

Student Question Answer

Student-1 Q1 ABC

Student-2 Q1 XYZ

Student-1 Q2 MNL

Student-2 Q2 EFG

Student-1 Q3 DEF

Student-2 Q3 KLM


how to format above table as.

Student Q1 Q2 Q3

Student-1 ABC MNL DEF

Student-2 XYZ EFG KLM

Can we do it without using VBA.


Thanks in advance.

 

[mercatog]

Hi


Here file with 3 intermediate columns and the final result


http://speedy.sh/Dgucy/madhu-61244.xlsx


Regards

 

[mercatog]

Sorry but I uploaded the wrong file (Apologize and if a moderator can delete the post)


Here the file: http://speedy.sh/FWecX/madhu-61244.xlsx

 

[madhu_61244]

Wow! thats really amazing solution. Kudos to Mercatog.


Thanks Again!

 

[Hui]

Madhu_61244


Firstly, Welcome to the Chandoo.org forums.


You can solve your problem without any intermediate columns


In I2:

=IFERROR(INDEX($C$2:$C$9,MATCH($H10&I$9,$A$2:$A$9&$B$2:$B$9,0)),"")

Ctrl+Shift+Enter

Copy I2 across

Then Copy I2:M2 down


Refer to your sample here: https://www.dropbox.com/s/z8r09zg199j1u2q/madhu_61244_Hui.xlsx

 

[madhu_61244]

Hui,

Thanks for the formula. This is short and sweet. However we need to copy distinct values from first two columns to create row and column headings for crosstab. How can we avoid this. Any thoughts.


Thanks,

Madhu

 

[NARAYANK991]

Hi Madhu ,


Check this file :


http://speedy.sh/Z4c48/Madhu.xlsx


Narayan


Courtesy : http://www.get-digital-help.com/2009/03/30/how-to-extract-a-unique-list-and-the-duplicates-in-excel-from-one-column/

 

[Hui]

Madhu


I have also done a formula based solution here:

https://www.dropbox.com/s/jmf2f57v124f2ra/madhu_61244_Hui2.xlsx


It uses a different technique to Narrayan's