Computer Problems? Computing.Net has over 1,000,000 posts about all things technology related! Over 90% answered within 24 hours! Click here to start participating now! Also, be sure to check out the New User Guide.
excel: comparing cells in rows of d
Name: aks_1978 Date: October 5, 2006 at 06:32:13 Pacific OS: windows xp CPU/Ram: p4 Product: intel
Comment:
Hi there. I'm hoping someone can help me simplify a function that I'm using in excel. Basically I have rows of data, e.g.
Row 1 - XOOXO Row 2 - XXXXO Row 3 - OOXOO
and so forth. What I want to know is between each row, how many of the adjacent cells contain different values. So using the above eg the answer would be:
Row 2 compared to 1 = 2 Row 3 compared to 2 = 3
I can do this using an IF statement to compare each set of cells, but my rows will have 30 columns in there so that's going to be a big IF statement!
So I need some sort of RANGE compare function. Any ideas?
Name: wizard-fred Date: October 5, 2006 at 06:48:10 Pacific
Reply:
I think a program to compare the rows by looping would be faster, but what is the significance of the answers. Does a difference in position changes the weighing of same value answers?
0
Response Number 2
Name: jon_k Date: October 5, 2006 at 06:59:13 Pacific
Reply:
for irow = 2 to 500 diff = 0 for icol = 1 to 30
if sheet1.cells(irow,icol).value <> sheet1.cells(irow - 1, icol).value then diff = diff +1 next icol sheet1.cells(irow, 31).formula = diff next irow
Something like that?
0
Response Number 3
Name: aks_1978 Date: October 5, 2006 at 07:09:22 Pacific
Reply:
wizard, i just want the difference to be output into another column, e.g. Column ABCDEFG Row 1 - XOOXO 0 Row 2 - XXXXO 2 Row 3 - OOXOO 3
then afterwards I use that difference column in other calculations.
jon, thanks for that. I was trying to get it working using the built-in functions but if you guys think a loop is the best way then cool. But, how do I apply the loop you've given into the worksheet?
0
Response Number 4
Name: aks_1978 Date: October 5, 2006 at 08:00:52 Pacific
Reply:
Ok, realised that I needed to put it into a macro. All done now and working. Thanks guys.
0
Response Number 5
Name: aks_1978 Date: October 8, 2006 at 05:49:37 Pacific
Reply:
Just an update to say I figured out a way to do it without using a macro. I converted the letters to numbers, so the rows are now like:
1001000111000 0001011110001
and then I can use the SUMPRODUCT function to get the result I wanted.
Summary: I'm confused, but we'll get to that later. First, about your code: As far as I can tell, your code is supposed to prevent users from editing any cell in Column E. At least that what it does when I put...
Summary: I'd like to know if there's a way of locking a certain cell so that noone can overwrite the formula that's in it. I have Excel 97 and have created a sheet where several cells contain formulas. My coll...
Summary: Hi, Please help me in comparing dates in Excel. Let the cells have dates as given below... A2 = 10-Jan-2009 B2 = 20-Feb-2009 C2 = 30-Mar-2009. I need a formula that checks if C2 is equal to an...
0 
