Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formula or code
Try this
Sub Macro1() Dim NameLp As Integer Dim SearchLp As Integer Dim ResltLp As Integer Dim NameCnt As Integer Dim ResltCnt As Integer Dim ResltPntr As Integer Dim LastRow As Integer Dim FirstRow As Integer Dim SearchName As String Dim SearchCnt As Integer 'set range FirstRow = 1 LastRow = ActiveSheet.UsedRange.Rows.Count ResltCnt = 0 If LastRow <= FirstRow Then Exit Sub 'Copy name list to new column 'Columns("A:A").Copy 'Columns("D:D").PasteSpecial 'Application.CutCopyMode = False 'Selection.Sort Key1:=Range("D1"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom 'Assuming names are in column "A", search for matches For NameLp = FirstRow To LastRow NameCnt = 0 ResltPntr = 0 SearchName = Range("A" & NameLp).Value If ResltCnt = 0 Then ResltCnt = ResltCnt + 1 Range("D" & ResltCnt).Value = SearchName ResltPntr = 1 Else For ResltLp = 1 To ResltCnt If Range("D" & ResltLp).Value = SearchName Then ResltPntr = ResltLp 'name in results already Exit For End If Next ResltLp If ResltPntr = 0 Then 'name not in results ResltCnt = ResltCnt + 1 ResltPntr = ResltCnt Range("D" & ResltPntr).Value = SearchName End If End If For SearchLp = FirstRow To LastRow If Range("A" & SearchLp).Value = SearchName Then 'using column "D" + "E" for temp results NameCnt = NameCnt + 1 Range("E" & ResltPntr).Value = NameCnt End If Next SearchLp Next NameLp 'Write back matches For ResltLp = 1 To ResltCnt For NameLp = FirstRow To LastRow If Range("D" & ResltLp).Value = Range("A" & NameLp).Value Then Range("B" & NameLp).Value = Range("E" & ResltLp).Value End If Next NameLp Next ResltLp End Sub Hope it helps "Gordon Cartwright" wrote in message ... Hi... In colum A I have 3000 names. In column B I want to indicate which of these names are repeated in the adjacant B cell. The value in colum B must be the number of times the name has been repeated...eg A B Jones 4 Jones 4 Jones 4 Harris Smith 2 Smith 2 Jackson Jones 4 Vlookup and Hlookup tables just don't seem to do the job. Can anyone help here? Thanks in advance Gordon Cartwright |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to code the formula? | Excel Discussion (Misc queries) | |||
copy formula using VB code | Excel Discussion (Misc queries) | |||
formula or vba code | Excel Worksheet Functions | |||
formula code | Excel Discussion (Misc queries) | |||
formula or code | Excel Worksheet Functions |