Trying to make millions :)

Hi there! I've got a range of number sets that i would like to filter. I want to be able to remove sets based on whether they have sequential numbers in them. i.e remove all sets with 4 consecutive numbers within the set. eg. 1,2,3,4,15,34 or 5,6,7,8,12,17 if you get my drift.
I've attached a sample file.
Would appreciate any advice. Thanks.

This really difficult...would appreciate any advice

Hi VBA Noob.

Not really. I want to filter out any of those cells that contain sets of numbers with x number of consecutive numbers. i.e. a cell has 1,2,3,6,8,9 and the one below it has 1,2,3,4,6,13. I would like to be able to filter out the cell with 4 consecutive numbers - (1,2,3,4,6,13) (or with 3,4,5 or 6 consecutive numbers as the user wishes).

Thanks for looking at it for me. I hit a wall with it

If you use Data > Text to columns to split the numbers into 6 columns, i.e. A to F then you could use this formula in G1 copied down to give the number of consecutive numbers in each row (this assumes numbers would always be listed in ascending order)

=SUMPRODUCT(--(B1:F1-A1:E1=1))+1

You could then filter by this column, if you wish, to see sets with 4 consecutive numbers, or use a COUNTIF formula to count them

Hello

Thank you for all your advce to date. The problem with seperating the values is that i'm actually dealing with a huge amount of data and im not sure it would be practical to do that. Ideally, i would like a user to be able to enter a number of consecutive numbers i.e 3 or 4 or 5, and upon executing, any of the cells that don't have that number of consecutive numbers are moved perhaps to another sheet.

Thank you all again.

Hi VBA Noob

I really hope I'm not frustrating you with this, I really appreciate your help and I will be able to use what you've done I'm sure.
Where you have put the combo box, the user would select 3 or 4 or 5 or 10 or whatever (ie. this is the number of consecutive numbers in each cell. The cell might have the numbers 28,29,30,35,38,40 in it. Selecting 3 from the list would filter this cell out. i.e. 28,29,30 = 3 consecutive numbers).

Sorry if I haven't been very clear...bit of a novice.

Hi

See if this is getting closer. There is a UDF in the file so you will have to allow the macros when opening.

You will have to put the number of sequential items into C1. If you have an existing filter, then you will have to turn it off, then refilter. Your data will also need to have the current separator of ", " (ie a comma followed by a space).

rylo

thanks Rylo

Thats great...I think I can take it from there. Thanks alot for your help!!