Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
matching data from 3 different sources
Where to start?!?!
I am trying to merge multiple data sheets into a single master report with all required data. One datasheet comes from a payroll system and the other two from a training system. The payroll has ex staff details, manager names,employment dates etc. I use vlookup against the other training system reports to work out whos no longer with the company and who they report to etc. Where it gets interesting is how to work with the two reports from the training system €“ the first report I draw with a total list of employees. The second report will only show those employees who have either registered or completed a training course €“ not those who have not yet launched/started a course. What I have been doing up to now is matching all the reports accurately BUT only by a single learning activity( an e-learning course staff members need to complete) at a time..i use vlookup to basically say if their name does not appear on the second report (they have not completed or started a course) then they have not attempted it yet. This is working per individual learning activity but business requirement is the exact same report with multiple training activities. So far thinking it through I have found the following challenges and thought of a possible solution €“ somehow including in the first report (with the list of employees) the names of the multiple training activities and then filtering and using vlookup against the second report that shows completed or started (and if I get an #N/A I change the status to not attempted) Would this work or am I barking up the wrong tree? Thanks for all the help from this forum €“ makes a huge difference!! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
matching data from 3 different sources
You should look at some of the VBA solutions under Programming. Sometimes it
is easier/better to complete tasks like this using worksheet formulas and other times it is better to use VBA code. If you have to perform the same manual operations (cut, pastes, copies) often and it takes time to do these operattions then it will eventual save you time by developing VBA code. VBA code is also better because it will repeatively perform the same operation constantly and without error. You did not state how you the new data is getting into the worksheets and how long it takes to perform the importing of the data so it is difficult to say if Wroksheet functions or VBA is the better choice. "vlookupabyss" wrote: Where to start?!?! I am trying to merge multiple data sheets into a single master report with all required data. One datasheet comes from a payroll system and the other two from a training system. The payroll has ex staff details, manager names,employment dates etc. I use vlookup against the other training system reports to work out whos no longer with the company and who they report to etc. Where it gets interesting is how to work with the two reports from the training system €“ the first report I draw with a total list of employees. The second report will only show those employees who have either registered or completed a training course €“ not those who have not yet launched/started a course. What I have been doing up to now is matching all the reports accurately BUT only by a single learning activity( an e-learning course staff members need to complete) at a time..i use vlookup to basically say if their name does not appear on the second report (they have not completed or started a course) then they have not attempted it yet. This is working per individual learning activity but business requirement is the exact same report with multiple training activities. So far thinking it through I have found the following challenges and thought of a possible solution €“ somehow including in the first report (with the list of employees) the names of the multiple training activities and then filtering and using vlookup against the second report that shows completed or started (and if I get an #N/A I change the status to not attempted) Would this work or am I barking up the wrong tree? Thanks for all the help from this forum €“ makes a huge difference!! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
matching data from 3 different sources
The data from payroll is imported from a payroll system - this doesnt take
too long The data from the training system is imported via ODBC from a database view/query this is very fast and doesnt take long.. "Joel" wrote: You should look at some of the VBA solutions under Programming. Sometimes it is easier/better to complete tasks like this using worksheet formulas and other times it is better to use VBA code. If you have to perform the same manual operations (cut, pastes, copies) often and it takes time to do these operattions then it will eventual save you time by developing VBA code. VBA code is also better because it will repeatively perform the same operation constantly and without error. You did not state how you the new data is getting into the worksheets and how long it takes to perform the importing of the data so it is difficult to say if Wroksheet functions or VBA is the better choice. "vlookupabyss" wrote: Where to start?!?! I am trying to merge multiple data sheets into a single master report with all required data. One datasheet comes from a payroll system and the other two from a training system. The payroll has ex staff details, manager names,employment dates etc. I use vlookup against the other training system reports to work out whos no longer with the company and who they report to etc. Where it gets interesting is how to work with the two reports from the training system €“ the first report I draw with a total list of employees. The second report will only show those employees who have either registered or completed a training course €“ not those who have not yet launched/started a course. What I have been doing up to now is matching all the reports accurately BUT only by a single learning activity( an e-learning course staff members need to complete) at a time..i use vlookup to basically say if their name does not appear on the second report (they have not completed or started a course) then they have not attempted it yet. This is working per individual learning activity but business requirement is the exact same report with multiple training activities. So far thinking it through I have found the following challenges and thought of a possible solution €“ somehow including in the first report (with the list of employees) the names of the multiple training activities and then filtering and using vlookup against the second report that shows completed or started (and if I get an #N/A I change the status to not attempted) Would this work or am I barking up the wrong tree? Thanks for all the help from this forum €“ makes a huge difference!! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
matching data from 3 different sources
If yoiu want to try a macro then one way to sdtart is to record a macro. You
can post the results of the macro on the website to refine it if necessary 1) Worksheet menu - Tools - Macro - Record new macro 2) Perform the operations you normally perform 3) stop macro - Tools - Macro - Stop Recording 4) Review the code - Tools - Macro - Visual Basic - Editor Worksheet formulas can be replaced with macro instructions including lookup. See other postings under programming to get examples of how to post you requirements. "vlookupabyss" wrote: The data from payroll is imported from a payroll system - this doesnt take too long The data from the training system is imported via ODBC from a database view/query this is very fast and doesnt take long.. "Joel" wrote: You should look at some of the VBA solutions under Programming. Sometimes it is easier/better to complete tasks like this using worksheet formulas and other times it is better to use VBA code. If you have to perform the same manual operations (cut, pastes, copies) often and it takes time to do these operattions then it will eventual save you time by developing VBA code. VBA code is also better because it will repeatively perform the same operation constantly and without error. You did not state how you the new data is getting into the worksheets and how long it takes to perform the importing of the data so it is difficult to say if Wroksheet functions or VBA is the better choice. "vlookupabyss" wrote: Where to start?!?! I am trying to merge multiple data sheets into a single master report with all required data. One datasheet comes from a payroll system and the other two from a training system. The payroll has ex staff details, manager names,employment dates etc. I use vlookup against the other training system reports to work out whos no longer with the company and who they report to etc. Where it gets interesting is how to work with the two reports from the training system €“ the first report I draw with a total list of employees. The second report will only show those employees who have either registered or completed a training course €“ not those who have not yet launched/started a course. What I have been doing up to now is matching all the reports accurately BUT only by a single learning activity( an e-learning course staff members need to complete) at a time..i use vlookup to basically say if their name does not appear on the second report (they have not completed or started a course) then they have not attempted it yet. This is working per individual learning activity but business requirement is the exact same report with multiple training activities. So far thinking it through I have found the following challenges and thought of a possible solution €“ somehow including in the first report (with the list of employees) the names of the multiple training activities and then filtering and using vlookup against the second report that shows completed or started (and if I get an #N/A I change the status to not attempted) Would this work or am I barking up the wrong tree? Thanks for all the help from this forum €“ makes a huge difference!! |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
matching data from 3 different sources
Whilst I agree that macros (VBA code) can be very useful, from my
understanding of your problem and in particular that you have ODBC already setup I would have thought that you could ideally have an ODBC sheet set up for all the employees you wish to report on and have simply added the various columns you require for the different training courses. In these different columns you can have the required formula to refer to other ODBC sheets. You could even use an IF formula so that if you would get an N/A value from your vlookup you can automatically show it as not attempted. There are other tricks available to save you time such as selecting from the Data menu, Import External Data, Data Range Properties and then making sure the tick box for "Fill Down Formulas for columns adjacent to the data" is ticked. Hope this makes sense and helps! "Joel" wrote: If yoiu want to try a macro then one way to sdtart is to record a macro. You can post the results of the macro on the website to refine it if necessary 1) Worksheet menu - Tools - Macro - Record new macro 2) Perform the operations you normally perform 3) stop macro - Tools - Macro - Stop Recording 4) Review the code - Tools - Macro - Visual Basic - Editor Worksheet formulas can be replaced with macro instructions including lookup. See other postings under programming to get examples of how to post you requirements. "vlookupabyss" wrote: The data from payroll is imported from a payroll system - this doesnt take too long The data from the training system is imported via ODBC from a database view/query this is very fast and doesnt take long.. "Joel" wrote: You should look at some of the VBA solutions under Programming. Sometimes it is easier/better to complete tasks like this using worksheet formulas and other times it is better to use VBA code. If you have to perform the same manual operations (cut, pastes, copies) often and it takes time to do these operattions then it will eventual save you time by developing VBA code. VBA code is also better because it will repeatively perform the same operation constantly and without error. You did not state how you the new data is getting into the worksheets and how long it takes to perform the importing of the data so it is difficult to say if Wroksheet functions or VBA is the better choice. "vlookupabyss" wrote: Where to start?!?! I am trying to merge multiple data sheets into a single master report with all required data. One datasheet comes from a payroll system and the other two from a training system. The payroll has ex staff details, manager names,employment dates etc. I use vlookup against the other training system reports to work out whos no longer with the company and who they report to etc. Where it gets interesting is how to work with the two reports from the training system €“ the first report I draw with a total list of employees. The second report will only show those employees who have either registered or completed a training course €“ not those who have not yet launched/started a course. What I have been doing up to now is matching all the reports accurately BUT only by a single learning activity( an e-learning course staff members need to complete) at a time..i use vlookup to basically say if their name does not appear on the second report (they have not completed or started a course) then they have not attempted it yet. This is working per individual learning activity but business requirement is the exact same report with multiple training activities. So far thinking it through I have found the following challenges and thought of a possible solution €“ somehow including in the first report (with the list of employees) the names of the multiple training activities and then filtering and using vlookup against the second report that shows completed or started (and if I get an #N/A I change the status to not attempted) Would this work or am I barking up the wrong tree? Thanks for all the help from this forum €“ makes a huge difference!! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Matching identical data using data only once in the matching proce | Excel Discussion (Misc queries) | |||
Sources Used in Data Validation | Excel Worksheet Functions | |||
I NEED TO TAKE TWO SOURCES OF DATA AND SEE WHAT IS MISSING OUT OF. | Excel Discussion (Misc queries) | |||
Matching and aranging data form two sources | Excel Worksheet Functions | |||
Matching data and linking it to the matching cell | Links and Linking in Excel |