How to match some lines according to a criteria in Excel?

Posted on 2011-05-10
Medium Priority
Last Modified: 2012-06-27
I have an excel sheet which i am sending in the attachment.

In sheet 1 I have this:

Name       Group
James      A
Brian      B
Richard      D
Jake              C
Lora         B

In Sheet 2:

Name       Department
Jake              Dev
James      QE
Richard      Dev
Lora              QE
Brian      Dev

What I want to make in Sheet 1 is:

Name       Group     Department
James      A            QE
Brian      B            Dev
Richard      D            Dev
Jake              C            Dev
Lora         B            QE

This is just an example and the actual list is so long.

How can I make it diagrammatically in Excel?



Question by:Tolgar
  • 2
LVL 85

Accepted Solution

Rory Archibald earned 2000 total points
ID: 35730520
In C2 on sheet1 enter:
and copy down.


Author Comment

ID: 35731231
Thank you it worked. But now I cannot count how many Dev and QE are in the Department column by simply doing ctrl + F and finding all.

How can I count them?

LVL 85

Assisted Solution

by:Rory Archibald
Rory Archibald earned 2000 total points
ID: 35731340
You can use a formula like:

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This article describes a serious pitfall that can happen when deleting shapes using VBA.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question