Excel question

[ckcollector]

How can I make a list of objects that appear in a different list a specific number of times. I have a long list of names, and I would like a new list of names that appear in this list three or more times.

Thanks

 

[Asala42]

How about a formula copied down the next column:

(Assuming your data is in Column A starting in A2)

=IF(COUNTIF($A$2:A2,A2)=3,A2,"")

HTH

 

[Jay Petrulis]

Hi,

You can do this in Excel by using COUNTIF and some advanced filtering.

You can do this quite easily in VBA with something like the following routine.<pre>Sub test()
Dim cell As Range, Rng As Range, MyArr
Dim Counter As Long
Dim fn As WorksheetFunction

Set fn = Application.WorksheetFunction
Set Rng = ThisWorkbook.ActiveSheet.Range("A1:A26")

ReDim MyArr(1 To Rng.Cells.Count)

For Each cell In Rng
If fn.CountIf(Rng, cell) >= 3 Then
If IsError(Application.Match(cell, MyArr, 0)) Then
Counter = Counter + 1
MyArr(Counter) = cell
End If
End If
Next cell

If Counter Then
ThisWorkbook.ActiveSheet.Range("B1").Resize(Counter, 1) _
= fn.Transpose(MyArr)
End If

End Sub</pre>

Change the data range and the output ranges to match you data.

_________________
Bye,
Jay

EDIT: Added a bit or error handling at the end to run to completion even if no entries are listed 3 or more times.
This message was edited by Jay Petrulis on 2002-09-13 11:48