Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Lookup second duplicate value location.
How to write VBA code to find the row location of a second duplicate
value in a list( ie a second "apple" in a list of fruits name) ? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Lookup second duplicate value location.
I like using a worksheet function to do this A1 A 1 =countif(A$1:A1,A1) A2 B 1 =countif(A$1:A2,A2) A3 C 1 =countif(A$1:A3,A3) A4 D 1 =countif(A$1:A4,A4) A5 E 1 =countif(A$1:A5,A5) A6 C 2 =countif(A$1:A6,A6) A7 B 2 =countif(A$1:A7,A7) A8 F 1 =countif(A$1:A8,A8) LastRow = Range("A" & rows.count).end(xlup).Row Range("B1").formual = _ "=countif(A$1:A1,A1)" Range("B1").copy _ Destination:=Range("B1:B" & LastRow) the results in column B greater then 1 is the duplicate results. Yo can then use an autofilter to select values 1 to get the results. -- joel ------------------------------------------------------------------------ joel's Profile: http://www.thecodecage.com/forumz/member.php?userid=229 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=147822 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Lookup second duplicate value location.
Marina,
You don't need VB for this it can be done with a formula but if you want code Sub sonic() Dim Found As Long, x As Long, LastRow As Long Dim ResPonse As String ResPonse = Trim(InputBox("Enter value to search for")) LastRow = Cells(Cells.Rows.Count, "A").End(xlUp).Row For x = LastRow To 1 Step -1 If UCase(Trim(Cells(x, 1).Value)) = UCase(ResPonse) Then Found = Found + 1 If Found = 2 Then MsgBox "second occurrence of " & ResPonse & " in row " & x Exit Sub End If End If Next MsgBox "Search string not found" End Sub Mike "Marina" wrote: How to write VBA code to find the row location of a second duplicate value in a list( ie a second "apple" in a list of fruits name) ? . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
lookup duplicate value | Excel Discussion (Misc queries) | |||
Lookup a location # with multiple dates | Excel Worksheet Functions | |||
Fixed file location for lookup formula | Excel Worksheet Functions | |||
Duplicate Lookup | Excel Programming | |||
lookup cell location | Excel Programming |