need help with a formula

[freeman]

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.

 

[Pete_UK]

Try this in B2:

=IF(COUNTIF($A$2:$A$7610,A2)>5,A2&" - "&COUNTIF($A$2:$A
$7610,A2),COUNTIF($A$2:$A$7610,A2))

Then copy it down. I've assumed yo still want the count in column B
for those items which do not appear more than 5 times. If you don't
want this, then simplify the formula to:

=IF(COUNTIF($A$2:$A$7610,A2)>5,A2&" - "&COUNTIF($A$2:$A$7610,A2),"")

Hope this helps.

Pete

 

[Bob Phillips]

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$7610>=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.

 

[freeman]

This array formula, What exactly does it do?

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$7610>=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

 

[Bob Phillips]

Essentially, it looks up the next count that is not blank and is greater
than 5, and puts the corresponding value from column A into the result cell.
Then the net row gets the next and so on, until all exhausted.

 

[freeman]

Bob,

This Array worked great, Thank you. Is is possible to modify this to display
something that repeated twice. As well as something that repeated 2-3 and 2-4
times?

Regards

Michael

 

[Bob Phillips]

Do you mean those items repeated twice as against those repeated 5 times?
And then those repeated 2 or 3 time as against 5 times?

 

[freeman]

That is correct.

Do you mean those items repeated twice as against those repeated 5 times?
And then those repeated 2 or 3 time as against 5 times?

--
__________________________________
HTH

Bob

 

[Bob Phillips]

Which is? There were two options there.