Compare disparute lists and lookup matched values

**pmd** · 03-02-2012, 08:54 AM

Hi,

I need to sum the size of 1500 user mailboxes from a list of 12000 people.

I have list of 1500 user names in Column A that I need to match against the 12000 usernames in Column B (both are in random non-alphabetised order). Column C contains a value for the size of the user's mailbox (user from column B). I want to identify in Column D which names in Column B are found in Column A so I can then filter and sum the mailbox sizes.

I'm having trouble working out which combination of formulas I need. I've tried COUNTIFs and VLOOKUPs but I'm not getting it right.

Many thanks!

**Fotis1991** · 03-02-2012, 09:02 AM

Hi

So in D1, put this formula.

=IF(COUNTIF($A$1:$A$10;B1)>0;"Duplicates";"")

Copy down.

Hope to helps you.

**pmd** · 03-02-2012, 10:06 AM

Thanks - I tried that but it doesn't work properly as when I cross-reference the filtered list of rows with "Duplicate" in Column D it does not return all of the duplicate i.e. some names in Col B that Match to Col A get missed - even thought spelling/syntax/case etc is exactly the same.

**Fotis1991** · 03-02-2012, 10:16 AM

...If you mean something else, pls upload a sample workbook.

**pmd** · 03-02-2012, 11:17 AM

Thanks. I can't upload the spreadsheet as it contains actual usernames that I can't publish becuase of data protection legislation.

I see from your sample spreadsheet how the formula works. I'll have to see if I can work out why it doesn't seem to pick up all the entries.

Thanks for your help. I'll come back and set the thread to solved once I figure out what it is.

Thanks again.