Transposing grouped data in rows

[Levy]

I have literature data grouped together comprising about
2000 rows in just 1 column. Here is an example:

TI: The variability of force platform data in normal and
cerebral palsy gait.
AU: White,-R; Agouris,-I; Selbie,-RD; Kirkpatrick,-M
SO: Clin-Biomech. 1999 Mar; 14(3): 185-92
AN: 9173098

TI: The variability of force platform data in normal and
cerebral palsy gait.
AU: White-R {a}; Agouris-I; Selbie-R-D; Kirkpatrick-M
SO: Clinical-Biomechanics. March, 1999; 14 (3) 185-192.
AN: 199900133738

Each block is 3 to 5 rows long, mostly 4&5 though. To
sort out all double references i want to sort them into
columns and then sort the autors names alphabetically.
The result will look like this:

TI: AU: SO: SI: AN:
TI: AU: SO: SI: AN:

I do have a formula for transposing this, but i still
have to select a new new range for each new entry. I also
have a transposing macro so i don't have to go through
the menu everytime. In total i have about 500 literature
hits so you see my problem!

Can someone help me with this?

Levy

---WinXP Pro SP1; Office 200 SP3---

 

[Jason Morin]

Try this, assuming your data is in col. A:

1. Insert this formula into B1, fill across to F1, and
fill down as far as needed:

=IF(ISBLANK(OFFSET($A$1,ROW()*5+COLUMN()-7,)),TRUE,OFFSET
($A$1,ROW()*5+COLUMN()-7,))

2. Select columns B thru F.
3. Copy and Paste Special > Value.
4. F5 > Special > Constants and uncheck all boxes
except "Logicals".
5. Press <ctrl><-> and select "Shift cells left".
6. Delete col. A.

HTH
Jason
Atlanta, GA

 

[Levy]

Jason,

You gave me a push in the right direction but I'm not
there yet. I translated the formula to my own language
and got it working. Only it doesn't give the right
results yet. I try to understand what you meant and i've
got a question: Isn't the COLUMN()-7 referencing a non
existing column?

Can you explain what you mean with the offset formula?
The rest is clear to me.

Thanks

Levy

 

[Levy]

Hi Jason,

I figured it out. The formula needs to be ROW()*6+COLUMN
()-8 and then it works like a charm.

Thank you very much for putting me on the right track.

Levy