Comparing data in columns
 

Ok, so I understand conditional formatting basics, but this seems over my head.

I want to highlight the row of data if the following parameters are met

Col C (names) Col E (usernames)

If rowXCol C matches rowYCol C AND NOT rowxCol E matches rowyCol E then
Highlight rows x and y

I would like to be able to compare rowXColC to the entire column C to find
matches, but rowXColE only needs to search the rows that match in ColC

Does this make sense?

Thanks in advance.

Comparing data in columns
 

So if A1 matches A7, but E1 doesn't match E7, then highlight it?

It sounds like you're just asking to highlight the unique combinations in A and
E.

Is that right?

So if I had:
a 2
b 1
c 3
a 2
b 2
c 3

In A1:A6 and E1:E6, then

a 2
b 1 <-- highlight this one
c 3
a 2
b 2 <-- highlight this one
c 3

Since those are the only ones that are unique.

If that's what you want, I could use this:
select the range to format|Conditional format
with A1 the activecell:
Formula is: =(SUMPRODUCT(--($A1=$A$1:$A$6),--($E1=$E$1:$E$6)))=1

=======
If, on the other hand, I were looking for duplicates, with the same data in
A1:E6, I'd end up with:

a 2 <-- highlight this one
b 1
c 3 <-- highlight this one
a 2 <-- highlight this one
b 2
c 3 <-- highlight this one

I'd use this formula:
=(SUMPRODUCT(--($A1=$A$1:$A$6),--($E1=$E$1:$E$6)))1

Adjust the ranges to match--but you can't use whole columns (except in xl2007).

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail he
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html


Nick wrote:

Ok, so I understand conditional formatting basics, but this seems over my head.

I want to highlight the row of data if the following parameters are met

Col C (names) Col E (usernames)

If rowXCol C matches rowYCol C AND NOT rowxCol E matches rowyCol E then
Highlight rows x and y

I would like to be able to compare rowXColC to the entire column C to find
matches, but rowXColE only needs to search the rows that match in ColC

Does this make sense?

Thanks in advance.


--

Dave Peterson

Comparing data in columns
 

You're on the right track with the first answer except that the spreadsheet
is 5k rows of unique data.

So maybe I need to sort into matching column C's first, then run this formula?


"Dave Peterson" wrote:

So if A1 matches A7, but E1 doesn't match E7, then highlight it?

It sounds like you're just asking to highlight the unique combinations in A and
E.

Is that right?

So if I had:
a 2
b 1
c 3
a 2
b 2
c 3

In A1:A6 and E1:E6, then

a 2
b 1 <-- highlight this one
c 3
a 2
b 2 <-- highlight this one
c 3

Since those are the only ones that are unique.

If that's what you want, I could use this:
select the range to format|Conditional format
with A1 the activecell:
Formula is: =(SUMPRODUCT(--($A1=$A$1:$A$6),--($E1=$E$1:$E$6)))=1

=======
If, on the other hand, I were looking for duplicates, with the same data in
A1:E6, I'd end up with:

a 2 <-- highlight this one
b 1
c 3 <-- highlight this one
a 2 <-- highlight this one
b 2
c 3 <-- highlight this one

I'd use this formula:
=(SUMPRODUCT(--($A1=$A$1:$A$6),--($E1=$E$1:$E$6)))1

Adjust the ranges to match--but you can't use whole columns (except in xl2007).

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail he
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html


Nick wrote:

Ok, so I understand conditional formatting basics, but this seems over my head.

I want to highlight the row of data if the following parameters are met

Col C (names) Col E (usernames)

If rowXCol C matches rowYCol C AND NOT rowxCol E matches rowyCol E then
Highlight rows x and y

I would like to be able to compare rowXColC to the entire column C to find
matches, but rowXColE only needs to search the rows that match in ColC

Does this make sense?

Thanks in advance.


--

Dave Peterson

Comparing data in columns
 

Also worth noting, I'm working with text, not numbers.

"Dave Peterson" wrote:

So if A1 matches A7, but E1 doesn't match E7, then highlight it?

It sounds like you're just asking to highlight the unique combinations in A and
E.

Is that right?

So if I had:
a 2
b 1
c 3
a 2
b 2
c 3

In A1:A6 and E1:E6, then

a 2
b 1 <-- highlight this one
c 3
a 2
b 2 <-- highlight this one
c 3

Since those are the only ones that are unique.

If that's what you want, I could use this:
select the range to format|Conditional format
with A1 the activecell:
Formula is: =(SUMPRODUCT(--($A1=$A$1:$A$6),--($E1=$E$1:$E$6)))=1

=======
If, on the other hand, I were looking for duplicates, with the same data in
A1:E6, I'd end up with:

a 2 <-- highlight this one
b 1
c 3 <-- highlight this one
a 2 <-- highlight this one
b 2
c 3 <-- highlight this one

I'd use this formula:
=(SUMPRODUCT(--($A1=$A$1:$A$6),--($E1=$E$1:$E$6)))1

Adjust the ranges to match--but you can't use whole columns (except in xl2007).

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail he
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html


Nick wrote:

Ok, so I understand conditional formatting basics, but this seems over my head.

I want to highlight the row of data if the following parameters are met

Col C (names) Col E (usernames)

If rowXCol C matches rowYCol C AND NOT rowxCol E matches rowyCol E then
Highlight rows x and y

I would like to be able to compare rowXColC to the entire column C to find
matches, but rowXColE only needs to search the rows that match in ColC

Does this make sense?

Thanks in advance.


--

Dave Peterson

Comparing data in columns
 

So did you adjust the formula and try it?

Nick wrote:

You're on the right track with the first answer except that the spreadsheet
is 5k rows of unique data.

So maybe I need to sort into matching column C's first, then run this formula?

"Dave Peterson" wrote:

So if A1 matches A7, but E1 doesn't match E7, then highlight it?

It sounds like you're just asking to highlight the unique combinations in A and
E.

Is that right?

So if I had:
a 2
b 1
c 3
a 2
b 2
c 3

In A1:A6 and E1:E6, then

a 2
b 1 <-- highlight this one
c 3
a 2
b 2 <-- highlight this one
c 3

Since those are the only ones that are unique.

If that's what you want, I could use this:
select the range to format|Conditional format
with A1 the activecell:
Formula is: =(SUMPRODUCT(--($A1=$A$1:$A$6),--($E1=$E$1:$E$6)))=1

=======
If, on the other hand, I were looking for duplicates, with the same data in
A1:E6, I'd end up with:

a 2 <-- highlight this one
b 1
c 3 <-- highlight this one
a 2 <-- highlight this one
b 2
c 3 <-- highlight this one

I'd use this formula:
=(SUMPRODUCT(--($A1=$A$1:$A$6),--($E1=$E$1:$E$6)))1

Adjust the ranges to match--but you can't use whole columns (except in xl2007).

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail he
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html


Nick wrote:

Ok, so I understand conditional formatting basics, but this seems over my head.

I want to highlight the row of data if the following parameters are met

Col C (names) Col E (usernames)

If rowXCol C matches rowYCol C AND NOT rowxCol E matches rowyCol E then
Highlight rows x and y

I would like to be able to compare rowXColC to the entire column C to find
matches, but rowXColE only needs to search the rows that match in ColC

Does this make sense?

Thanks in advance.


--

Dave Peterson


--

Dave Peterson

Comparing data in columns
 

Dave,

Thanks for the tip. I ended up sorting by one column and running a
conditional format (Cell equals the one below it [without the $]) and applied
that to the columns I wanted to compare. Now if I see a gap in color, I look
at that one harder.

Regards!

"Dave Peterson" wrote:

So did you adjust the formula and try it?

Nick wrote:

You're on the right track with the first answer except that the spreadsheet
is 5k rows of unique data.

So maybe I need to sort into matching column C's first, then run this formula?

"Dave Peterson" wrote:

So if A1 matches A7, but E1 doesn't match E7, then highlight it?

It sounds like you're just asking to highlight the unique combinations in A and
E.

Is that right?

So if I had:
a 2
b 1
c 3
a 2
b 2
c 3

In A1:A6 and E1:E6, then

a 2
b 1 <-- highlight this one
c 3
a 2
b 2 <-- highlight this one
c 3

Since those are the only ones that are unique.

If that's what you want, I could use this:
select the range to format|Conditional format
with A1 the activecell:
Formula is: =(SUMPRODUCT(--($A1=$A$1:$A$6),--($E1=$E$1:$E$6)))=1

=======
If, on the other hand, I were looking for duplicates, with the same data in
A1:E6, I'd end up with:

a 2 <-- highlight this one
b 1
c 3 <-- highlight this one
a 2 <-- highlight this one
b 2
c 3 <-- highlight this one

I'd use this formula:
=(SUMPRODUCT(--($A1=$A$1:$A$6),--($E1=$E$1:$E$6)))1

Adjust the ranges to match--but you can't use whole columns (except in xl2007).

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail he
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html


Nick wrote:

Ok, so I understand conditional formatting basics, but this seems over my head.

I want to highlight the row of data if the following parameters are met

Col C (names) Col E (usernames)

If rowXCol C matches rowYCol C AND NOT rowxCol E matches rowyCol E then
Highlight rows x and y

I would like to be able to compare rowXColC to the entire column C to find
matches, but rowXColE only needs to search the rows that match in ColC

Does this make sense?

Thanks in advance.


--

Dave Peterson


--

Dave Peterson

Comparing data in columns
 

Sorting the data may make it easier to find the problems--but it isn't necessary
for the formula to work.

Personally, I'd drop the format|Conditional formatting and put that formula in
an adjacent cell. Then I could use data|filter|autofilter to show the
problem--I wouldn't be depending on me being able to pick out the colors.

Nick wrote:

Dave,

Thanks for the tip. I ended up sorting by one column and running a
conditional format (Cell equals the one below it [without the $]) and applied
that to the columns I wanted to compare. Now if I see a gap in color, I look
at that one harder.

Regards!

"Dave Peterson" wrote:

So did you adjust the formula and try it?

Nick wrote:

You're on the right track with the first answer except that the spreadsheet
is 5k rows of unique data.

So maybe I need to sort into matching column C's first, then run this formula?

"Dave Peterson" wrote:

So if A1 matches A7, but E1 doesn't match E7, then highlight it?

It sounds like you're just asking to highlight the unique combinations in A and
E.

Is that right?

So if I had:
a 2
b 1
c 3
a 2
b 2
c 3

In A1:A6 and E1:E6, then

a 2
b 1 <-- highlight this one
c 3
a 2
b 2 <-- highlight this one
c 3

Since those are the only ones that are unique.

If that's what you want, I could use this:
select the range to format|Conditional format
with A1 the activecell:
Formula is: =(SUMPRODUCT(--($A1=$A$1:$A$6),--($E1=$E$1:$E$6)))=1

=======
If, on the other hand, I were looking for duplicates, with the same data in
A1:E6, I'd end up with:

a 2 <-- highlight this one
b 1
c 3 <-- highlight this one
a 2 <-- highlight this one
b 2
c 3 <-- highlight this one

I'd use this formula:
=(SUMPRODUCT(--($A1=$A$1:$A$6),--($E1=$E$1:$E$6)))1

Adjust the ranges to match--but you can't use whole columns (except in xl2007).

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail he
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html


Nick wrote:

Ok, so I understand conditional formatting basics, but this seems over my head.

I want to highlight the row of data if the following parameters are met

Col C (names) Col E (usernames)

If rowXCol C matches rowYCol C AND NOT rowxCol E matches rowyCol E then
Highlight rows x and y

I would like to be able to compare rowXColC to the entire column C to find
matches, but rowXColE only needs to search the rows that match in ColC

Does this make sense?

Thanks in advance.


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson