Sorting Data

  • Thread starter Thread starter MicMicHK
  • Start date Start date
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!
 
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
 
Max, I slightly simplified ur formula in B1:
=offset($A$1;row(A1)*6+column(A1)-7,0)

regards,
Alojz
 
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)
 
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)
 
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
Click to expand...
 
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.
 
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.
 
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.
 
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.)
 
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 :-)
 
.. 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
---
 
Alojz said:
Max, I slightly simplified ur formula in B1:
=offset($A$1;row(A1)*6+column(A1)-7,0)
Click to expand...

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
---
 
Back
Top