Copying data

S

Shanks

Hi

I need help in copying data.

I have data exported from a text file and has somthing
like following format -

Main Grp: 600
Sub Grp: 11890

grp desc amount
7878 ABCDEMF 89887
ABCDEMF 89784
ABCDEMF 45654
ABCDEMF 56587

<<page break>>>

Main Grp: 600
Sub Grp: 11900

grp desc amount
7900 ABCDEMF 56844
ABCDEMF 12546
ABCDEMF 74125
ABCDEMF 36985

so need to copy 'grp' below alongwith the lines of data
within the group and 'sub grp' also alongside the data..
may be on right side after amount.

I tried with Edit/Go to.. and special options but could
not get any clue to do this.

Can anyone help to achive this?
It will be really great thing for me.

Thx in Adv
Shanks
 
M

makisa

Hi,

Since you are doing it with fixed with delimited field, what I would suggest
is you paste your data into a text file and from there.

You do a Data->Import External Data->From Text File, with Fixed Width,

That could help in solving your problems.
 
S

Shanks

I have done these steps. There are around 200 rows of
data and around 30 'grps'.... so its very tidious.

any udf or other solution would help:)

Thx
Shanks
 
M

Myrna Larson

I'm assuming you have imported the data, then used Data/Text to Columns, to
split it into 3 columns.

If you have done that, "Main" is in A1, "Grp:" in B1, 600 in C1.

First, insert a blank row above, and put these headers in A1:E1

MAIN,SUB,GRP,DESC,AMOUNT

Then insert 2 new columns, A and B.

Now Main should be in C2, Grp: in D2, and the group number in E2

In A2, put this formula: =IF(C2="Main",E2,A1)
In B2, put this formula: =IF(C3="Sub",E3,B1)

Copy these formulas down to the bottom of your list.

The next task is to get the Grp number in each row. To do that, select C2 down
through the last row of data; then Edit/Goto, click Special and select Blanks.
That should select all blanks in column C, with the active cell being C4. In
that cell, type =C3, and press CTRL+ENTER to put the same formula in all blank
cells.

Then convert the formulas in column A:C to their values: Edit/Copy columns
A:C; without changing the selection, Edit/Paste Special and select the Values
option.

Then sort on the Grp column (C), so the original header rows will go to the
bottom. Delete these rows.

You should end up with one row for each of the original rows that had an
amount, with the main group in A, sub group in B, group in C, description in
D, and amount in E.
 
Top