Not sure how far this would help you,
but no harm trying it out on a spare copy ..
.. I am trying to get excel to move each line up
Assume the data is in col B
Select col B
Press F5 Special Check "Blanks" OK
Right-click on the selection Delete Shift cells up OK
This should delete all the intervening blank cells in the col,
and move all data cells up to the top
Now, to re-arrange the columnar data in a row-wise fashion, a lot depends on
the regularity of the data structure down the column. If it's regular (for
example if the data is in groups of exactly say, 8 cells each as per sample
below), then one way to quickly re-arrange it ..:
Assume the data below is in col B, from B1 down
The data is in groups of 8 cells each ..
Acc#1
Name1
Address1
Suburb1
state1
zip1
Phone1
Fax1
Acc#2
Name2
Address2
Suburb2
state2
zip2
Phone2
Fax2
etc
Put in C1:
=OFFSET($B$1,ROWS($A$1:A1)*8-8+COLUMNS($A$1:A1)-1,)
Copy C1 across a total of 8 cols to J1, then fill down until zeros appear,
signalling exhaustion of data extracted from col B.
For the sample data above, you'd get:
Acc#1 Name1 Address1 Suburb1 state1 zip1 Phone1 Fax1
Acc#2 Name2 Address2 Suburb2 state2 zip2 Phone2 Fax2
etc
Freeze the results in cols C to J with a Copy Paste special Values OK,
either in-place or elsewhere
If the data's not regular in structure (and unfortunately, this appears to
be the case as described in your post), I don't know how it could be done
other than manually. But do hang around awhile for possible insights from
others.
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Mrbanner" wrote in message
oups.com...
Hi ,
Not sure if people can Help i am trying to find if there is a way to do
the following i have
65,000 lines of stuff most not getting used
What i am trying to do is the following
I use a program that outs to a file that looks like below
I am trying to get excel to move each line up
i am trying to combine the the following onto 1 line
(A1)Acc#,(B1)Name,(c1)Address,(d1)Address line2
,(e1)suburb,(f1)state,(G1)zip,(i1)Phone,
(j1)fax.
I have tryed to delete all of the Empty Boxes but this doesnt work due
to some people not having
a phone number or fax.
also some dont have account number
Is there any easy way i can move these up? and evan them out?
i dont want to do a manual way could take 10 hours to do it.
the document looks the same all the way down to the bottom 65000 lines
this is the only way i can get output from app i use
A B C D E F G H I J K
1 Acc#
2
3 Name
4
5
6 Address
7
8 Suburb
9 state
10 zip
11
12 Phone
13 Fax
14
15
16
14
18
19
20
21
22 Acc#
23
24
25
26 Name
27
28 Address
29
30
31 Suburb
32
33 State
34
35 Zip
36
37
38
39
40