August 27th, 2008, 08:58 PM
posted to microsoft.public.excel.worksheet.functions
|
|
|
need help with a formula
This array formula, What exactly does it do?
"Bob Phillips" wrote:
Change B2 to
=IF(COUNTIF($A$2:$A2,A2)1,"",COUNTIF($A$2:$A$7610 ,A2))
and copy down.
Then in some spare column, row 1, add
=IF(ISERROR(SMALL(IF(($B$2:$B$7610"")*($B$2:$B$7 610=5),ROW($A$2:$A$7610)-MIN(ROW($A$2:$A$7610))+1,""),ROW($A1))),"",
INDEX(A$2:A$7610,SMALL(IF(($B$2:$B$7610"")*($B$2 :$B$7610=5),ROW($A$2:$A$7610)-MIN(ROW($A$2:$A$7610))+1,""),ROW($A1))))
which is an array formula, so commit wit Ctrl-Shift-Enter, and copy down as
far as you might need, and acroos one column.
--
__________________________________
HTH
Bob
"freeman" wrote in message
news
I have an excel file that looks like this.
Column A has a long list of names, many that repeat
Column B has a the following formula =COUNTIF($A$2:$A$7610,A2) that will
look at column A and tell me how many times an item is repeating.
What I am looking for is the ability to take both column A and B and
display
the following information.
Items that repeated more then five times will show the relevant data from
column A and B in this column.
Any Ideas?
BTW I am not very good at VB.
|