Review many postings on conditional row delete but still need help. I
want to delete all rows where there are fewer than 5 companies within
industry. The median within that industry is not very useful if there
are less than 5, or some other number, of companies.

Company SIC Code Sales

If frequency of SIC code is less than 5, then delete the row.

Please help.

I think more info is needed such as a layout. Do NOT try to attach the
workbook.

--
Don Guillett
SalesAid Software

wrote in message
ups.com...
Review many postings on conditional row delete but still need help. I
want to delete all rows where there are fewer than 5 companies within
industry. The median within that industry is not very useful if there
are less than 5, or some other number, of companies.

Company SIC Code Sales

If frequency of SIC code is less than 5, then delete the row.

Please help.

One thing you can try:

First, back up your work.

Then turn on Autofilter (Data/Filter/Autofilter). Click the drop arrow on
SIC Code column. Select custom from the list and "is less than" 5. Select
the results of the filter and Edit/Delete. Then turn off the autofilter.


" wrote:

Review many postings on conditional row delete but still need help. I
want to delete all rows where there are fewer than 5 companies within
industry. The median within that industry is not very useful if there
are less than 5, or some other number, of companies.

Company SIC Code Sales

If frequency of SIC code is less than 5, then delete the row.

Please help.

JMB, this is a PERFECT example of how I always go about things the wrong
way. I was getting ready to reply to this (every decade or so I try to give
something back) with all kinds of ways to check for values less than 5
(assuming that's the question, it might be checking for less than 5
instances of the SIC number in a column) and delete using VBA, and then I
read your post. Autofilter didn't even OCCUR to me.

I have to laugh at myself sometimes.

Good answer, JMB.


"JMB" wrote in message
...
One thing you can try:

First, back up your work.

Then turn on Autofilter (Data/Filter/Autofilter). Click the drop arrow on
SIC Code column. Select custom from the list and "is less than" 5.
Select
the results of the filter and Edit/Delete. Then turn off the autofilter.


" wrote:

Review many postings on conditional row delete but still need help. I
want to delete all rows where there are fewer than 5 companies within
industry. The median within that industry is not very useful if there
are less than 5, or some other number, of companies.

Company SIC Code Sales

If frequency of SIC code is less than 5, then delete the row.

Please help.

On Feb 3, 4:47 pm, wrote:
Review many postings on conditional row delete but still need help. I
want to delete all rows where there are fewer than 5 companies within
industry. The median within that industry is not very useful if there
are less than 5, or some other number, of companies.

Company SIC Code Sales

If frequency of SIC code is less than 5, then delete the row.

Please help.

It's the "freqency" part that's important. I don't want an industry
which is only represented by five or fewer companies. The SIC code
will change. For example if there are five companies within the SIC
Code 1, then I want to keep them and calculate medians, otherwise
delete the company.

assume SIC code is in column B

Assuming one company per row:

Sub ABC()
Dim lastrow as long
lastrow = Cells(rows.count,2).End(xlup).row
for i = lastrow to 2 step -1
if application.countif(B:B,cells(i,2) < 5 then
rows(i).Delete
end if
Next i
End Sub



--
Regards,
Tom Ogilvy


wrote in message
oups.com...
On Feb 3, 4:47 pm, wrote:
Review many postings on conditional row delete but still need help. I
want to delete all rows where there are fewer than 5 companies within
industry. The median within that industry is not very useful if there
are less than 5, or some other number, of companies.

Company SIC Code Sales

If frequency of SIC code is less than 5, then delete the row.

Please help.

It's the "freqency" part that's important. I don't want an industry
which is only represented by five or fewer companies. The SIC code
will change. For example if there are five companies within the SIC
Code 1, then I want to keep them and calculate medians, otherwise
delete the company.

Had typo(s) in the countif line:

Sub ABC()
Dim lastrow as long
Dim i as Long
lastrow = Cells(rows.count,2).End(xlup).row
for i = lastrow to 2 step -1
if application.countif(Range("B:B"),cells(i,2)) < 5 then
rows(i).Delete
end if
Next i
End Sub

--
Regards,
Tom Ogilvy



"Tom Ogilvy" wrote in message
...
assume SIC code is in column B

Assuming one company per row:

Sub ABC()
Dim lastrow as long
lastrow = Cells(rows.count,2).End(xlup).row
for i = lastrow to 2 step -1
if application.countif(B:B,cells(i,2) < 5 then
rows(i).Delete
end if
Next i
End Sub



--
Regards,
Tom Ogilvy


wrote in message
oups.com...
On Feb 3, 4:47 pm, wrote:
Review many postings on conditional row delete but still need help. I
want to delete all rows where there are fewer than 5 companies within
industry. The median within that industry is not very useful if there
are less than 5, or some other number, of companies.

Company SIC Code Sales

If frequency of SIC code is less than 5, then delete the row.

Please help.

It's the "freqency" part that's important. I don't want an industry
which is only represented by five or fewer companies. The SIC code
will change. For example if there are five companies within the SIC
Code 1, then I want to keep them and calculate medians, otherwise
delete the company.

Correction - almost a good answer. Looks like Don had it right the first
time - more details were needed. But the OP could have set up a helper
column using Countif and filter on the helper column.


"CompleteNewb" wrote:

JMB, this is a PERFECT example of how I always go about things the wrong
way. I was getting ready to reply to this (every decade or so I try to give
something back) with all kinds of ways to check for values less than 5
(assuming that's the question, it might be checking for less than 5
instances of the SIC number in a column) and delete using VBA, and then I
read your post. Autofilter didn't even OCCUR to me.

I have to laugh at myself sometimes.

Good answer, JMB.


"JMB" wrote in message
...
One thing you can try:

First, back up your work.

Then turn on Autofilter (Data/Filter/Autofilter). Click the drop arrow on
SIC Code column. Select custom from the list and "is less than" 5.
Select
the results of the filter and Edit/Delete. Then turn off the autofilter.


" wrote:

Review many postings on conditional row delete but still need help. I
want to delete all rows where there are fewer than 5 companies within
industry. The median within that industry is not very useful if there
are less than 5, or some other number, of companies.

Company SIC Code Sales

If frequency of SIC code is less than 5, then delete the row.

Please help.