Hi Ron, Thanks for your response. I tried the Array-entered formula, it worked perfectly. I selected the entire column A in place of 'Fruit' in your formula.
Hi Claus B, Thanks for the response. I am yet to try your code, I will use it in a macro excel later.
Thanks for helping me out.
Regards,
Ismail
Quote:
Originally Posted by mdIsmailkm
I have a huge excel sheet where i need to find the address of duplicate cell value in a column.
I have given an example below, where Column B returns the address of duplicate value found in Column A. Could you please help by providing the formula for Column B result? VBA is also fine.
| A | B
1 | Apple | A4
2 | Orange | A6
3 | Mango | no-match
4 | Apple | A1
5 | Grapes | no-match
6 | Orange | A2
7 | Pineapple | no-match
I used the formula "=ADDRESS(MATCH(A1,(($A$1:$A$7)),0)+ROW($A$1)-1,COLUMN($A$1:$A$7),4)", which returned the cell address of same cell that is being searched.
Apple | A1
Orange | A2
Mango | A3
Apple | A1
Grapes | A5
Orange | A2
Pineapple | A7
I did enough search over internet, but could not find. In the above formula, I just need to ignore searching for the cell being searched. Please help.
|
Quote:
Originally Posted by Ron Rosenfeld[_2_]
On Sat, 2 Nov 2013 07:24:34 +0000, mdIsmailkm wrote:
I have a huge excel sheet where i need to find the address of duplicate
cell value in a column.
I have given an example below, where Column B returns the address of
duplicate value found in Column A. Could you please help by providing
the formula for Column B result? VBA is also fine.
| A | B
1 | Apple | A4
2 | Orange | A6
3 | Mango | no-match
4 | Apple | A1
5 | Grapes | no-match
6 | Orange | A2
7 | Pineapple | no-match
I used the formula
"=ADDRESS(MATCH(A1,(($A$1:$A$7)),0)+ROW($A$1)-1,COLUMN($A$1:$A$7),4)",
which returned the cell address of same cell that is being searched.
Apple | A1
Orange | A2
Mango | A3
Apple | A1
Grapes | A5
Orange | A2
Pineapple | A7
I did enough search over internet, but could not find. In the above
formula, I just need to ignore searching for the cell being searched.
Please help.
This formula must be **array-entered**:
B1: =IFERROR(ADDRESS(MATCH(1,(Fruit=A1)*(ROW(Fruit)<R OW()),0),1),"No Match")
(and fill down as far as needed
----------------------------------------
To **array-enter** a formula, after entering
the formula into the cell or formula bar, hold down
<ctrl<shift while hitting <enter. If you did this
correctly, Excel will place braces {...} around the formula.
|