Count number of pairs between 2 columns

**Johnald** · 01-09-2013, 12:57 PM

Hi all, I've been looking online for a while now and can't find a solution to my problem.

To keep my problem simple, I will use this example.

COL A = a list of employees that cannot take vacation on the same day
COL B = a list of employees that have booked vacation for a certain day

Both these lists are populated with names from the same defined list so they will be identical (=EMPLOYEES)
There are no duplicates within a column (ie a name might appear in A and B, but will not appear twice in col A)

A...................B
John...............Bill
Bill.................Steve
Dave..............Jim

What I would like to find:

In a cell (ex C1) I want a formula that will count how many names appear in both columns. With above example, since Bill is the only person who appears in both A and B it would return 1. If Bill and Steve both appeared in A and B, it would return 2 etc.

(Just to explain why I need the number, I have several 'conflict groups' who are allowed X number of employees off per day. If col A is conflict group 1, and they are allowed only 2 employees off/day, I will have it setup so that if the number in C1 exceeds 2, I get a warning)

Thanks in advance for your help!!

Cheers John

**FDibbins** · 01-09-2013, 01:04 PM

Based on the sample you provided, try this, copied down...

=COUNTIF($A$1:$A$3,B1)

adjust range as needed

**Fotis1991** · 01-09-2013, 01:07 PM

Hi

Assuming that your data start in row 1. then an easy way to do is this.

In a column(let's say d1) and copy down use this formula.

Please Login or Register  to view this content.

Column D, will be a helper and hidden column.

Then in C1, use this one.

Please Login or Register  to view this content.

**Johnald** · 01-09-2013, 01:55 PM

My apologies, I should have been more specific. I'm familiar with the above listed techniques but I need to find this information in 1 cell (as opposed to dedicating a column to finding this information) because I have a grid of cells, where the columns are my conflict groups and the rows are days of the year.
So
-cell C1 would find how many people in Group 1 are off Jan 1st
-cell C2 would find Group 1 off Jan 2nd
-cell D1 would find how many people in Group 2 are off Jan 1st
-cell D2 would find Group 2 off Jan 2nd

I will try to post an example up shortly
Hopefully this explains it better, sorry for the confusion. Thanks again, John

**FDibbins** · 01-09-2013, 01:57 PM

To best describe or illustrate your problem you would be better off attaching a dummy workbook. The workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

**Johnald** · 01-09-2013, 02:21 PM

example.xlsx

here is an example of what I'm trying to do

**Fotis1991** · 01-09-2013, 02:45 PM

A little...ugly but works.

Please Login or Register  to view this content.

**Johnald** · 01-09-2013, 06:21 PM

Thanks Fotis, I had basically the same thing as you to begin with, but have 10 groups and was hoping to avoid using the long form. But this will do the trick