Help with data sorting - macro?
I have a block of cells that contain a number,a character, or nothing. I would like to select the cells and organize the numbers into columns, ignoring the characters. Not all of the rows contain all the numbers so when a row doesn't have a number the cell in that column should be blank. The numbers change each time. Is there a "straighten data" macro out there somewhere? Thanks, Chuck ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ |
Help with data sorting - macro?
"rine9558" wrote in message
... I have a block of cells that contain a number,a character, or nothing. I would like to select the cells and organize the numbers into columns, ignoring the characters. Not all of the rows contain all the numbers so when a row doesn't have a number the cell in that column should be blank. The numbers change each time. Is there a "straighten data" macro out there somewhere? Thanks, Chuck A block? Is your data in a single column, or a number of rows and columns? Do you want the numbers to end up in a single column or in the same columns they started out in? Would it be satisfactory just to clear the non-numeric cells? -- Bob Kilmer |
Help with data sorting - macro?
Attached is an example of the data sorting I am trying to accomplish. The actual data are 100 rows by about 1000 columns. The numbers are not always in ascending/descending order and cells sometimes contain characters which must be ignored/deleted. Thanks, Tim +----------------------------------------------------------------+ | Attachment filename: example.xls | |Download attachment: http://www.excelforum.com/attachment.php?postid=354312| +----------------------------------------------------------------+ ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ |
Help with data sorting - macro?
Excel only has 256 columns, so not sure how you expect to handle 1000
columns. Sure you don't mean 1000 rows and 100 columns. Anyway, any solution would need to know what numbers you have or at least what the range of numbers are. I assume you columns would hold sequential numbers with no missing positions, so the range of numbers would be sufficient So lets take you example 56 57 a 58 What would the end result look like for that - what happens to the "a" - is it deleted. results 56 57 58 also, you have your numbers starting several columns over to the right. Where will the numbers start in the data - where do you want the numbers to start in the end result. This could be as simple as Dim varr(1 to 1, 1 to 200) Dim rw as Long Dim i as Long for rw = 10 to 110 for i = 1 to 200 res = application.Match(i,cells(rw,1).Entirecolumn,0) if not iserror(res) then varr(1,i) = i else varr(1,i) = vbNullChar end if Next Range("G" & rw).Resize(1,200) = varr Next -- Regards, Tom Ogilvy "rine9558" wrote in message ... Attached is an example of the data sorting I am trying to accomplish. The actual data are 100 rows by about 1000 columns. The numbers are not always in ascending/descending order and cells sometimes contain characters which must be ignored/deleted. Thanks, Tim +----------------------------------------------------------------+ | Attachment filename: example.xls | |Download attachment: http://www.excelforum.com/attachment.php?postid=354312| +----------------------------------------------------------------+ ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ |
Help with data sorting - macro?
The data is being imported from another program. I can break it into chunks if I can only sort 256 columns at a time. I can also condense the rows down so all the blank cells are at the end of the row. However, I'm still left with a row of integers that I would like to be sorted into columns. The range of integers is large (each row might contain 100 numbers between 1 and 1000) and there is no row with all the numbers. I suppose I could search all the cells for the MIN and MAX, and then look for each integer in between and save that as an array which becomes the column designations (header?), then sort the numbers in each row so that they are aligned under the correct column header leaving blank cells where a row doesn't contain a number...?????? Any help is appreciated. CHUCK ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ |
Help with data sorting - macro?
Is the file a text file.
I would suggest using Line Input to read in a line at the time have an array sorted1(1 to 1, 1 to 250) sorted2(1 to 1, 1 to 250) sorted3(1 to 1, 1 to 250) sorted4(1 to 1, 1 to 250) loop throught the Line Input results and parse out your values. use value mod 250 to get the idex into the array use int(value/250)+1 to get the array to use (case statement) then just assign the values to their appropriate index, then assign each array to the appropriate row on 4 worksheets clear the arrays, get the next line. -- Regards, Tom Ogilvy rine9558 wrote in message ... The data is being imported from another program. I can break it into chunks if I can only sort 256 columns at a time. I can also condense the rows down so all the blank cells are at the end of the row. However, I'm still left with a row of integers that I would like to be sorted into columns. The range of integers is large (each row might contain 100 numbers between 1 and 1000) and there is no row with all the numbers. I suppose I could search all the cells for the MIN and MAX, and then look for each integer in between and save that as an array which becomes the column designations (header?), then sort the numbers in each row so that they are aligned under the correct column header leaving blank cells where a row doesn't contain a number...?????? Any help is appreciated. CHUCK ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ |
All times are GMT +1. The time now is 04:37 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com