Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old 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
Default 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   Report Post  
Old 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
Default 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   Report Post  
Old 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
Default 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








Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Adding data to existing data that has a unique number in column Lars Excel Discussion (Misc queries) 3 June 28th 07 11:48 AM
Selecting only rows of data with unique identifier nackington Excel Discussion (Misc queries) 1 August 25th 06 03:21 PM
Combine data from 2 worksheets using unique identifier CathyW Excel Worksheet Functions 0 March 23rd 06 02:06 AM
Unique identifier Steve Barnett Excel Discussion (Misc queries) 19 January 6th 06 11:26 AM
Unique identifier Steve Barnett Excel Worksheet Functions 18 January 6th 06 11:26 AM


All times are GMT +1. The time now is 11:04 AM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017