| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 unknown ranges for this numbers, not the actual numbers. How can I do that? Doing this manually after sorting the data is taking us forever. 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? |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Can Excel "extract" ranges of numbers listed in the A column?
You said you wanted the UNknown ranges.
In B2: =IF(A2-A1=1,"",A1&" - "&A2) and fill down. This formula has to be modified somewhat to report the known ranges, as in your example. -- Kind regards, Niek Otten Microsoft MVP - Excel "Quco" wrote in message ... | 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 unknown ranges for this | numbers, not the actual numbers. How can I do that? | | Doing this manually after sorting the data is taking us forever. | | 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? | |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Can Excel "extract" ranges of numbers listed in the A column?
The code below may do what you want. It will scan the numbers in column A of
Sheet1 and put the start and end number of each series of sequential numbers out to Sheet2, with the starting number of the block in Sheet2 column A and ending number of each block in Sheet2 column B. Change the lines of code marked with '<<< to your specific needs. Sub LookAtLists() Dim StartRow As Long ' data begins in this row Dim EndRow As Long ' data ends in this row (calculated) Dim Temp As Long Dim RowNdx As Long Dim Dest As Range ' results are written starting in this cell Dim DataColumn As String ' data is in this column Dim WS As Worksheet ' data resides on this worksheet Dim SaveStart As Long StartRow = 1 '<<< CHANGE DataColumn = "A" '<<< CHANGE Set WS = Worksheets("Sheet1") '<<< CHANGE Set Dest = Worksheets("Sheet2").Range("A1") '<<< CHANGE With WS ' note the leading periods in the lines of code below EndRow = .Cells(.Rows.Count, DataColumn).End(xlUp).Row SaveStart = .Cells(StartRow, DataColumn) For RowNdx = StartRow + 1 To EndRow If .Cells(RowNdx, DataColumn) + 1 < .Cells(RowNdx + 1, DataColumn) Then Dest(1, 1) = SaveStart Dest(1, 2) = .Cells(RowNdx, DataColumn).Value SaveStart = .Cells(RowNdx + 1, DataColumn).Value Set Dest = Dest(2, 1) End If Next RowNdx End With End Sub -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting www.cpearson.com (email on the web site)Sub LookAtLists() "Quco" wrote in message ... 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 unknown ranges for this numbers, not the actual numbers. How can I do that? Doing this manually after sorting the data is taking us forever. 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? |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Can Excel "extract" ranges of numbers listed in the A column?
Chip,
I'm learning to read code, I inserted the code as a Module in Sheet 1 (where I have the data listed starting in cell A1). Then I went to view macros run macro and selected the title LookAtLists() to run it. It runs, but stops saying there is a syntaxis error at this cell: If .Cells(RowNdx, DataColumn) + 1 < .Cells(RowNdx + 1, DataColumn) In the meantime I'm going to close and open the excel spreadsheet without saving changes. Maybe I need to activate the macros? but I thought I did that. "Chip Pearson" wrote: The code below may do what you want. It will scan the numbers in column A of Sheet1 and put the start and end number of each series of sequential numbers out to Sheet2, with the starting number of the block in Sheet2 column A and ending number of each block in Sheet2 column B. Change the lines of code marked with '<<< to your specific needs. Sub LookAtLists() Dim StartRow As Long ' data begins in this row Dim EndRow As Long ' data ends in this row (calculated) Dim Temp As Long Dim RowNdx As Long Dim Dest As Range ' results are written starting in this cell Dim DataColumn As String ' data is in this column Dim WS As Worksheet ' data resides on this worksheet Dim SaveStart As Long StartRow = 1 '<<< CHANGE DataColumn = "A" '<<< CHANGE Set WS = Worksheets("Sheet1") '<<< CHANGE Set Dest = Worksheets("Sheet2").Range("A1") '<<< CHANGE With WS ' note the leading periods in the lines of code below EndRow = .Cells(.Rows.Count, DataColumn).End(xlUp).Row SaveStart = .Cells(StartRow, DataColumn) For RowNdx = StartRow + 1 To EndRow If .Cells(RowNdx, DataColumn) + 1 < .Cells(RowNdx + 1, DataColumn) Then Dest(1, 1) = SaveStart Dest(1, 2) = .Cells(RowNdx, DataColumn).Value SaveStart = .Cells(RowNdx + 1, DataColumn).Value Set Dest = Dest(2, 1) End If Next RowNdx End With End Sub -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting www.cpearson.com (email on the web site)Sub LookAtLists() "Quco" wrote in message ... 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 unknown ranges for this numbers, not the actual numbers. How can I do that? Doing this manually after sorting the data is taking us forever. 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? |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Can Excel "extract" ranges of numbers listed in the A column?
Still haven't figured out what to do at this point. I did save the Excel
spreadsheet as *.xlsm to keep the macro available. For now I'll continue to work on the spreadsheet manually. "Quco" wrote: Chip, I'm learning to read code, I inserted the code as a Module in Sheet 1 (where I have the data listed starting in cell A1). Then I went to view macros run macro and selected the title LookAtLists() to run it. It runs, but stops saying there is a syntaxis error at this cell: If .Cells(RowNdx, DataColumn) + 1 < .Cells(RowNdx + 1, DataColumn) In the meantime I'm going to close and open the excel spreadsheet without saving changes. Maybe I need to activate the macros? but I thought I did that. "Chip Pearson" wrote: The code below may do what you want. It will scan the numbers in column A of Sheet1 and put the start and end number of each series of sequential numbers out to Sheet2, with the starting number of the block in Sheet2 column A and ending number of each block in Sheet2 column B. Change the lines of code marked with '<<< to your specific needs. Sub LookAtLists() Dim StartRow As Long ' data begins in this row Dim EndRow As Long ' data ends in this row (calculated) Dim Temp As Long Dim RowNdx As Long Dim Dest As Range ' results are written starting in this cell Dim DataColumn As String ' data is in this column Dim WS As Worksheet ' data resides on this worksheet Dim SaveStart As Long StartRow = 1 '<<< CHANGE DataColumn = "A" '<<< CHANGE Set WS = Worksheets("Sheet1") '<<< CHANGE Set Dest = Worksheets("Sheet2").Range("A1") '<<< CHANGE With WS ' note the leading periods in the lines of code below EndRow = .Cells(.Rows.Count, DataColumn).End(xlUp).Row SaveStart = .Cells(StartRow, DataColumn) For RowNdx = StartRow + 1 To EndRow If .Cells(RowNdx, DataColumn) + 1 < .Cells(RowNdx + 1, DataColumn) Then Dest(1, 1) = SaveStart Dest(1, 2) = .Cells(RowNdx, DataColumn).Value SaveStart = .Cells(RowNdx + 1, DataColumn).Value Set Dest = Dest(2, 1) End If Next RowNdx End With End Sub -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting www.cpearson.com (email on the web site)Sub LookAtLists() "Quco" wrote in message ... 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 unknown ranges for this numbers, not the actual numbers. How can I do that? Doing this manually after sorting the data is taking us forever. 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? |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Can Excel "extract" ranges of numbers listed in the A column?
The code got line wrapped in the news post. The line you are having
problems with should end with the word "Then" and the "Then" on the next line should be deleted. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting www.cpearson.com (email on the web site) "Quco" wrote in message ... Chip, I'm learning to read code, I inserted the code as a Module in Sheet 1 (where I have the data listed starting in cell A1). Then I went to view macros run macro and selected the title LookAtLists() to run it. It runs, but stops saying there is a syntaxis error at this cell: If .Cells(RowNdx, DataColumn) + 1 < .Cells(RowNdx + 1, DataColumn) In the meantime I'm going to close and open the excel spreadsheet without saving changes. Maybe I need to activate the macros? but I thought I did that. "Chip Pearson" wrote: The code below may do what you want. It will scan the numbers in column A of Sheet1 and put the start and end number of each series of sequential numbers out to Sheet2, with the starting number of the block in Sheet2 column A and ending number of each block in Sheet2 column B. Change the lines of code marked with '<<< to your specific needs. Sub LookAtLists() Dim StartRow As Long ' data begins in this row Dim EndRow As Long ' data ends in this row (calculated) Dim Temp As Long Dim RowNdx As Long Dim Dest As Range ' results are written starting in this cell Dim DataColumn As String ' data is in this column Dim WS As Worksheet ' data resides on this worksheet Dim SaveStart As Long StartRow = 1 '<<< CHANGE DataColumn = "A" '<<< CHANGE Set WS = Worksheets("Sheet1") '<<< CHANGE Set Dest = Worksheets("Sheet2").Range("A1") '<<< CHANGE With WS ' note the leading periods in the lines of code below EndRow = .Cells(.Rows.Count, DataColumn).End(xlUp).Row SaveStart = .Cells(StartRow, DataColumn) For RowNdx = StartRow + 1 To EndRow If .Cells(RowNdx, DataColumn) + 1 < .Cells(RowNdx + 1, DataColumn) Then Dest(1, 1) = SaveStart Dest(1, 2) = .Cells(RowNdx, DataColumn).Value SaveStart = .Cells(RowNdx + 1, DataColumn).Value Set Dest = Dest(2, 1) End If Next RowNdx End With End Sub -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting www.cpearson.com (email on the web site)Sub LookAtLists() "Quco" wrote in message ... 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 unknown ranges for this numbers, not the actual numbers. How can I do that? Doing this manually after sorting the data is taking us forever. 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? |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Can Excel "extract" ranges of numbers listed in the A column?
Thank you! I wanted to ask for your advice about what book can I get to learn
more about Macros and Excel. I really need to start writing some useful code for our office! "Chip Pearson" wrote: The code got line wrapped in the news post. The line you are having problems with should end with the word "Then" and the "Then" on the next line should be deleted. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting www.cpearson.com (email on the web site) "Quco" wrote in message ... Chip, I'm learning to read code, I inserted the code as a Module in Sheet 1 (where I have the data listed starting in cell A1). Then I went to view macros run macro and selected the title LookAtLists() to run it. It runs, but stops saying there is a syntaxis error at this cell: If .Cells(RowNdx, DataColumn) + 1 < .Cells(RowNdx + 1, DataColumn) In the meantime I'm going to close and open the excel spreadsheet without saving changes. Maybe I need to activate the macros? but I thought I did that. "Chip Pearson" wrote: The code below may do what you want. It will scan the numbers in column A of Sheet1 and put the start and end number of each series of sequential numbers out to Sheet2, with the starting number of the block in Sheet2 column A and ending number of each block in Sheet2 column B. Change the lines of code marked with '<<< to your specific needs. Sub LookAtLists() Dim StartRow As Long ' data begins in this row Dim EndRow As Long ' data ends in this row (calculated) Dim Temp As Long Dim RowNdx As Long Dim Dest As Range ' results are written starting in this cell Dim DataColumn As String ' data is in this column Dim WS As Worksheet ' data resides on this worksheet Dim SaveStart As Long StartRow = 1 '<<< CHANGE DataColumn = "A" '<<< CHANGE Set WS = Worksheets("Sheet1") '<<< CHANGE Set Dest = Worksheets("Sheet2").Range("A1") '<<< CHANGE With WS ' note the leading periods in the lines of code below EndRow = .Cells(.Rows.Count, DataColumn).End(xlUp).Row SaveStart = .Cells(StartRow, DataColumn) For RowNdx = StartRow + 1 To EndRow If .Cells(RowNdx, DataColumn) + 1 < .Cells(RowNdx + 1, DataColumn) Then Dest(1, 1) = SaveStart Dest(1, 2) = .Cells(RowNdx, DataColumn).Value SaveStart = .Cells(RowNdx + 1, DataColumn).Value Set Dest = Dest(2, 1) End If Next RowNdx End With End Sub -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting www.cpearson.com (email on the web site)Sub LookAtLists() "Quco" wrote in message ... 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 unknown ranges for this numbers, not the actual numbers. How can I do that? Doing this manually after sorting the data is taking us forever. 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? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell | Excel Discussion (Misc queries) | |||
| How do I change the column heading in Excel to display "A" "B" "C | New Users to Excel | |||
| My Column display as "numbers" instead of "alphabets" | Excel Discussion (Misc queries) | |||
| Can Excel "extract" ranges of numbers listed in the A column? | Excel Discussion (Misc queries) | |||
| Column listed as "Number Stored as Text" - Exported from Access | Excel Programming | |||
Linear Mode
