A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

need help with a formula



 
 
Thread Tools Display Modes
  #1  
Old August 26th, 2008, 09:49 PM posted to microsoft.public.excel.worksheet.functions
freeman
external usenet poster
 
Posts: 8
Default need help with a formula

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.
  #2  
Old August 26th, 2008, 10:02 PM posted to microsoft.public.excel.worksheet.functions
Pete_UK
external usenet poster
 
Posts: 8,780
Default need help with a formula

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

On Aug 26, 9:49*pm, freeman wrote:
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.


  #3  
Old August 26th, 2008, 10:13 PM posted to microsoft.public.excel.worksheet.functions
Bob Phillips[_2_]
external usenet poster
 
Posts: 1,562
Default need help with a formula

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.



  #4  
Old August 27th, 2008, 08:58 PM posted to microsoft.public.excel.worksheet.functions
freeman
external usenet poster
 
Posts: 8
Default 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.




  #5  
Old August 27th, 2008, 09:24 PM posted to microsoft.public.excel.worksheet.functions
Bob Phillips[_2_]
external usenet poster
 
Posts: 1,562
Default need help with a formula

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.

--
__________________________________
HTH

Bob

"freeman" wrote in message
...
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.






  #6  
Old August 27th, 2008, 10:08 PM posted to microsoft.public.excel.worksheet.functions
freeman
external usenet poster
 
Posts: 8
Default need help with a formula

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" 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.




  #7  
Old August 27th, 2008, 10:41 PM posted to microsoft.public.excel.worksheet.functions
Bob Phillips[_2_]
external usenet poster
 
Posts: 1,562
Default need help with a formula

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

"freeman" wrote in message
...
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" 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.






  #8  
Old August 28th, 2008, 05:52 PM posted to microsoft.public.excel.worksheet.functions
freeman
external usenet poster
 
Posts: 8
Default need help with a formula

That is correct.

"Bob Phillips" wrote:

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

"freeman" wrote in message
...
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" 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.






  #9  
Old August 28th, 2008, 11:08 PM posted to microsoft.public.excel.worksheet.functions
Bob Phillips[_2_]
external usenet poster
 
Posts: 1,562
Default need help with a formula

Which is? There were two options there.

--
__________________________________
HTH

Bob

"freeman" wrote in message
...
That is correct.

"Bob Phillips" wrote:

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

"freeman" wrote in message
...
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" 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.








 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 03:46 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.