Finding largest value in subsequent column from matching ID numbers

[Norsemermaid]

Hello,

I've been trying to find a similar problem on the forums but I can't see it, sorry if this is a duplicate.

I'm making a spreadsheet and I need to combine the data in several rows to one row and delete the duplicates.

I have three basic fields that i need to combine at the moment, Shipment, Lplant and RecPl.

The problem is sometimes the lplant is not on the first line, and sometimes I need to take the details from the RecPl column. Also, the data might be a combination of letters and numbers, and sometimes just numbers

So far i've tried doing an if and vlookup combo but that only returns the first value that matches the shipment number, and it doesn't check the second column.

I've attached (hopefully ) a spreadsheet with an example of the formula and some data to show what i mean.

Can someone help please? I don't know if I need a macro or a formula to do this, either is fine by me.

Thanks!