Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 30
Default Creating unique keys

I have two questions/problems that I'd like some help with. I'm using
Excel 2003 and have a spreadsheet, Master Roster, containing Guardian
Names, Addresses as well as student names. There can be multiple
students with a guardian. I need to find the unique guardians and
count of how many students they're guardian to (some of these quardian
names have middle initials or middle names so I need to create a
unique field). Using these two fields I need to create another
speadsheet containing the Guardian Name, Address (city,state,zip) and
the names of their students. I thought about taking the guarian last
name, guardian first initial of first name, and the address to create
this unique field. The problem is the addresses were entered by many
people and are not consistent. 103 West Maple Ave, 103 W. Maple, 103
W Maple Avenue, PO Box 1604, P.O. 1604, etc. I'm thinking using
SUBSTITUTE I can just extract the numeric portion of the address, eg.
103. I think this and the guardian part of the key would give me the
key I'd need.

Questions: From the address column how can I extract just numeric
characters to create a new field?

How do I extract from Master Roster using this new unique field the
names of all students with the guardian? I've thought about Pivot
Tables but I'm not really experienced using that function.

Any help is really appreciated.
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 16
Default Creating unique keys

Jack, 1st, be sure you have a backup of your data (in case you need a
clean copy)
2nd: standardize the guardian names, so the names are identical for
that guardian. If they;re obviously the same guardian. . .one
containing a middle initial; one without, make them the same by
decising whether to include or remove the middle initial.

Now, you really need to learn how to work with pivot tables. They're
not as spooky as you may have been led to beileve. There are several
great books on the subject, and there are many excellent sites for
beginners, and pro's on the subject. Do check out contextures.com and
navigate to Excel tips and pivot tables for some tremendous excercises
and instruction. You'll quickly find that they'll do exactly what
you're looking for, and with a little study and praictice, pretty easy
to accomplish.

Pete

Do report back
On Feb 4, 8:08*am, Jack Deuce
wrote:
I have two questions/problems that I'd like some help with. *I'm using
Excel 2003 and have a spreadsheet, Master Roster, containing Guardian
Names, Addresses as well as student names. *There can be multiple
students with a guardian. *I need to find the unique guardians and
count of how many students they're guardian to (some of these quardian
names have middle initials or middle names so I need to create a
unique field). Using these two fields I need to create another
speadsheet containing the Guardian Name, Address (city,state,zip) and
the names of their students. *I thought about taking the guarian last
name, guardian first initial of first name, and the address to create
this unique field. *The problem is the addresses were entered by many
people and are not consistent. *103 West Maple Ave, 103 W. Maple, 103
W Maple Avenue, PO Box 1604, P.O. 1604, etc. *I'm thinking using
SUBSTITUTE I can just extract the numeric portion of the address, eg.
103. *I think this and the guardian part of the key would give me the
key I'd need.

Questions: *From the address column how can I extract just numeric
characters to create a new field?

How do I extract from Master Roster using this new unique field the
names of all students with the guardian? *I've thought about Pivot
Tables but I'm not really experienced using that function.

Any help is really appreciated.


  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 30
Default Creating unique keys

I kinda knew Pivot Tables were going to be the suggested solution and
I will give it a try. I have done some reading on them and have done
a couple of simple queries (just for totaling basically).

I solved the unique part of the address key by using ASAP Utilities
TEXT, Advanced character replace, to extract on 0-9 and used Text to
columns to extract Guardian Last & Guardian First so do have a unique
key once concatenating GL,GF,address that I used to identify the
families. Now we need to do a mass-mailing addressed to the Guardian
with the names of their children in the school system.

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
Merge/consolidate text cells based on unique keys ? x13 New Users to Excel 3 June 3rd 11 07:14 PM
Creating a Unique List Ellen G Excel Discussion (Misc queries) 5 February 27th 10 11:37 AM
Counts of unique keys in a list gazza67 Excel Discussion (Misc queries) 6 January 29th 07 10:53 AM
Ctrl Shift | Macro Stops | Creating Short Cut Keys gduron Excel Programming 3 April 6th 06 11:18 PM
Creating shortcut keys Magnus Ramfelt via OfficeKB.com Excel Discussion (Misc queries) 1 January 21st 05 02:06 PM


All times are GMT +1. The time now is 07:26 PM.

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

About Us

"It's about Microsoft Excel"