0

I am looking at formatting a report which has been automatically generated by a 3rd party system. As we have no access to the Database directly i would like to build a Macro which would format the report into a more readable format.

I had initially thought about ingesting this raw data into a database as i am pretty competent a writing the SQL queries, however i think it would be easier if someone could run this through a macro.

The initial report shows which users have attempted which training modules and their completion status.

N.b. A user may have completed a module several times, therefore will appear multiple times.

The link below is the spreadsheet with two sheets, sheet 1 is the raw data and sheet 2 is how i would like things to appear.

https://www.dropbox.com/s/p1hipx17q3mf3dm/Learning-Report.xlsx

Any help / ideas would be much appreciated as i am pretty new to the whole macro's in excel thing.

Many thanks

Ian

Community
  • 1
  • 1
revolution14
  • 43
  • 1
  • 7
  • If you look at the FAQ , you will see this more of a "help you were you are stuck" Q&A forum vs a "we'll build your code for you" forum. That said, give this a shot by using a macro recorder and reading about `For Each Next` and `If End If` statements with VBA. When you get to the point where you get stuck, post the code you've tried to work out and let us know what is eating you. Then we can pitch in and get it going. – Scott Holtzman Aug 15 '12 at 15:50
  • Hi Scott, thanks for your help that's no problem i am trying to work on it myself now. Does anyone know how i can write the equivalent 'SELECT highest_score FROM results WHERE user_id ='23093' AND module_id='GDQ1001' ORDER by highest_score DESC. I am currently looking at a Vlookup however im not sure this is the correct formula – revolution14 Aug 15 '12 at 15:58
  • vlookup may be good in general, but not for finding a max value, since a vlookup is a lookup for a specific value against an index. You can try a `Max(If(` array formula. See this link for help with that: – Scott Holtzman Aug 15 '12 at 16:15
  • Is ADO a possibility? This would let you use SQL to query the data - more details in this earlier answer to a similar question http://stackoverflow.com/a/7863452/53614 – barrowc Aug 15 '12 at 21:30

1 Answers1

1

I am answering in the same manner the question is phrased so please bear with me...

  1. Identify all unique employees (for rows)
  2. Identify all unique courses (for columns)
  3. Find all the attempts and compute a. Highest Score b. Status
  4. Put the data in the second sheet.

See if the question has been precise... the answer would also be to the point.

Bharat Sinha
  • 13,973
  • 6
  • 39
  • 63
  • Sorry Bharat i guess i could have worded it better. Does anyone know how i can write the equivalent in a formulae SELECT highest_score FROM results WHERE user_id ='23093' AND module_id='GDQ1001' ORDER by highest_score DESC Thanks – revolution14 Aug 15 '12 at 16:09
  • Now that's a good question. One hint... try searching for VLOOKUP and see if this solves it for you. Post it as another question and see if someone helps! – Bharat Sinha Aug 15 '12 at 16:12