Merging Cells with common values in VBA

Hi I have a raw data including #NA and 0 as well, like below

Raw Data

Location Dept
xyz HR
abc Accounts
def Finance
rst HR
abc HR
#NA
0
def HR
abc Finance
xyz Accounts
rst Accounts
#NA
0


and I need output like this :

Location Dept
XYZ HR
Accounts
abc HR
Accounts
Finance
def HR
Finance
rst HR
Accounts

Here location cells should be merged if have more than 1 department, cannot use Pivot table as need to filter many other values but stuck only in this part.

Pls Help