I have a name range with columns A- D and rows that are set dynamic via VBA with the last row of data.

I need to copy some data out of the name rang into another sheet.

My name range looks like

Key Product Location Price
1 Table UK $2.00
1 Table USA $8.00
1 Table China $6.00
1 Table Canada $4.00
1 Chair UK $12.00
1 Chair USA $18.00
1 Chair China $16.00
1 Chair Canada $14.00
1 Desk UK $22.00
1 Desk USA $28.00
1 Desk China $26.00
1 Desk Canada $24.00




but I need to copy the data to a range that looks like
Location Product A Product B Product C
UK $2.00 $12.00 $22.00
USA $8.00 $18.00 $28.00
China $6.00 $16.00 $26.00
Canada $4.00 $14.00 $24.00


they can be other Prodcuts within the first or source data like Seat, Lamp, etc but I don't need does other types in the output or destination.

any ideas?

Do I have to go cell by cell on the source and check the product then copy to the destination ?
can I use some type advance filter?