Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Merge/consolidate text cells based on unique keys ? | New Users to Excel | |||
Creating a Unique List | Excel Discussion (Misc queries) | |||
Counts of unique keys in a list | Excel Discussion (Misc queries) | |||
Ctrl Shift | Macro Stops | Creating Short Cut Keys | Excel Programming | |||
Creating shortcut keys | Excel Discussion (Misc queries) |