Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Comparing two columns of data | Excel Discussion (Misc queries) | |||
Comparing columns of data | Excel Discussion (Misc queries) | |||
Comparing 2 columns of data | New Users to Excel | |||
comparing columns of data | Excel Worksheet Functions | |||
Comparing Data in 2 columns | Excel Worksheet Functions |