I am trying to compile variable data with not as frequently changed data. It has repetitive information and incomplete data. There are numerous spreadsheets available in excel but I think access is a better choice. I would like to link these tables and make a relationship between various fields. Here is a sample of what I have:
Year Person ID Office State Address Sort#
99 Joe 12 downtown Z 1st Ave 1
Sort# Office Address Person Year State Gender
6 Uptown 2nd St Sam 00 X F
ID Year Person State Sort# Gender
12 01 Joe Y 2 M
Year Person ID Office State Sort#
02 Fred 34 uptown X 5
This is what I can see getting:
Year Person ID Office State Address Sort# Birthday
99 Joe 12 downtown Z 1st Ave 1
00 Sam uptown X 2nd St 6 10/15
01 Joe 12 Y 2
02 Fred 34 uptown X 5
I want to fill in the empty cells and filter out the duplicates. Is there a formula that says if the office is the same and the state is the same use the information from the Address and complete the cell?
Some more notes on the infromation.
Each year is a different spread sheet and the fields are not in the same order for each sheet, the sort# is dependant on the year, the ID is unique to the person, the person is only listed once per year the person may be listed in a different year. There is not the same number of offices each year. I want to be able to filter the data easily without duplications if possible so I can make statistical analysis of the data. Any suggestions on some first steps? Besides starting from scratch.
Hating my predecessor and the inconsistency of data entry!
Year Person ID Office State Address Sort#
99 Joe 12 downtown Z 1st Ave 1
Sort# Office Address Person Year State Gender
6 Uptown 2nd St Sam 00 X F
ID Year Person State Sort# Gender
12 01 Joe Y 2 M
Year Person ID Office State Sort#
02 Fred 34 uptown X 5
This is what I can see getting:
Year Person ID Office State Address Sort# Birthday
99 Joe 12 downtown Z 1st Ave 1
00 Sam uptown X 2nd St 6 10/15
01 Joe 12 Y 2
02 Fred 34 uptown X 5
I want to fill in the empty cells and filter out the duplicates. Is there a formula that says if the office is the same and the state is the same use the information from the Address and complete the cell?
Some more notes on the infromation.
Each year is a different spread sheet and the fields are not in the same order for each sheet, the sort# is dependant on the year, the ID is unique to the person, the person is only listed once per year the person may be listed in a different year. There is not the same number of offices each year. I want to be able to filter the data easily without duplications if possible so I can make statistical analysis of the data. Any suggestions on some first steps? Besides starting from scratch.
Hating my predecessor and the inconsistency of data entry!
