# Comparing Lists Using the COUNTIF Formula

To compare between two Lists:

Step 1: Check each List

1. For cells in column B, insert the formula =IF(COUNTIF(E:E,A2)0,3,1).
2. For cells in column F, insert the formula =IF(COUNTIF(A:A,E2)0,3,2).

Step 2: Merging the Lists into one List of unique names

1. Select cell B2, and click the Sort Ascending icon on the Standard toolbar.
2. Repeat step 1 for cell F2.
3. In column E, beginning with cell E2, copy the names of those employees for whom the number 2 appears in column F, and then paste the names at the bottom of the first List.
Screenshot // Comparing Lists Using the COUNTIF Formula

## 7 thoughts on “Comparing Lists Using the COUNTIF Formula”

1. “When I am transfering any decimal number from datacheet to document, I am not getting the same number (i.e. datasheet = 4.2 in Document it will be 4.200000000000003 (fifteen (15) decimals instead of one as mentioned in my datasheet).
What to do to get exactly the same number in both of them. “

I suspect that it must be related to the way that Excel stores numbers using normal floating point conventions. When that number is transferred via the mail merge to, say, Word, it gets re-expressed in the 15 decimal places that you are seeing.

Note: That is conjecture, I cannot prove it!

If my theory is correct, then my solution would be to transfer the ‘data’ as ASCII text, rather than numbers, thus avoiding the vagueries of floating point arithmetic.

To do this, suppose that you have a set of fields in Excel that will be transferred, via mail merge, to Word.

These fields are in A1:B101, with A1 and B1 being headings (perhaps name and age, to one decimal place (43.1 for example)).

If I am right, to get around the problem, add another column C with heading say, “”Name_Age”” and data in(C2:C99 for example) with the following formula referring to B2 and copy down:

=TEXT(B2,””0.0″”)

The result of this formula will be to take your number in column B, and turn it into text (42.1 becomes “”42.1″” being four characters – no longer a number).

You can now mail merge using the Age_Text field in place of the numeric Age field, and avoid the floating point issue.

Have a go and post back to say if it worked. “

3. “When I am transfering any decimal number from datacheet to document, I am not getting the same number (i.e. datasheet = 4.2 in Document it will be 4.200000000000003 (fifteen (15) decimals instead of one as mentioned in my datasheet).
What to do to get exactly the same number in both of them.
Regards

4. “Hi Rebouche,

I suspect that it must be related to the way that Excel stores numbers using normal floating point conventions. When that number is transferred via the mail merge to, say, Word, it gets re-expressed in the 15 decimal places that you are seeing.

Note: That is conjecture, I cannot prove it!

If my theory is correct, then my solution would be to transfer the ‘data’ as ASCII text, rather than numbers, thus avoiding the vagueries of floating point arithmetic.

To do this, suppose that you have a set of fields in Excel that will be transferred, via mail merge, to Word.

These fields are in A1:B101, with A1 and B1 being headings (perhaps name and age, to one decimal place (43.1 for example)).

If I am right, to get around the problem, add another column C with heading say, “”Name_Age”” and data in(C2:C99 for example) with the following formula referring to B2 and copy down:

=TEXT(B2,””0.0″”)

The result of this formula will be to take your number in column B, and turn it into text (42.1 becomes “”42.1″” being four characters – no longer a number).

You can now mail merge using the Age_Text field in place of the numeric Age field, and avoid the floating point issue.

Have a go and post back to say if it worked.

Alan.

5. “In the eighth ‘paragraph’ – just above the formula, I used the wrong label for the new column, it should have read:

++++

If I am right, to get around the problem, add another column C with heading say, “”Age_Text”” and data in(C2:C99 for example) with the following formula referring to B2 and copy down:

++++

Not a major issue, but I’d hate to think it caused any confusion!

Alan. “

6. “Hi Alan,
Congratulations … It is working perfectly. Many thanks for your help.
Rebouche.”