Merge rows with a few same columns in excel

I have an excel sheet with data similar to the following

Reg_id     Name     Product_Owned     Product_model
----------------------------------------------------
34        Tom       Laptop            Dell Studio15 
34        Tom       Mobile            HTC Sense 
34        Tom       Camera            Canon xyz 
67        Steven    Laptop            Apple Mac Book 
67        Steven    Phone             Apple iPhone4 
88        Sunny     Mobile            Samsung i9000 
88        Sunny     Camera            Sony xyz

I have only three values in the Product_Owned column - Lap开发者_开发问答top, Camera & Mobile. I want their information to be stored as separate columns in a single row for the same person.

I want to make it in this format:

Reg_id     Name     Laptop           Mobile           Camera
--------------------------------------------------------------
34         Tom      Dell Studio15    HTC Sense       Canon xyz
67         Steven   Apple Mac Book   Apple iPhone4
88         Sunny                     Samsung i9000   Sony xyz

Can't do it manually as there are ~50000 rows. Please give some suggestions..

Create a unique list of Reg_ID and Name in an adjacent table, similar to what you have set up in the second table.

Create a header for Laptop, Mobile and Camera, as you have, then below, enter the following equation in H2 as an array formula, assuming the first table is in cells A1:D8 and the newly created in F1:J4:

=INDEX($D$2:$D$8,MATCH($F2,$A$2:$A$8,0)+MATCH(H$1,OFFSET($C$2,MATCH($F2,$A$2:$A$8,0)-1,0,COUNTIF($A$2:$A$8,$F2)),0)-1)

This will return the value for the corresponding Reg_ID and Product_Owned, and an '#NA' when a Product_Owned isn't present. If you wanted to return a blank rather than the '#NA', you could add an ISNA() formula as such:

=IF(ISNA(INDEX($D$2:$D$8,MATCH($F2,$A$2:$A$8,0)+MATCH(H$1,OFFSET($C$2,MATCH($F2,$A$2:$A$8,0)-1,0,COUNTIF($A$2:$A$8,$F2)),0)-1)),"",INDEX($D$2:$D$8,MATCH($F2,$A$2:$A$8,0)+MATCH(H$1,OFFSET($C$2,MATCH($F2,$A$2:$A$8,0)-1,0,COUNTIF($A$2:$A$8,$F2)),0)-1))