Solved

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

Posted on 2011-05-10
Medium Priority
260 Views
Hi,
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?

Thanks,

Book2.xlsx
0
Question by:Tolgar
• 2

LVL 85

Accepted Solution

Rory Archibald earned 2000 total points
ID: 35730520
In C2 on sheet1 enter:
=VLOOKUP(A2,Sheet2!A:B,2,FALSE)
and copy down.

HTH
Rory
0

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?

Thanks,
0

LVL 85

Assisted Solution

Rory Archibald earned 2000 total points
ID: 35731340
You can use a formula like:
=countif(c:c,"Dev")
0

Featured Post

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…
Suggested Courses
Course of the Month15 days, 18 hours left to enroll