Search cell value in same column

[mdIsmailkm]

I have a huge excel sheet where i need to find the address of duplicat
cell value in a column.
I have given an example below, where Column B returns the address o
duplicate value found in Column A. Could you please help by providin
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 formul
"=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 abov
formula, I just need to ignore searching for the cell being searched
Please help

 

[Claus Busch]

Hi,

Am Sat, 2 Nov 2013 07:24:34 +0000 schrieb mdIsmailkm:

1 | Apple | A4
2 | Orange | A6
3 | Mango | no-match
4 | Apple | A1
5 | Grapes | no-match
6 | Orange | A2
7 | Pineapple | no-match

insert a header in column A and then try:

Sub Test()
Dim LRow As Long
Dim LRow2 As Long
Dim c As Range
Dim i As Long
Dim firstaddress As String

Sheets.Add After:=Sheets(Sheets.Count)
ActiveSheet.Name = "Temp"

With Sheets("Sheet1")
.Columns("A:A").AdvancedFilter Action:=xlFilterCopy, _
CopyToRange:=Range("A1"), Unique:=True
LRow = .Cells(.Rows.Count, 1).End(xlUp).Row
LRow2 = Sheets("Temp").Cells(.Rows.Count, 1).End(xlUp).Row
For i = 2 To LRow2
If WorksheetFunction.CountIf(.Range("A1:A" & LRow), _
Sheets("Temp").Cells(i, 1)) = 1 Then
Sheets("Temp").Cells(i, 2) = "no match"
Else
Set c = .Range("A1:A" & LRow).Find(Sheets("Temp") _
.Cells(i, 1), LookIn:=xlValues, lookat:=xlWhole)
If Not c Is Nothing Then
firstaddress = c.Address
Do
Sheets("Temp").Cells(i, 2) = Sheets("Temp") _
.Cells(i, 2) & c.Address(0, 0) & ", "
Set c = .Range("A1:A" & LRow).FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstaddress
End If
End If
Next
With .Range("B2:B" & LRow)
.Formula = "=Substitute(Vlookup(A2,Temp!" & Range("A2:B" & LRow2)
_
.Address & ",2,0),Address(Row(),1,4) & "", "",)"
.Value = .Value
End With
End With
Application.DisplayAlerts = False
Sheets("Temp").Delete
Application.DisplayAlerts = True
End Sub


Regards
Claus B.

 

[mdIsmailkm]

Hi Ron, Thanks for your response. I tried the Array-entered formula, i
worked perfectly. I selected the entire column A in place of 'Fruit' i
your formula.

Hi Claus B, Thanks for the response. I am yet to try your code, I wil
use it in a macro excel later.

Thanks for helping me out.
Regards,
Ismail

I have a huge excel sheet where i need to find the address of duplicat
cell value in a column.
I have given an example below, where Column B returns the address o
duplicate value found in Column A. Could you please help by providin
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 formul
"=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 abov
formula, I just need to ignore searching for the cell being searched
Please help.

;1614771']On Sat, 2 Nov 2013 07:24:34 +0000, mdIsmailk

I have a huge excel sheet where i need to find the address o 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)<>ROW()),0),1),"N
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

 

[Ron Rosenfeld]

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.

Glad to help. Thanks for the feedback.

BTW, if you use a smaller range than the entire column, the formula should execute quicker, if that is an issue on your machine. Perhaps $A$1:$A$10000 might be large enough.