Formula to Find Sequance/Range

R

Robert_Fabian

New Member
Joined
Apr 7, 2015
Messages
6
​i want the sequence from Filename column to appear as range in the next column

is there any formula to do this
i need a formula to get the highlighted values from the Filename Column

DateBox.noFilenameRange
1/10/2014100050001323555000132355-5000132363
1/10/201410005000132356 50001032556
1/10/201410005000132357 5000132364-5000132385
1/10/201410005000132358 5000125322
1/10/201410005000132359
1/10/201410005000132360
1/10/201410005000132361
1/10/201410005000132362
1/10/201410005000132363
1/10/201410005001032556
1/10/201410005000132364
1/10/201410005000132365
1/10/201410005000132366
1/10/201410005000132367
1/10/201410005000132368
1/10/201410005000132369
1/10/201410005000132370
1/10/201410005000132371
1/10/201410005000132372
1/10/201410005000132373
1/10/201410005000132374
1/10/201410005000132375
1/10/201410005000132376
1/10/201410005000132377
1/10/201410005000132378
1/10/201410005000132379
1/10/201410005000132380
1/10/201410005000132381
1/10/201410005000132382
1/10/201410005000132383
1/10/201410005000132384
1/10/201410005000132385
1/10/201410005000125322
1/10/201410005000132386
1/10/201410005000132387
1/10/201410005000132388
1/10/201410005000132389
1/10/201410005000132390
1/10/201410005000132391
1/10/201410005000132392
1/10/201410005000132393
1/10/201410005000132394
1/10/201410005000132395
1/10/201410005000132396
1/10/201410005000132397
1/10/201410005000132398
1/10/201410005000132399
1/10/201410005000132400
1/10/201410005000132401
1/10/201410005000132402
1/10/201410005000132403
1/10/201410005000132404
1/10/201410005000132405
1/10/201410005000132406
1/10/201410005000132407
1/10/201410005000132408
1/10/201410005000132409
1/10/201410005000132410
1/10/201410005000132411

<tbody>
</tbody>
 
Last edited:

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
Sort by date Sort by votes
Do you want to regroup by date? Box number? Similar date and box number? Like Special-K99 I do not understand why you have 2 elements in the first line, only 1 in the second, etc.

but the formula to groupe text is =concatenate(C1,"-",C2,"-",C3) or =C1&"-"&C2&"-"&C3
 
Last edited:
Upvote 0
The Logic is simple
5000132355 is the starting number of the sequence
500013236 is the ending number of the sequence

 
Upvote 0
The Logic is simple
5000132355 is the starting number of the sequence
500013236 is the ending number of the sequence

The second line contains only one element because there is no other element in that sequence
 
Upvote 0
But what dictates each sequence? Your values in the Box No. column are all equal, as are those in the Date column.

Regards
 
Upvote 0
500013236 is the ending number of the sequence?

How do you KNOW that's the ending number of the sequence since that number doesn't occur ANYWHERE in the data?

Can I pick a random number and we use that instead since the number I pick won't occur ANYWHERE in the data either?
 
Upvote 0
Regrettably it's not.

Since given the filename 5000132358 your answer of 5000125322 is not the value of the filename.
Same goes for filename 5000132356, 50001032556 is a completely different number.

And the ending number, how is that determined since most of those are not the filename either.

I'm starting to lose interest in solving this (as yet) undefinable problem. . . :(
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Solved
How to work out general trend in a series of numbers (film rankings)
Replies
5
Views
158
stefanaalten
S
D
  • Solved
Create totals using drag down
Replies
2
Views
210
Danny54
D
K
  • Question
Split list/table according to specified time gap
2
Replies
11
Views
433
Kariba
K
E
  • Question
Function to return array of 10 largest absolute values while maintaining sign
Replies
0
Views
190
eurobonds
E
H
  • Question
using sorting and array to deal with big data for expected result
Replies
3
Views
194
Hasson
H

Forum statistics

Threads
1,217,382
Messages
6,136,231
Members
450,000
Latest member
jgp19

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top