Comparing data in 4 different columns??

stodd72

New Member
Joined
Jan 22, 2005
Messages
2
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
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

vconfused

Well-known Member
Joined
Jun 11, 2004
Messages
547
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

New Member
Joined
Jan 22, 2005
Messages
2
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

Well-known Member
Joined
Jun 11, 2004
Messages
547
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

Well-known Member
Joined
Jun 11, 2004
Messages
547
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.
 

Forum statistics

Threads
1,147,696
Messages
5,742,679
Members
423,747
Latest member
Shadeslayers09

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top