Name finder Macro?

[Gorky]

Hi,

Thanks to all who have helped me on my last question.

I need to know if there is a way of creating a macro to do the following:

Find the number of times a name ocurrs in a column and then on a different sheet in the same workbook, place the number of times into a different column. Preferably without making the first column the same one each time, although this is not a problem.

Therefore if the name Smith appears 5 times in sheet 2 column c then it will place the number 5 in column H cell 2 in sheet 1, and so on down the list of names. Obviously the cells in sheet 1 will change for each person's name.

I suspect that it will involve VLOOKUP, but I'm not well versed enough in Excel to get it right.

I need to do this for multiple names.

Thanks

Gorky

 

[Gunswick]

this macro may help.

click in the column you want to copy, in row 4 i think.

then run it, and it will copy all unique names to column H, you can combine it with an IF statement to select it to show you the name based on how many times it appears.

e.g. john = 3
bob = 3
product1 = 4

you could set the IF >=0 and it would give you all of the names, and how many times they appear

Sub LC2()
'
' LC2 Macro that makes a list show only objects that appear a certain number or times
' based on a condition.
' Chris Scott:Select cell D2 First! to copy vendors >1 invoices to column R
' Keyboard Shortcut: Ctrl+Shift+V
'
    ActiveCell.Offset(0, -1).Columns("A:A").EntireColumn.Select
    ActiveCell.Range("A1:A349").AdvancedFilter Action:=xlFilterCopy, CopyToRange _
        :=ActiveCell.Offset(0, 15).Columns("A:A").EntireColumn, Unique:=True
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 4
    ActiveWindow.ScrollColumn = 5
    ActiveWindow.SmallScroll ToRight:=6
    ActiveCell.Offset(0, 15).Columns("A:A").EntireColumn.EntireColumn.AutoFit
    ActiveCell.Offset(1, 16).Range("A1").Select
    ActiveCell.FormulaR1C1 = "=COUNTIF(R2C3:R299C3,RC[-1])"
    ActiveCell.Select
    Selection.AutoFill Destination:=ActiveCell.Range("A1:A195"), Type:= _
        xlFillDefault
    ActiveCell.Range("A1:A195").Select
    ActiveWindow.ScrollRow = 56
    ActiveWindow.ScrollRow = 55
    ActiveWindow.ScrollRow = 54
    ActiveWindow.ScrollRow = 53
    ActiveWindow.ScrollRow = 51
    ActiveWindow.ScrollRow = 50
    ActiveWindow.ScrollRow = 48
    ActiveWindow.ScrollRow = 46
    ActiveWindow.ScrollRow = 43
    ActiveWindow.ScrollRow = 40
    ActiveWindow.ScrollRow = 37
    ActiveWindow.ScrollRow = 34
    ActiveWindow.ScrollRow = 31
    ActiveWindow.ScrollRow = 27
    ActiveWindow.ScrollRow = 24
    ActiveWindow.ScrollRow = 21
    ActiveWindow.ScrollRow = 17
    ActiveWindow.ScrollRow = 13
    ActiveWindow.ScrollRow = 10
    ActiveWindow.ScrollRow = 7
    ActiveWindow.ScrollRow = 4
    ActiveWindow.ScrollRow = 1
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = "=IF(RC[-1]>1,RC[-2],"""")"
    ActiveCell.Select
    Selection.AutoFill Destination:=ActiveCell.Range("A1:A195"), Type:= _
        xlFillDefault
    ActiveCell.Range("A1:A195").Select
    ActiveWindow.ScrollRow = 74
    ActiveWindow.ScrollRow = 73
    ActiveWindow.ScrollRow = 72
    ActiveWindow.ScrollRow = 71
    ActiveWindow.ScrollRow = 70
    ActiveWindow.ScrollRow = 68
    ActiveWindow.ScrollRow = 66
    ActiveWindow.ScrollRow = 64
    ActiveWindow.ScrollRow = 63
    ActiveWindow.ScrollRow = 61
    ActiveWindow.ScrollRow = 59
    ActiveWindow.ScrollRow = 57
    ActiveWindow.ScrollRow = 55
    ActiveWindow.ScrollRow = 53
    ActiveWindow.ScrollRow = 51
    ActiveWindow.ScrollRow = 50
    ActiveWindow.ScrollRow = 48
    ActiveWindow.ScrollRow = 47
    ActiveWindow.ScrollRow = 44
    ActiveWindow.ScrollRow = 42
    ActiveWindow.ScrollRow = 40
    ActiveWindow.ScrollRow = 38
    ActiveWindow.ScrollRow = 36
    ActiveWindow.ScrollRow = 33
    ActiveWindow.ScrollRow = 30
    ActiveWindow.ScrollRow = 28
    ActiveWindow.ScrollRow = 25
    ActiveWindow.ScrollRow = 23
    ActiveWindow.ScrollRow = 20
    ActiveWindow.ScrollRow = 19
    ActiveWindow.ScrollRow = 16
    ActiveWindow.ScrollRow = 14
    ActiveWindow.ScrollRow = 12
    ActiveWindow.ScrollRow = 10
    ActiveWindow.ScrollRow = 8
    ActiveWindow.ScrollRow = 6
    ActiveWindow.ScrollRow = 4
    ActiveWindow.ScrollRow = 3
    ActiveWindow.ScrollRow = 1
    ActiveCell.Columns("A:A").EntireColumn.Select
    Selection.AutoFilter
    Selection.AutoFilter Field:=1, Criteria1:="<>"
    ActiveCell.Offset(149, 1).Range("A1").Select
End Sub

 

[Gorky]

Thanks!