compare and retrieve unique and first duplicate values

[deekarvois]

I have 2 worksheets (in two different workbooks):


First worksheet, 1st column has department abbreviations, 2nd column

has the formal department 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)

 

[keymaster]

Checkout this for a general approach on how to solve it http://chandoo.org/wp/2008/11/06/unique-duplicate-missing-items-excel-help/


essentially you use COUNTIF to find the number of occurrences of each value and then use that in a lookup formula to get uniques