Excel - compare 2 columns, highlight difference in column 2

QuestionEdit

QUESTION: Data is contained in Column 1) C, D; 2) F, G; 3) I, J; 4) L, M; 5) O, P... same pattern.

I need to compare the 1st column (C) with the second (D), and like wise for Case 2, 3, 4.... each column has about 44 rows of data and each row from each column needs to get compared with the next. Starting row where the actual data exists is C12 and last cell is C98. Cells maybe blank also.

thanks.

Columns can contain Integer, time, string, text, etc. and must make use of the TRIM feature as well.

ANSWER: Nevil,

Select D12. Hold down the shift key and select G98

do format => conditional formatting

in Excel 2003 and earlier, change Cell value is to Formula is and put in this formula

=Countif(C$12:C$98,D12)=0

then select the format button, select fill or pattern and select the color you want to use for a highligh.

OK your way out.

In Excle 2007, conditional formatting is found on the home tab. Click that, then select new rule. Then select the last item "Use a formula to determine which cells to format"

then in the resulting dialog, put in the above formula and then hit the format button and select the color under fill tab.

That worked for me.

The formula is dependent on the area D12:G98 being selected with D12 as the activecell. It is written relative to D12.

It worked for me.

I don't know how the trim feature would fit into this. Perhaps go to H12 and put in

=Trim(C12) and drag fill that across 5 columns and down 44 rows. Then select the whole area and do Edit=>copy, then go to the original area and do Edit=>Paste Special and select values.

Now delete the columns with the formulas. Then apply the conditional formatting. Do this on a copy of your data since it will change your data.

---------- FOLLOW-UP ----------

QUESTION: That worked. Thank you very much. I took your conditional format formula and made it output a RED if there is no match between 2 columns.

Is there a way to detect the output of Conditional formatting formula? I want to add a master cell on top of each 'case' (column D, F, H, K...) where it should read/detect the output of condittional formating in cell D12:D98, F12:F98...) and color code Cell D10, F10... a "green" if there are no negative outcomes or color that cell "red" if one or more cells of that column are Red.

AnswerEdit

Nevil,

You can't detect the actual conditional format color of the cell - but formulas don't support that anyway even for regular formatting.

You can put this formula in D10: =44-SUMPRODUCT(COUNTIF(C$12:C$98,D$12:D$98))

then copy and paste it to the right for the other cells.

then you can apply conditional formatting to those cells with a formula like

=D10>0 Format color Red (make the default color green)

or if you want those cells to have some other value, then you can use the above formula in as the conditional formatting formula

=(44-SUMPRODUCT(COUNTIF(C$12:C$98,D$12:D$98)))>0