HELP me sort these

[Mrbanner]

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

 

[Max]

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.

--

 

[David McRitchie]

I expect you would really want these in a spreadsheet format; otherwise,
you could use notepad or other text editor for your data.

If you can start with the original where you probably have each group
consisting of 21 lines you can easily use coding to put into spreadsheet
format. It appears that the data has been somewhat changed as I cannot
add 21 to the row number and get the next Fax number for instance.

Take a look at
http://www.mvps.org/dmcritchie/excel/snakecol.htm

Since you have not provided an example of simulated data, I can't
determine whether you have an entry prefixed by "Name" or whether
name is a name. I am assuming the worst that the line with name
has only a name and no prefix tag.

No matter what you do you have to have some way of determining
programmatically where a group (addressee) changes. If your data
is in as bad a shape as you indicated you will then have to programmatically
insert empty cells on a row so that you have items in their proper
columns. Once everything is lined up in columns it is very simple to
move columns around (see fillhand.htm).

The account number for instance probably has a specific format that
can be identified such as 12 characters begins with a letter ends with
a number.