Complex reordering of cells from one spreadsheet into another

Hello

I need to create a new worksheet from an original worksheet but the new worksheet needs to be ordered in a specific way.

My original worksheet has columns "primer", "DNAid", "allele1", allele2"

Primer DNAid allele1 allele2
TB136 1 333 333
TB136 2 321 333
TB136 3 333 333
TB117 3 106 108
TB117 5 106 106
TB117 2 106 108
TB187 3 166 176
TB187 1 166 176
TB187 2 166 176
TB109 5 119 123
TB109 4 119 119
TB109 1 119 119

I need to interrogate the data somehow to help me automatically create a new spreadsheet which presents the data in a different way.
My new spreadsheet has a primary column of "DNAid" and the "primer" column has now been separated by each individual primer.
DNAid TB136 TB117 TB187 TB109
allele1 allele2 allele1 allele2 allele1 allele2 allele1 allele2
1 333 333 166 176 119 119
2 321 333 106 108 166 176
3 333 333 106 108 166 176
4 119 119
5 106 106 119 123

Is there a function or formula that I can use to do this?

I'd appreciate your help as last time I did it manually and wasted 3 days of my life as I have thousands of rows.

Regards
Poppy

I recommend selecting the data range, and using Insert Pivot Table.

DNAid as Row, Primer as Columns, Allele1 and Allele2 as Value.

You could be done in seconds.

No problem. I'm no fan of PivotTables myself, but sometimes they're just the right tool for the job.

This is what a Pivot table can quickly present. Where there are no values for the intersections of column and row the cells will be blank.