Remember Me?

#1
December 9th 07, 06:13 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Dec 2007 Posts: 1
Adding a unique identifier to a column of data

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

#2
December 9th 07, 06:58 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Jul 2006 Posts: 695
Adding a unique identifier to a column of data

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

#3
December 9th 07, 07:01 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Nov 2006 Posts: 15,768
Adding a unique identifier to a column of data

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

 Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On

 Similar Threads Thread Thread Starter Forum Replies Last Post Lars Excel Discussion (Misc queries) 3 June 28th 07 11:48 AM nackington Excel Discussion (Misc queries) 1 August 25th 06 03:21 PM CathyW Excel Worksheet Functions 0 March 23rd 06 02:06 AM Steve Barnett Excel Discussion (Misc queries) 19 January 6th 06 11:26 AM Steve Barnett Excel Worksheet Functions 18 January 6th 06 11:26 AM

All times are GMT +1. The time now is 06:25 AM.