Comparing data in 4 different columns??

[stodd72]

Hello, I'm new to this forum and hoping i can find out some information i'm in need of. I have been tasked by my boss at work to find information that is the same in an excel worksheet.

Ok, I have 4 columns with data in alot of rows of each column. I need to compare all 4 columns and mark each cell in the columns that is not in all 4 of the columns. If 1 of the columns has it i need to mark it like "red" and if 2 of the 4 columns has it i need to mark in "red".

All the data in the 4 columns should be the same in all the rows. What we are trying to see is what all is different. If anyone can help me it would be a great thanks!

Thanks
Shane

Ex.
Col A
aaron.c
abbey.joseph
abby.andre

Col B
aaron.c
abbey.joseph
abby.andre

Col C
aagron
aaron.c
abbey.joseph
abby.andre

Col D
aagron
aalbo
aaron.c
abbey.joseph
abby.andre

 

[vconfused]

Not suer if I completely undertand, but if you want to see what data is not listed 4 times you can use conditional formatting. In the simplest scenario, if you want to see all values that are not listed 4 times as red, click on the first cell that contains data and, go to Format->Conditional Formatting, select the drop-down box and choose "Formula is", and type in =COUNTIF($A:$D,A1)<4 (if the data is in columns A through D and replace A1 with the cell that you are on, click the Format button, select the Patterns tab, click on the red and click OK, OK. Then right-click and drag that cell to column D and when you let go of the right-mouse button select Fill Formatting Only. Now with that row selecting right-click the Fill Handle and drag down to the last row, let go and select Fill Formatting Only.

The flaw is that is does a total count, so if you have 2 matches in column A, one match in B, and one in C, it will NOT mark it as the total count is 4.

The reason I say to drag out the formatting is because I just tried it by selecting all of the data first and it seemed to freeze up Excel.

Hope this helps,
Nick

 

[stodd72]

Nick,

Got a couple of questions about your post. You said:

"Then right-click and drag that cell to column D and when you let go of the right-mouse button select Fill Formatting Only. Now with that row selecting right-click the Fill Handle and drag down to the last row, let go and select Fill Formatting Only."

I'm not sure what you mean, When i put the formula in conditional formatting it turned cell A1 red. Can you please give me a few more instructions on this part?

Also, is there a way to count up all the cells that are "Red" and put that number at the bottom of each column, this would help me to see exactly how many cells are different in each column.

Thanks so much for your help, its saving me alot of time!

Shane

 

[vconfused]

As for the first part, if you have a cell selected, by default, you should see a little black dot at the bottom-right-corner of the cell. This is the Fill Handle (which can be turned off in Tools->Options). Normally you would click on this and drag it with your left-mouse button to repeat the formula, series, etc. However, if you click on it with your right-mouse button and drag, then when you release the mouse button, you will be prompted with a few choices. One is to "Fill Formatting Only". This way the formatting (in our case the conditional formatting) is copied to the cells, but the cell values are not changed.

 

[vconfused]

As for the second part, I do not know how to get a count of formatted cells in a column without using code. However code like:
Function getRedCount()
Dim cell As Range
Dim intCount As Integer
Application.Volatile

For Each cell In Range(Cells(2, Application.Caller.Column), Cells(Application.Caller.Row - 1, Application.Caller.Column))
If cell.Interior.ColorIndex <> xlNone Then intCount = intCount + 1
Next
getRedCount = intCount
End Function

does not seem to work with Conditional Formatting. Maybe someone else has a better idea.