Fix EXACT function so it always compares in the same row.

[soilcon1]

I am using the EXACT function in column B to compare if information in column
A is the same as column C on a spreadsheet. If it is not, I am inserting
cells above all the data in column C until I find a match with column A.
What is happening is that the EXACT function seems to stay fixed with it's
original cell in column C. Example:

A1=apple, C1=orange, B1=EXACT(A1,C1) results in FALSE

But, A2=orange and C1=orange, so I insert a cell above the cell in C so that
it becomes C2. So now:

A2=orange and C2=orange. But the EXACT function in column B in row 2 now
says:

B2=EXACT(A2,C3). C3=banna, so the result if False.

I want the function to remain B2=EXACT(A2,C2), NOT change to C3.

When more data is entered into the rows A and C later, I need to be able to
fill the EXACT formula down, so I don't think I can use $. I don't want to
enter the function individually into each cell in column B.

Any ideas?

 

[Max]

To satisfy the process that you describe,
you could place this in B1:
=EXACT(OFFSET(INDIRECT("A1"),ROWS($1:1)-1,),OFFSET(INDIRECT("A1"),ROWS($1:1)-1,2))
Fill down as required. It'll return the results that you seek.

 

[Bernie Deitrick]

A macro would do it, but to use formulas, you can follow these
instructuions.

Insert two columns at column C, so that your original data in column C is
moved to column E. Then, in the new cell C1, use the formula

=IF(ISERROR(MATCH(ROW(),D,FALSE)),"",INDEX(E:E,MATCH(ROW(),D,FALSE)))

and in cell D1, use the formula

=IF(SUMPRODUCT(EXACT($A$1:$A$100,E1)*1)>1,"There are " &
SUMPRODUCT(EXACT($A$1:$A$100,E1)*1) & " EXACT matches for " & E1,
SUMPRODUCT(EXACT($A$1:$A$100,E1)*ROW($A$1:$A$100)))

Increas the row of the $A$100s to reflect your list in column A.

Then copy C1 down to match your list in column A, and copy D1 down to match
your list in column E.

Then copy column C and past special values, and get rid of columns D and E.

HTH,
Bernie
MS Excel MVP

 

[Ron Coderre]

Try this:

C1: =EXACT(INDEX(A:A,ROW()),INDEX(C:C,ROW()))
Copy that formula down as far as you need

Does that help?
Post back if you have more questions.
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

 

[Max]

C1: =EXACT(INDEX(A:A,ROW()),INDEX(C:C,ROW()))

Believe Ron meant to place the above formula in B1, not in C1 <g>

---

 

[soilcon1]

Ron's suggestion worked great. Yes, Max is right, the placement is in B1.
Thanks everyone!