RE: Comparing two columns

Well, I'm about to get really confused! I need a little clarification here.
Where you say "do not show this on the report" and "show this on the report"
- exactly what does "the report" mean? Are you talking about the reporting
of duplicate row numbers for the D/J column test results?

"Sharon" wrote:

Let me give you the entire picture (at least as far as I received
instructions!).

First, I have to compare columns D and J (and columns J and D). I have
created columns K and L with the formulas as follows:

Column K: =COUNTIF($J$2:$J$457,D2)>0
Column L: =COUNTIF($D$2:$D$457,J2)>0

Then, I have added column M, with the formula to determine if either columns
K and L are true:

Column M: =OR(K2,L2)


Second, I have to compare columns A and G (and columns G and A). I have
created columns N and O with the formulas as follows:

Column N: =COUNTIF($A$2:$A$457,G2)>0
Column O: =COUNTIF($G$2:$G$457,A2)>0


Then, I have added Column P, with the formula to determine if either columns
N and O are true:

Column P: =OR(N2,O2)


The end result would be if Column D has any duplicates in Column J, then
show the rows where the duplicate exists.

If Column J has any duplicates in Column D, then show the rows were the
duplicate exists.

If Column D has no duplicates AND Column A and G are equal, do not show this
on the report.

If Column D has no duplicates AND Column A and G are not equal, show this on
the report.

So, I guess I need code to add to another column that would show the rows
where the duplicates exist?

--
S


"JLatham" wrote:

Have you got another spare column hanging around? I'd have to use some VB
coding to find exact location of multiple duplicates and report them. This
could be in the form of a user defined function (UDF) that could be used like
a formula in a cell, or it could be written to respond to a double-click on a
cell in column D (or J or both) to report duplicates in the other columns via
a message when a cell in one of those columns is double-clicked.


"Sharon" wrote:

This works perfectly! One more step, if possible?

Is there anyway to find out which rows have the duplicate values? For
instance, if column D2 has a value and there is a duplicate in J6, J7, J8, is
there a way to have it list the cells where the duplicate is located?

I appreciate your help.

--
S


"JLatham" wrote:

Maybe I don't understand well enough, but it looks to me like this would work
(change ranges if necessary)
in first cell in K:
=COUNTIF($J$2:$J$457,D2)>0
That will give you TRUE if value in D2 appears anywhere in J2:J456
in first cell in L:
=COUNTIF($D$2:$D$457,J2)>0
again, if value in J2 appears anywhere in D2:D457, will return TRUE
finally in first cell in L:
=OR(K2,L2)
Will return TRUE if only K2 is True or only L2 is true or both are True: it
only returns FALSE when both K2 and L2 are false.


"Sharon" wrote:

I have a spread*** where I need to compare two columns and get a true,
false value.

Column D

Column J


I need to compare column D to column J: I used the following in column K:

=ISNA(MATCH(D2,$J$2:$J$457,FALSE))

to compare J to D in column L: =ISNA(MATCH(J2,$D$2:$D$457,FALSE))

I can see that the first one is incorrect because there is a duplicate value
in D2 and J6.

Then, I want to have a column where the value returns true if column K or L
is true:

=IF(OR(K2=TRUE,L2=TRUE),TRUE)


I tried th

=VLOOKUP(D2,'*** 1'!$J$2:$J$457,2,FALSE)

which I got from another post.

Can someone please tell me how to do this? Thanks.


--
S
.