Can Excel "extract" ranges of numbers listed in the A column?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
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.
Click to expand...

What do you mean, "the ranges"?

If you mean the largest and smallest values, see the MAX and Min
functions.
 
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 here:

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?
 
=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
 
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.
 
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
 
Back
Top