Extract Sequential Numbers of 5 or More Into Separate Columns

**etm6477** · 04-10-2018, 09:35 PM

Hello Everyone. I've attached a file where I place 20 #'s into columns CG-CZ, and the formula places the numbers in their corresponding column to the left. It also highlights the sequential numbers of 3 or more like in the 1st 20 examples. What I would like for it to also do is place any runs of 5 or more numbers in column DC-DV like the examples in the next 20 rows.

I appreciate your help with this issue. Thank you.

P.S. What are running sequential numbers considered in excel? I tried researching this on my own, but didn't know how to word it in Google.

**FlameRetired** · 04-11-2018, 12:57 AM

etm6477 in the output example (row 24) there is an empty cell between the two "runs" of 5 numbers.

Since there is only one occurrence of multiple runs it is difficult to tell if that is intentional.

Is it?

**etm6477** · 04-11-2018, 07:55 AM

It was on purpose, if its easier just to just have them next to each other then thats fine because its very rare for there to be 2 or more in the same row. I just wanted 1 example because it will happen, and i didn't want it to screw up the function, or conditional formatting, if that's what you'll use.

**FlameRetired** · 04-11-2018, 01:32 PM

OK next question.

If we were to put space(s) between the outputs:
In the example there is only one non-qualifying number between the runs of 5 in CG2:CZ2 and one space between the outputs in row 24.
If there were more than one non-qualifying number between those runs how many spaces do you want in the output?

**etm6477** · 04-11-2018, 01:40 PM

No need for the spaces between them then. It will be fine without them. As I said, it rarely occurs, so if they could just go next to the other run of five, or more then that's fine. Thank you.

**FlameRetired** · 04-11-2018, 03:56 PM

This is going to take me a while.

Using the conditional formatting formula as a guide returns the numbers. So far I have not been able to eliminate sequential runs less than 5.

**etm6477** · 04-15-2018, 10:25 AM

Still looking for help on this. Just wanna move it to the top.

**FlameRetired** · 04-15-2018, 11:57 AM

Yeah.

Still working on it. Getting the first run of numbers is not a problem. It's where there are multiple runs that I hit snags.

**FlameRetired** · 04-16-2018, 01:33 AM

I've exhausted all my ideas and called for community help.

**AliGW** · 04-16-2018, 01:35 AM

Can you share what you did to generate the first run of numbers?

**FlameRetired** · 04-16-2018, 01:49 AM

Sure. With two helper columns in DB:DC.

In DB2

Formula:

Please Login or Register  to view this content.

In DC2

Formula:

Please Login or Register  to view this content.

Then in DD2 fill the table with this

Formula:

Please Login or Register  to view this content.

In column DY are some left over formula parts I thought might take me somewhere. I left them just in case it helps someone.

**JohnTopley** · 04-16-2018, 05:21 AM

VBA offering

Please Login or Register  to view this content.

**CAntosh** · 04-16-2018, 11:07 AM

EDIT: This solution misses sequences that start with numbers lower than 5. See below for a better version.

I think I have a formula solution that works? To avoid circular references, it should start in column DE. Try the following formula in DE2, it should be array-entered (confirmed with ctrl+shift+enter instead of enter):

=IFERROR(SMALL(IF(MATCH($CG2:$CZ2,SMALL(IFERROR((($CK2:$DD2-4)=$CG2:$CZ2)*$CG2:$CZ2,0),COLUMN($CG2:$CZ2)-COLUMN($CG2)+1),1)>IFERROR(MATCH($CG2:$CZ2-5,SMALL(IFERROR((($CK2:$DD2-4)=$CG2:$CZ2)*$CG2:$CZ2,0),COLUMN($CG2:$CZ2)-COLUMN($CG2)+1),1),COLUMN($CZ2)),$CG2:$CZ2),COLUMN(A:A)),"")

Fill right as far as you need to and down. Take a look at the attachment to see if it's working as desired:

**FlameRetired** · 04-16-2018, 01:02 PM

@CAntosh,

Bravo! Good work. I really like the approximate match approach and without helper column(s). I had about given up hope for a formula solution.

If you are interested the nested IFERRORS can be replaced.

Formula:

Please Login or Register  to view this content.

Good work.

**CAntosh** · 04-16-2018, 02:00 PM

Thanks! Nifty trick to eliminate the IFERRORS! I noticed when checking it, though, that both solutions struggle with sequences starting below 5. Here's an ugly tweak that I think solves the problem?

With IFERROR (array-entered):
=IFERROR(SMALL(IF(MATCH($CG2:$CZ2,SMALL(IFERROR((($CK2:$DD2-4)=$CG2:$CZ2)*$CG2:$CZ2,0),COLUMN($CG2:$CZ2)-COLUMN($CG2)+1),1)>IFERROR(MATCH($CG2:$CZ2-5,IF(SMALL(IFERROR((($CK2:$DD2-4)=$CG2:$CZ2)*$CG2:$CZ2,0),COLUMN($CG2:$CZ2)-COLUMN($CG2)+1)=0,-5,SMALL(IFERROR((($CK2:$DD2-4)=$CG2:$CZ2)*$CG2:$CZ2,0),COLUMN($CG2:$CZ2)-COLUMN($CG2)+1)),1),COLUMN($CZ2)),$CG2:$CZ2),COLUMN(A:A)),"")

With only the outer IFERROR (array-entered):
=IFERROR(SMALL(IF(MATCH($CG2:$CZ2,SMALL(((N(+$CK2:$DD2)-4)=$CG2:$CZ2)*$CG2:$CZ2,COLUMN($CG2:$CZ2)-COLUMN($CG2)+1),1)>MATCH(($CG2:$CZ2-5),IF(SMALL(((N(+$CK2:$DD2)-4)=$CG2:$CZ2)*$CG2:$CZ2,COLUMN($CG2:$CZ2)-COLUMN($CG2)+1)=0,-5,SMALL(((N(+$CK2:$DD2)-4)=$CG2:$CZ2)*$CG2:$CZ2,COLUMN($CG2:$CZ2)-COLUMN($CG2)+1)),1),$CG2:$CZ2),COLUMN(A:A)),"")

**FlameRetired** · 04-16-2018, 02:43 PM

Edit Disregard the comments about offset references. I'll blame it in my bifocals!

LOL

I noticed when checking it, though, that both solutions struggle with sequences starting below 5.

Initially I had suspected that might be a drawback of approximated matches. I tested it on a few rows and didn't see any problems with your solution. I'll check again.

Is there any particular row/sequence combination that you can tell me about?

In your last attached I did notice the formula rows are offset from the source references and appear to start referencing the second row instead of the first one.

I haven't tested it, but do you suppose that is the source of apparent error?

**CAntosh** · 04-16-2018, 03:42 PM

Originally Posted by FlameRetired

Is there any particular row/sequence combination that you can tell me about?

On my initial effort, you can see the errors if you change any row to start with 1,2,3,4,5. The solution recognizes the streak, but only returns a 5. It happened because the "-5" in the second half of the formula creates negative numbers in the array when the values in the row are less than 5, and the ascending MATCH struggles with lookup values that are lower than anything in the MATCH array. The workaround isn't particularly elegant, but it should replace the zeroes in the lookup array with "-5"s so that the Match can operate properly.

**FlameRetired** · 04-16-2018, 04:18 PM

Yeah. I just finished tracing all that. Excellent sleuthing.

Thanks for answering the Cavalry call. I burned neurons on this one. Consider yourself double repped.

**CAntosh** · 04-16-2018, 04:27 PM

Haha, I appreciate it! This was a fun one to play with.

**FlameRetired** · 04-16-2018, 05:26 PM

Originally Posted by CAntosh

Haha, I appreciate it! This was a fun one to play with.

I'm too exhausted to see the fun ... yet. Should have made the Cavalry call days ago. I learned some new things.

**FlameRetired** · 05-13-2018, 01:20 AM

etm6477 we haven't heard any feedback.

Been playing around with this. Finally came up with a formula I felt worth posting.

Array entered in DC2 filled down and across. If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

Formula:

Please Login or Register  to view this content.

**etm6477** · 11-10-2019, 09:51 PM

Hello again Everyone.

I wanna first start off by thanking everyone for all their help that they've provided since I first asked this question over a year and a half ago. I appreciate it tremendously, but since then, I've had to change a couple minor things in the spreadsheet in which I continue to analyze data with.
I've attached the same file from my initial question, but with a few changes:

The numbers before used to go to just 80, but now the 1st 10 numbers repeat after 80 because I realized after I posted this that the numbers loop around... So the same way that 18,19,20,21,22 is a run of 5 numbers, according to the program 78,79,80,1,2 is a run of 5 numbers as well, so here lies my issues:

I first needed to repeat the 1st 10 numbers, so with a little research I figured out the formula to get the number 1-10 to repeat

"=IF($C52=0,"",$C52)"

Now I'm not sure if that's the best formula to use, or I can alter the original formula of

=IF(ISERROR(MATCH(COLUMN(A74),$CQ74:$DJ74,FALSE)),"",COLUMN(A74))

so that it can just pull the number 1-10 from the 20 random numbers in columns CQ to DJ

Now this is the part that goes back to my original question. I need the formula you helped me with before

=IFERROR(SMALL(IF(MATCH($CQ74:$DJ74,SMALL(IFERROR((($CU74:$DL74-4)=$CQ74:$DJ74)*$CQ74:$DJ74,0),COLUMN($CQ74:$DJ74)-COLUMN($CQ74)+1),1)>IFERROR(MATCH($CQ74:$DJ74-5,IF(SMALL(IFERROR((($CU74:$DL74-4)=$CQ74:$DJ74)*$CQ74:$DJ74,0),COLUMN($CQ74:$DJ74)-COLUMN($CQ74)+1)=0,-5,SMALL(IFERROR((($CU74:$DL74-4)=$CQ74:$DJ74)*$CQ74:$DJ74,0),COLUMN($CQ74:$DJ74)-COLUMN($CQ74)+1)),1),COLUMN($DJ74)),$CQ74:$DJ74),COLUMN(A:A)),"")

to also extract the runs of 78,79,80,1,2; ect.

The example I provided will give a little more detail, but the highlighted numbers are what I need to appear in my spreadsheet, that from the original formula do not at this time.

I appreciate ALL your help with this. It's gonna save me SO MUCH time on a daily basis, so thank you all again for whatever help you can give me with this.

**JeteMc** · 11-12-2019, 09:25 PM

This proposal employs helper columns which may be moved and/or hidden for aesthetic purposes.
The helper columns (EA:EF) are populated using:

Formula:

Please Login or Register  to view this content.

Dave's array entered formula is appended so that it reads:

Formula:

Please Login or Register  to view this content.

(See the yellow highlighted rows)
Let us know if you have any questions.

**menem** · 11-13-2019, 04:39 AM

Please try this condition format formula at CG2

Please Login or Register  to view this content.

And formula for C2

Please Login or Register  to view this content.

Regards.

**etm6477** · 11-13-2019, 01:08 PM

Dave...I tried plugging 77,78,79,80,1,2,3,4 in, and it didn't work

**JeteMc** · 11-13-2019, 01:25 PM

Not Dave, however I feel that you may be referring to post #23. I plugged in 77,78,79,80 in CW3:CZ3 then 1,2,3,4 in CG3:CJ3.
Next I dragged the formula in EF3 over to EH3.
Next I modified the formula in DC3 to read:

Formula:

Please Login or Register  to view this content.

Lastly I dragged that formula over to DV3.
Let us know if you have any questions.

Extract Sequential Numbers of 5 or More Into Separate Columns

Re: Extract Sequential Numbers of 5 or More Into Separate Columns

Re: Extract Sequential Numbers of 5 or More Into Separate Columns

Re: Extract Sequential Numbers of 5 or More Into Separate Columns

Re: Extract Sequential Numbers of 5 or More Into Separate Columns

Re: Extract Sequential Numbers of 5 or More Into Separate Columns

Re: Extract Sequential Numbers of 5 or More Into Separate Columns

Re: Extract Sequential Numbers of 5 or More Into Separate Columns

Re: Extract Sequential Numbers of 5 or More Into Separate Columns

Re: Extract Sequential Numbers of 5 or More Into Separate Columns

Re: Extract Sequential Numbers of 5 or More Into Separate Columns

Re: Extract Sequential Numbers of 5 or More Into Separate Columns

Re: Extract Sequential Numbers of 5 or More Into Separate Columns

Re: Extract Sequential Numbers of 5 or More Into Separate Columns

Re: Extract Sequential Numbers of 5 or More Into Separate Columns

Re: Extract Sequential Numbers of 5 or More Into Separate Columns

Re: Extract Sequential Numbers of 5 or More Into Separate Columns

Re: Extract Sequential Numbers of 5 or More Into Separate Columns

Re: Extract Sequential Numbers of 5 or More Into Separate Columns

Re: Extract Sequential Numbers of 5 or More Into Separate Columns

Re: Extract Sequential Numbers of 5 or More Into Separate Columns

Re: Extract Sequential Numbers of 5 or More Into Separate Columns

Re: Extract Sequential Numbers of 5 or More Into Separate Columns

Re: Extract Sequential Numbers of 5 or More Into Separate Columns

Re: Extract Sequential Numbers of 5 or More Into Separate Columns

Re: Extract Sequential Numbers of 5 or More Into Separate Columns

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] extract unique list then list the sequential numbers as ranges

[SOLVED] Separate input numbers into separate columns.

[SOLVED] Extract and separate the letters into their own separate columns

Bookmarks

Bookmarks

Posting Permissions