Convert similar row into one row with column

[chuapm]

excel1.png


How do you convert the excel as shown above?

[Pepe Le Mokko]

Welcome to the forum

Please attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[chuapm]

Hi
As attached.
I'm not an expert in Excel but need to convert the left side data into the right side as shown in the picture.

Many thanks

[JeteMc]

Perhaps the following will help:
The question ID's are populated using: =IFERROR(IF(MATCH(0,INDEX(COUNTIF(F$2:F2,B$2:B$20),,),)>COUNTA(B$2:B$20),"",INDEX(B$2:B$20,MATCH(0,INDEX(COUNTIF(F$2:F2,B$2:B$20),,),))),"")
The usernames are populated using: =IFERROR(IF(MATCH(0,INDEX(COUNTIF($F2:F2,$A2:$A20),,),)>COUNTA($A2:$A20),"",INDEX($A2:$A20,MATCH(0,INDEX(COUNTIF($F2:F2,$A2:$A20),,),))),"")
The scores are populated using: =INDEX($C$2:$C$20,AGGREGATE(15,6,(ROW($C$2:$C$20)-ROW($C$1))/($A$2:$A$20=G$2)/($B$2:$B$20=$F3),1))
Let us know if you have any questions.

[alansidman]

a very simple way to achieve this is to use Power Query/Get and Transform. Here is the Mcode and your file with the solution using PQ

Please Login or Register  to view this content.

[Gregb11]

Or, I think you could just create a pivot table. See attached.