Creating unique keys

[Jack Deuce]

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.

 

[Pete]

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

 

[Jack Deuce]

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.