Tags Tags

Creating unique keys

February 04th, 2012 - 09:08 am ET by Jack Deuce | Report spam
Help Create a new topic
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.
email Follow the discussionReplies 2 repliesReplies Make a reply

Replies

#1 Pete
February 04th, 2012 - 09:53 am ET | Report spam
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.

Similar topics

Make your own search :