Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Search cell value in same column
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. Last edited by mdIsmailkm : November 2nd 13 at 07:29 AM Reason: Format of the table provided is not appropriate. it removed more spaces with one space |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Search cell value in same column
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. -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Search cell value in same column
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. |
#4
|
|||
|
|||
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:
Quote:
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Search cell value in same column
On Sat, 2 Nov 2013 12:19:14 +0000, mdIsmailkm wrote:
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Search the column and return a cell value | Excel Programming | |||
Search for first empty cell in column | Excel Programming | |||
SEARCH COLUMN BY NAME THEN DISPLAY CELL ON SAME ROW | Excel Worksheet Functions | |||
Loop through column headers to search from column name and get cell range | Excel Programming | |||
search column for specific cell using vba | Excel Programming |