I have two questions/problems that I'd like some help with. I am 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 are 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 am 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 have thought about Pivot
Tables but I am not really experienced using that function.
Any help is really appreciated.