Deleting Rows with Too Many of the Same Number
 

I have seven columns, A-G. All of these columns have different
quantities of whole numbers 1-7. I'd like to make a macro to go through
row by row and delete any rows that have more than 4 of the same numbers
in the same row. How would I go about doing this?


--
Losse
------------------------------------------------------------------------
Losse's Profile: http://www.excelforum.com/member.php...o&userid=24813
View this thread: http://www.excelforum.com/showthread...hreadid=559436

Deleting Rows with Too Many of the Same Number
 

There's probably an easier way but this should get the job done:

Consider haivng an integer variable, one for each of the values fro
1-7 to store the occurance of the particular value in a row.
At the end of the row, if any of the variables have a value of 4 o
greater then delete the row.

of course this should be looped for each row and the variables ar
reset at the beginning of a new iteration.

Thats how I would do it. hope it helps

--
bail
-----------------------------------------------------------------------
bails's Profile: http://www.excelforum.com/member.php...fo&userid=3615
View this thread: http://www.excelforum.com/showthread.php?threadid=55943

Deleting Rows with Too Many of the Same Number
 

Thanks, I had thought about that, except I need to conserve system
resources. This program will be run on a LOT of rows, and I suspect it
will crash the computer or at least run extremely slowly.


--
Losse
------------------------------------------------------------------------
Losse's Profile: http://www.excelforum.com/member.php...o&userid=24813
View this thread: http://www.excelforum.com/showthread...hreadid=559436

Deleting Rows with Too Many of the Same Number
 

How about putting this in H1
=MAX(COUNTIF(A1:g1,A1:g1))
This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

And drag down.

Now you can just filter by that column to show 4 and delete the visible rows.

If you need a macro, you could record one when you did it manually.

Losse wrote:

I have seven columns, A-G. All of these columns have different
quantities of whole numbers 1-7. I'd like to make a macro to go through
row by row and delete any rows that have more than 4 of the same numbers
in the same row. How would I go about doing this?

--
Losse
------------------------------------------------------------------------
Losse's Profile: http://www.excelforum.com/member.php...o&userid=24813
View this thread: http://www.excelforum.com/showthread...hreadid=559436

--

Dave Peterson

Deleting Rows with Too Many of the Same Number
 

Thank you, that should solve my problem

--
Loss
-----------------------------------------------------------------------
Losse's Profile: http://www.excelforum.com/member.php...fo&userid=2481
View this thread: http://www.excelforum.com/showthread.php?threadid=55943