| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Can Excel "extract" ranges of numbers listed in the A column?
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. |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Can Excel "extract" ranges of numbers listed in the A column?
Can you give an example of what you want reported?
"Quco" wrote: 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. |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Can Excel "extract" ranges of numbers listed in the A column?
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/ |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Can Excel "extract" ranges of numbers listed in the A column?
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/ |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Can Excel "extract" ranges of numbers listed in the A column?
=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/ |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Can Excel "extract" ranges of numbers listed in the A column?
=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/ |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Can Excel "extract" ranges of numbers listed in the A column?
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/ |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Can Excel "extract" ranges of numbers listed in the A column?
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/ |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Chg 1 "Last, First Mid" column to 3 "First", "Middle", "Last" colu | Excel Discussion (Misc queries) | |||
| Defining Multiple "named" data ranges for Piot Tables in Excel 200 | Excel Discussion (Misc queries) | |||
| HELP on "left","right","find","len","substitute" functions | Excel Discussion (Misc queries) | |||
| Count occurences of "1"/"0" (or"TRUE"/"FALSE") in a row w. conditions in the next | New Users to Excel | |||
| remove last character in a column of part numbers if a "V" | Excel Discussion (Misc queries) | |||
Linear Mode
