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
The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.
"Hi Alan,
Congratulations ... It is working perfectly. Many thanks for your help.
Rebouche."
"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. "
"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.
"
"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"
"Congratulations ... It is working perfectly. Many thanks for your help.
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. "
"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. "