I have a spreadsheet of data I exported from a Loutus Notes folder that
unfortunately I can only get in this format:

First: Fred
Last: Flintstone
Title: Controller
First: Betty
Last: Rubble
Title: Analyst
First: Donald
Last: Duck
Title: VP


Is there a function I could use to fill in some kind of unique identifier in
a column so I can turn these into records with a pivot table?
Like this:


A First: Fred
A Last: Flintstone
A Title: Controller
B First: Betty
B Last: Rubble
B Title: Analyst
C First: Donald
C Last: Duck
C Title: VP


Or any other ideas?


I want to end up with:

First Last Title

Fred Flintstone Controller
Betty Rubble Analyst
Donald Duck VP



Thanks for any ideas and help!

Holly

Insert and hit CTRL+SHIFT+ENTER - then copy down

B1=INDEX(TRIM(RIGHT($A$1:$A$1000,LEN($A$1:$A$1000)-6)),SMALL(ROW($A$1:$A$1000),(ROW(1:1)*3)-2))
C1=INDEX(TRIM(RIGHT($A$1:$A$1000,LEN($A$1:$A$1000)-6)),SMALL(ROW($A$1:$A$1000),(ROW(1:1)*3)-1))
D1=INDEX(TRIM(RIGHT($A$1:$A$1000,LEN($A$1:$A$1000)-6)),SMALL(ROW($A$1:$A$1000),(ROW(1:1)*3)))


"Holly" skrev:

I have a spreadsheet of data I exported from a Loutus Notes folder that
unfortunately I can only get in this format:

First: Fred
Last: Flintstone
Title: Controller
First: Betty
Last: Rubble
Title: Analyst
First: Donald
Last: Duck
Title: VP


Is there a function I could use to fill in some kind of unique identifier in
a column so I can turn these into records with a pivot table?
Like this:


A First: Fred
A Last: Flintstone
A Title: Controller
B First: Betty
B Last: Rubble
B Title: Analyst
C First: Donald
C Last: Duck
C Title: VP


Or any other ideas?


I want to end up with:

First Last Title

Fred Flintstone Controller
Betty Rubble Analyst
Donald Duck VP



Thanks for any ideas and help!

Holly

Try this...

Assume your data is in the range A1:A9

Select the range A1:A9
Goto DataText to Columns
DelimitedNextSpaceNext
In step 3 of the wizard the column that contains First, Last and Title will
be highlighted
Select Do not import column (skip)
Finish

Enter these column headers in D1:F1 - First, Last, Title

Enter this formula in D2 and copy across to F2:

=INDEX($A:$A,(ROWS($1:1)-1)*3+COLUMNS($A:B)-1)

Select the range D2:F2 and copy down until you get returns of 0.

Select the entire range of formulas
Goto EditCopy
Then, EditPaste SpecialValuesOK

Select all the 0s and delete them.

--
Biff
Microsoft Excel MVP


"Holly" wrote in message
. ..
I have a spreadsheet of data I exported from a Loutus Notes folder that
unfortunately I can only get in this format:

First: Fred
Last: Flintstone
Title: Controller
First: Betty
Last: Rubble
Title: Analyst
First: Donald
Last: Duck
Title: VP


Is there a function I could use to fill in some kind of unique identifier
in a column so I can turn these into records with a pivot table?
Like this:


A First: Fred
A Last: Flintstone
A Title: Controller
B First: Betty
B Last: Rubble
B Title: Analyst
C First: Donald
C Last: Duck
C Title: VP


Or any other ideas?


I want to end up with:

First Last Title

Fred Flintstone Controller
Betty Rubble Analyst
Donald Duck VP



Thanks for any ideas and help!

Holly