excel macros: analyse data from a table -- 3

Overall, I need 3 macros. Two are closely related and more complex than no. 3.

The attached excel file has four sheets:

No. 1 is exemplary for the data I want to analyze with the FIRST macro. There are variants of this table, see below

No. 2 represents an extreme case of data that should be covered by the FIRST macro

No. 3 explains what shall happen in the THIRD macro

No. 4 is for the SECOND macro building closely on the first one

The table in the first sheet is as follows:

There are various classes of drugs (see numbers: 2, 3, 4, 5, ...). Inside each class, drug names (e.g. Rifapentin, Rifaximine, ... bold letters) + companies selling these drugs (e.g. ‘other’, Salix, Valeant, ...). Columns indicate if the drugs where marketed in years 2002-2010, as shown by values of 1, 2 or 3 for each drug + each company.

I want to analyze how many entries of new companies took place per drug class + year (for the years 2003-2010; 2002 is a control year to see if a new entry occurs in 2003) from the perspective of the companies being already present in the drug class. A company that is confronted with a new entry shall be listed, with the numbers of entry per drug class + year (if there is only one new entry, then 1, if two new entries, then 2, etc.). If the company introduces a drug itself, then this self-introduction does not count.

Note that one drug may have been marketed by more than one company, therefore it is necessary to look at the bold numbers in the table to see if entries took place. The attached file gives you an example ('fictive sample' sheet).

Further notes:

- The actual datasets are much larger

- The drug classes also have other names, sometimes text or numbers

- A pivot table is created based on language settings of the Excel versions used (I use a German Excel, but the macro should work universally)

- Brand lines (bold characters) are important to assess if an entry occurred (which happens if it goes from an empty cell to a value (0, 1, 2, 3, ...)

- Values in the table may be >1 (see e.g. D24). These do not change the results

- The company lines are only important to assess for which company an entry occurred

- Regarding the extreme case (‘fictive sample’ sheet):

For drug 5, we see an increase of 1 in year 2006. If you look at the cells in the cell range belonging to drug 5 (H20:H24), you see three new entries: 3>1 (usually, it is =1, see e.g. drug 3: D10 => D11: +1), and you cannot say who actually entered the class in lines 21-23. I will have to look that up in the raw data, but I need a hint that I need to do this. Therefore, I would like to be notified in the data with the text "manual check” as in lines 21:24.

I want to do an extra analysis (SECOND macro, see sheet ‘Pivot classID brand NCE’) considering only entries of so-called NCEs. There, values of 0 indicate that non-NCE drugs were marketed in the years, and values > 0 indicate that the drugs were NCEs.

The macro from the 3rd sheet is easy to implement. The 1st column defines the cell range to compare in the first step - as long as the values are identical, one needs to search for duplicates in the 2nd column). Then for the duplicates from the 2nd column in the defined cell range, one has to compare the values in the following columns and take the minimum of two values.

The results are free of duplicate values from the 2nd column within the cell range defined via the 1st column.

Final notes: - I don't need any graphical user interfaces, I only need code with a brief docu how to adapt which variable, e.g. how to adjust cell ranges in case the tables have a slightly different structure (more or less columns)

- Roughly estimated the time for completing the macros (4-6 hs)

- Please give me also an estimate of the time for programming the macros. I need the results by this Friday (March 21)

- I do not work with milestones, I need invoices and would pay the 1st invoice after having successfully testing the 1st macro

- If you have any questions, drop me a line

Excel Visual Basic

Project ID: #5566726

About the project