Compare Data in one cell to another cell (but for LOTS of cells)

RayJay

Board Regular
Joined
Feb 24, 2009
Messages
69
Hi all,

I have a spreadsheet which looks like this:

Rich (BB code):
<TABLE style="WIDTH: 171pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=225 border=0 x:str><COLGROUP><COL style="WIDTH: 19pt; mso-width-source: userset; mso-width-alt: 914" span=9 width=25><TBODY><TR style="HEIGHT: 13.5pt" height=18><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 19pt; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent" align=right width=25 height=18 x:num>1</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext; WIDTH: 19pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent" align=right width=25 x:num>2</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext; WIDTH: 19pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent" align=right width=25 x:num>3</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext; WIDTH: 19pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent" align=right width=25 x:num>4</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext; WIDTH: 19pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent" width=25></TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext; WIDTH: 19pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent" align=right width=25 x:num>1</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext; WIDTH: 19pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent" align=right width=25 x:num>2</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext; WIDTH: 19pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent" align=right width=25 x:num>3</TD><TD class=xl26 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext; WIDTH: 19pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent" align=right width=25 x:num>4</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent" height=18>A</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent">B</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent">C</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent">D</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent"></TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent">W</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent">X</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent">Y</TD><TD class=xl26 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent">Z</TD></TR></TBODY></TABLE>

which goes 97 rows down, and I was wondering: is there an easy way to compare the data which is in 1 on the left (i.e. A) to the data which is in 1 on the right (i.e. W), to compare the data which is in 2 on the left (i.e. B) to the data which is in 2 on the right (i.e. X) and so on...

I also want it to colour the cells Green if they match (e.g. 1 on the left and 1 on the right have the same data in them) and colour the cells Red if they don't match.

I know I can do this with conditional formatting, but that would take a very long time (since there are 388 cells to compare to another 388), and it would probably be easier just to compare them myself, so if anyone knows of a really easy way to do this, or has a macro which will do this, then I would be very happy! :)

Any help would be appreciated.

Thanks very much,

RayJay

(P.S.) The data in the cells is not just numerical: it can either be numerical or alphabetical.

(P.P.S.) I'm using Excel 2003.
 

Some videos you may like

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

texasalynn

Well-known Member
Joined
May 19, 2002
Messages
8,458
is this what you want?
Excel Workbook
ABCDEFGHIJKLM
11234*1234TRUETRUETRUETRUE
2ABCD*WXYZFALSEFALSEFALSEFALSE
Sheet3



if you put this formula in conditional format then make the condition for true the green and red for false

PS, I know you were probably wanting the cells compared to A & W, but was keeping the display simple. Just change the formulas to the proper cell setting
 

RayJay

Board Regular
Joined
Feb 24, 2009
Messages
69
Thank you ever so much - that's absolutely perfect!!

Cheers texasalynn!

RayJay
 

Watch MrExcel Video

Forum statistics

Threads
1,123,085
Messages
5,599,650
Members
414,325
Latest member
kfg1287

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