Compare columns pull unique values and first duplicate value

[Dee]

I have 2 worksheets:

First worksheet, 1st column has department abbreviations, 2nd columns
has the formal department names names
Second worksheet, 1st column has abreviations, 2nd column contains
department nicknames, 3rd column contains floor space by the floor
for
each department, 4th column contains the total workspace for each
department. (I did a vlookup to sum floors)


On a new worksheet
First column compare abreviations from first column in other
worksheets and return the nickname (unique values and first
duplicate)
and in the
Second column return the total workspace for the department (unique
values and first duplicate)