Sorting Data

M

MicMicHK

Hi, I have a text file which contains about 500 customers info, it's in a
format like this:

1. ABC Company
Name: Mary
Tel: 8391039
e-mail: (e-mail address removed)
address: xxxxxxxxx

2. XYZ Company
Name: John
Tel: 88347899
e-mail: (e-mail address removed)
address: xxxxxxxxx



What's the quickest way to change it into a format like this?

Company Name Tel e-mail address
1 ABC Com Mary ... som.. xxxxxxx
2 XYZ Com John ... Som... XXXXX


Doesn't have to be Excel, if others like Access can do the job, please
kindly tell me, thanks a lot!
 
M

Max

Source data as posted assumed representative, running in A1 down
In B1: =OFFSET($A$1,ROWS($1:1)*6-6+COLUMNS($A:A)-1,)
Copy B1 across to F1, fill down as far as required to exhaust source data.
Freeze cols B to F with an "in-place" copy n paste special as value. Insert a
new col C, then select col B, click Data > Text to Columns, delimited, enter
a period for "Other" in step 2. Click Finish. And that should return the
exact results that you seek in cols B to G.

Success?, click YES below
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:23,500 Files:370 Subscribers:66
xdemechanik
 
A

Alojz

Max, I slightly simplified ur formula in B1:
=offset($A$1;row(A1)*6+column(A1)-7,0)

regards,
Alojz
 
S

Shane Devenshire

Hi,

And of course you can simply that to

=OFFSET($A$1,ROW(A1)*6+COLUMN(A1)-7,)
or equally short:
=INDIRECT("A"&ROW(A1)*6+COLUMN(A1)-6)
 
S

Shane Devenshire

Hi again,

And if you want to get fancy you can name the range where the data is in
column a "A" and then the INDEX function shortens to

=INDEX(A,ROW(A1)*6+COLUMN(A1)-6)
 
A

Alojz

yes, Shane, thanks for your remark

Shane Devenshire said:
Hi again,

And if you want to get fancy you can name the range where the data is in
column a "A" and then the INDEX function shortens to

=INDEX(A,ROW(A1)*6+COLUMN(A1)-6)

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire
 
M

MicMicHK

Hi, Max, Alojz and Shane,

Thanks a lot for all of you guys help. I've got some concept now, but I
don't really understand start from here:

"Freeze cols B to F with an "in-place" copy n paste special as value. Insert
a
new col C, then select col B, click Data > Text to Columns, delimited, enter
a period for "Other" in step 2. Click Finish. And that should return the
exact results that you seek in cols B to G."


Sorry for I'm too stupid, but please kindly explain more clearly, thanks a
lot.
 
M

MicMicHK

Hi, Max, Alojz and Shane,

Thanks for all of you guys helping. I've got some concept now, but I don't
really understand start from here:

"Freeze cols B to F with an "in-place" copy n paste special as value. Insert
a
new col C, then select col B, click Data > Text to Columns, delimited, enter
a period for "Other" in step 2. Click Finish. And that should return the
exact results that you seek in cols B to G."

Especially "Freeze cols B to F with an "in-place" copy n paste special as
value."

Sorry for I'm too stupid, but please kindly explain more clearly, thanks a
lot.
 
A

Alojz

Hi, the formula itself just makes ur data will be displayed in rows, company
by company. Insert one of formulas posted (all of them should work), through
col B-G.
As u can see, data are sorted, but still Name, Tel, e-mail and address are
in each row. To extraxt those, as they are repeating on each line, use text
to column trick. Add new column behind column C. Copy column B and paste
special on the same place as text. Then go to data, text to columns and split
the column using fixed width. (U do not need even add new column, just make
text to column, fixed width and do not insert splitted left part, just the
right hand one (from the original content of cell C2, e.g. Name: Mary, u will
get only the word Mary). Try and u will definitely see, what we are talking
about.
 
A

Alojz

Sorry, I meant copy col C, not col B. But first u can copy and paste special,
values, the whole new data area to get rid of formulas, then use text to col
trick to get rid of all repeating labels (e.g. Name:, Tel:, etc.)
 
A

Alojz

I created sample file for u, will send it to u together with word doc
containing screenshot step by step. I use Czech version of Excel 2007 so do
not be confused with screenshots, little bit of research gives u proper
answer, I am dead sure.

Sending the mail right now.

Click yes if this help as I did something additonal to U :)
 
M

Max

.. Freeze cols B to F with an "in-place" copy n paste special as value
"Freeze" means to remove the formulas and preserve the underlying calculated
values returned. An "in-place" copy n paste special as values means to copy
the formulas range, then to overwrite the same formulas range with a "Paste
special" as values, which accomplishes "Freezing". Trust that clarifies it
better.

Btw, I left the expression intentionally unsimplified arithmetically so that
it is easier to see the underlying pattern, which aids understanding how it
works, and from there, potential cross application.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:23,500 Files:370 Subscribers:66
xdemechanik
---
 
M

Max

Alojz said:
Max, I slightly simplified ur formula in B1:
=offset($A$1;row(A1)*6+column(A1)-7,0)

I had left the expression intentionally unsimplified arithmetically so that
it is easier to see the underlying pattern, which aids understanding how it
works, and from there, potential cross application. And there's a subtle
difference between using ROWS($1:1) and ROW(A1), and COLUMNS($A:A) and
column(A1), but it is not of issue here.

P/s: Please refrain from using the shortforms "u" and "ur" in your
newsgroups communication. Save these for the phone text messaging.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:23,500 Files:370 Subscribers:66
xdemechanik
---
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top