| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 ... 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
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 ... 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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 ... 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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 ... 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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 ... 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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 ... 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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 ... 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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Linear Mode
