matching data from 3 different sources

[vlookupabyss]

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!!

 

[Joel]

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]

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]

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.

 

[Derek]

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!