try this:
Assumes ranges are determined by first 3 digits of the numbers
your original list in column A, starting row 1
in B1: =A1
in C1:
=INDEX(A:A,MATCH(B1,A:A,0)+SUMPRODUCT(--(LEFT(A1:A1000,3)=LEFT(B1,3)))-1)
in B2: =INDEX(A:A,MATCH(C1,A:A,0)+1)
in C2:
=INDEX(A:A,MATCH(B2,A:A,0)+SUMPRODUCT(--(LEFT(A2:A1001,3)=LEFT(B2,3)))-1)
Copy down B2 & C2 until you get #N/A error
HTH
"Quco" wrote:
Thank you, but we don't know what the limits for each range are going to be.
I was asking if Excel can automatically group these ranges. If I use the
formulas you suggested I still need to visually inspect the spreadsheet. The
goal is to cut the time to identify and isolate these ranges.
"Toppers" wrote:
=SUMPRODUCT(--($A$1:$A$1000=41222),--($A$1:$A$1000<=41227))
use absolute addresses
"Toppers" wrote:
=SUMPRODUCT(--(A1:A1000=41222),--(A1:A1000<=41227))
will count number in the above range
Better to put your ranges in cells and use:
=SUMPRODUCT(--(A1:A1000=X1),--(A1:A1000<=Y1))
X1=41222, Y1=41227
HTH
"Quco" wrote:
Thank you for quick replies but it's not about the MAX and MIN commands (I
think). Here's an example, I cut and paste a super-small portion of the
sorted data he
41222
41223
41224
41225
41226
41227
41762
41763
41764
41765
41766
41767
41768
41769
41770
41771
41772
41773
41774
41775
41776
41777
42302
42303
42304
42305
42306
42307
42308
42309
42310
42311
42312
42313
42314
42315
42316
42830
42831
42832
42833
42834
42835
42836
42837
42838
42839
42842
42843
42844
42845
42846
42847
42848
And this is what we need:
Range 1: 41222-41227
Range 2: 41762-41777
Range 3: 42302-42316
Range 4: 42830-42848
etc...
Any suggestions?
"Stan Brown" wrote:
Mon, 9 Jul 2007 04:08:00 -0700 from Quco
:
Hi, I have a LOOOONNNG list of 5-digit numbers in a spreadsheet. They are all
listed in the A column. We only need to report the ranges for this numbers,
not the actual numbers. How can I do that? doing this manually after sorting
the data is taking forever.
What do you mean, "the ranges"?
If you mean the largest and smallest values, see the MAX and Min
functions.
--
Stan Brown, Oak Road Systems, Tompkins County, New York, USA
http://OakRoadSystems.com/