Help with data sorting - macro?

  • Thread starter Thread starter rine9558
  • Start date Start date
R

rine9558

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

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