How to remove cells if less than four in a row occur? (please help!)

**rinonishi** · 03-05-2018, 12:46 PM

Hiya

I'm really new to all this but basically have 20,000 observations sorted by company name (lots of companies haha) but i'm only allowed to use the companies who have more than 4 observations, e.g:

company 1 - 2 cars
company 1 - 4 cars
company 1 - 1 car
company 1 - 3 cars
company 1 - 2 cars
company 2 - 1 car
company 2 - 3 cars
company 3 -
etc....

so in the above case company 1 I could use as there are 5 observations, but company 2 couldn't be as there are only 2... so instead of trawling through 20000 observations i was hoping maybe there was a script i could use to count the number of times the company name appears and remove all the listings (cell rows) if it's less than 5... is this possible?

thanks!

**YasserKhalil** · 03-05-2018, 12:59 PM

This code may be a good start for you. It will list unique values and count them for you

Please Login or Register  to view this content.

**Mumps1** · 03-05-2018, 01:03 PM

This macro assumes that the company names are in column A, that there is a header in cell A1 and your data starts in row 2.

Please Login or Register  to view this content.

**YasserKhalil** · 03-05-2018, 02:15 PM

Try this code

Please Login or Register  to view this content.

**rinonishi** · 03-05-2018, 07:01 PM

Hiya Mumps1, I can't get it to work

so of the 20,000 cells when I ran the code it went down to 17,000 however there are lots of companies left with multiple entries? For example, a company called "Offspring" only appears 3 times in the company name column (3 cells one above the other) e.g.:

Offspring "data"
Offspring "data"
Offspring "data"

so as there's only 3 bit of data for them, they aren't reliable enough so i need to remove them all from the spreadsheet, but i keep running the macro and they wont go... any idea? The company names are in column A yes and the data in B, although all that matters is the company name only appearing 4 times or less... im so sorry i've never done any sort of programming so sorry if i sound really stupid!!

thanks x

And YasserKhalil hello too, I'm probably being really stupid but when i try to run yours it doesn't seem to do anything? just runs for a slight second but nothing changes in the spreadsheet? Thanks for all the help by the way im very grateful!

**Marc L** · 03-05-2018, 09:16 PM

Hi !

An EZ demonstration upon the active worksheet (if not, paste this code in the worksheet module) :

Please Login or Register  to view this content.

Do you like it ? So thanks to click on bottom left star icon « ★ Add Reputation » !

**Marc L** · 03-06-2018, 05:17 AM

Thanks for the rep' Yasser !

**rinonishi** · 03-06-2018, 06:42 AM

Marc L it works! thanks so much for your help

**Marc L** · 03-06-2018, 08:32 AM

Thanks for the rep' !

The code works just with Excel basics inner features like any beginner can do manually …

How to remove cells if less than four in a row occur? (please help!)

LinkBack

Thread Tools

Rate This Thread

Display

How to remove cells if less than four in a row occur? (please help!)

Re: How to remove cells if less than four in a row occur? (please help!)

Re: How to remove cells if less than four in a row occur? (please help!)

Re: How to remove cells if less than four in a row occur? (please help!)

Re: How to remove cells if less than four in a row occur? (please help!)

Another way …

Re: How to remove cells if less than four in a row occur? (please help!)

Re: How to remove cells if less than four in a row occur? (please help!)

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Help with Formula: Remove trailing commas or using IF to remove results from blank cells?

[SOLVED] Need the Count of cells only if they occur consecutively

[SOLVED] vba in commandbutton_click event to only allow to occur in certain cells question

[SOLVED] Count number of occurences of values over range of cells that occur at least Three times.

[SOLVED] Highlight 18 Digit cells in Column A that occur in "triplicate" (not using Cond/Form)

Find the earliest date in a column where blank cells and N/A can occur

Where and when does recalculation occur?

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions