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
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. “

  2. “I have been thinking about this since your other post with the same question.

    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
    Reply: Mail Merge”

  4. “Hi Rebouche,

    I have been thinking about this since your other post with the same question.

    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. “

Leave a Reply

Your email address will not be published. Required fields are marked *


5 + = nine

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>