Hi everyone
Can somebody please help me?
I am supposed to somehow obtain a table with data that must be "pulled" from 2 excel spreadsheets.
I will try to show what the 2 spreadsheets contain(i will mention only the columns i am interested in):
Spreadsheet 1
User Status
y pass
x fail
z fail
x pass
y fail
z fail
etc etc
Spreadsheet 2
User Job Company
x1 1a ABC
y1 1c DEF
y1 1b DEF
x1 3c ABC
z1 5g GHI
z1 8g GHI
I mention that the users in the first spreadsheet are the same users that can be seen in the second spreadsheet, but their names are recorded differently(added a number or a group of letters,etc).
My task is to get a table in which to show the compliance of each user in terms of numbers and in terms of quality of the work they are doing.
the compliance refering to the numbers of jobs can be obtained by making a ratio between the number of times each user appears in spreadsheet 1 with either pass or fail (that represents the number of jobs they have sent us,regardless if we have passed or failed them) and the number of jobs for each user in spreadhseet nr 2.
So taking as example user x1 (or x, it is the same user), he had 2 jobs and we passed/failled 2 jobs, so the compliance in terms of numbers of jobs is 100%.
But the compliance in terms of quality of jobs is 50%, because we only passed 1 job from the 2.
The new table must specify the company of each user also.
Is there a way I can create this new table using only Excel (and how would i do that)or i need to import the 2 spreadsheets in access (what macros/scripts would i need in this case)?
I dont know if I explained myself well enough, but im hoping sombody will be able to give me a suggestion.
Thank you very much!
Can somebody please help me?
I am supposed to somehow obtain a table with data that must be "pulled" from 2 excel spreadsheets.
I will try to show what the 2 spreadsheets contain(i will mention only the columns i am interested in):
Spreadsheet 1
User Status
y pass
x fail
z fail
x pass
y fail
z fail
etc etc
Spreadsheet 2
User Job Company
x1 1a ABC
y1 1c DEF
y1 1b DEF
x1 3c ABC
z1 5g GHI
z1 8g GHI
I mention that the users in the first spreadsheet are the same users that can be seen in the second spreadsheet, but their names are recorded differently(added a number or a group of letters,etc).
My task is to get a table in which to show the compliance of each user in terms of numbers and in terms of quality of the work they are doing.
the compliance refering to the numbers of jobs can be obtained by making a ratio between the number of times each user appears in spreadsheet 1 with either pass or fail (that represents the number of jobs they have sent us,regardless if we have passed or failed them) and the number of jobs for each user in spreadhseet nr 2.
So taking as example user x1 (or x, it is the same user), he had 2 jobs and we passed/failled 2 jobs, so the compliance in terms of numbers of jobs is 100%.
But the compliance in terms of quality of jobs is 50%, because we only passed 1 job from the 2.
The new table must specify the company of each user also.
Is there a way I can create this new table using only Excel (and how would i do that)or i need to import the 2 spreadsheets in access (what macros/scripts would i need in this case)?
I dont know if I explained myself well enough, but im hoping sombody will be able to give me a suggestion.
Thank you very much!
