If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Fix EXACT function so it always compares in the same row.
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? |
#2
|
|||
|
|||
Fix EXACT function so it always compares in the same row.
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. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "soilcon1" wrote: 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? |
#3
|
|||
|
|||
Fix EXACT function so it always compares in the same row.
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,M ATCH(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 "soilcon1" wrote in message ... 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? |
#4
|
|||
|
|||
Fix EXACT function so it always compares in the same row.
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) "soilcon1" wrote in message ... 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? |
#5
|
|||
|
|||
Fix EXACT function so it always compares in the same row.
C1: =EXACT(INDEX(A:A,ROW()),INDEX(C:C,ROW()))
Believe Ron meant to place the above formula in B1, not in C1 g -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#6
|
|||
|
|||
Fix EXACT function so it always compares in the same row.
Ron's suggestion worked great. Yes, Max is right, the placement is in B1.
Thanks everyone! "Max" wrote: C1: =EXACT(INDEX(A:A,ROW()),INDEX(C:C,ROW())) Believe Ron meant to place the above formula in B1, not in C1 g -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
Thread Tools | |
Display Modes | |
|
|