Lookup Hostname and retreive data based on match

[fish221171]

Dear Members,

Hope someone can help me I have some CMDB merged data that I need to clean (See example sheet attached). What I am trying to do is match Device name (some are the same) but the data in column F is more up to date so I want to copy that and only have one entry.

Example:

Computer Name Device Name OS OS Version OS Patch Level Role
3DCPRI-ADDS01 3DCPRI-ADDS01 Windows Windows Server 2008 Enterprise without Hyper-V Service Pack 2
3DCPRI-ADDS01 3DCPRI-ADDS01 Windows Server IWP2 Domain controller for ad.hq.dept

All I want to do is if I get a match on the device name is move the role IWP2 Domain controller for ad.hq.dept to the Role column which is blank for that device name. So it should look like:

Computer Name Device Name OS OS Version OS Patch Level Role
3DCPRI-ADDS01 3DCPRI-ADDS01 Windows Windows Server 2008 Enterprise without Hyper-V Service Pack 2 IWP2 Domain controller for ad.hq.dept
3DCPRI-ADDS01 3DCPRI-ADDS01 Windows Server

Can anyone help me with this?

Thanks