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

Skills: Excel, Visual Basic

See more: what is manual data entry, what is extreme programming, what is data structure in programming, what is data structure in c, what is data structure, what is data in data structure, what is a variable in programming, what is a programming language, what is a data structure in c, what is a data structure, what is a class in programming, what does data entry do, what data structure, what are data structure, what are classes in programming, visual basic programming notes, visual basic 6.0 programming language, use of data structure in programming, use of data structure, tables in data structure, table data structure, self programming language, search data structure, questions of data structure, programming what is a class

Project ID: #5566726

Awarded to:


Hey There, Thanks for posting the project. Its looks very much feasible and am Interested to do it. Next steps: Lets discuss/validate the complete requirements to be on the same page and I can start to get More

$10 USD / hour
(8 Reviews)

8 freelancers are bidding on average $10/hour for this job


Sir, I am well versed in this kind of jobs and can do your project as per requirement. Refer my online work portfolio at http://bit.ly/Lta2EN Looking for further discussions in this matter. with than More

$12 USD / hour
(32 Reviews)

Dear sir, I'm interesting in your job. I have much experience in the MS Excel and VBA. I can do this job. If you want, I can develop a demo within a short time. Regards. PS. In order to show my ability, I c More

$12 USD / hour
(9 Reviews)

Hi, to ensure you get the best product could you please clarify. If the pivot has already provided you with necessary summary, why do you want it to be in a macro. I am sure I missed something in your description, so w More

$8 USD / hour
(4 Reviews)

I am Data Entry, MS Word and MS Excel Expert. i am very much professional in this work i am pretty sure that you cant find a best person for this job like me so i am ready to work on your project with low rate and high More

$5 USD / hour
(10 Reviews)

Hi Let me know if my proposition is acceptable for you. I will start doing 1-st macro after your answer. 20 hours x 10 USD per hour = 200 USD.

$10 USD / hour
(2 Reviews)

A proposal has not yet been provided

$8 USD / hour
(5 Reviews)

HI, I have done similar projects in the past. I will like to use my experience to serve you better. You can trust my sincere indulgence both in quality and time frame. If you will give me a chance, I will ensure that y More

$8 USD / hour
(2 Reviews)

A proposal has not yet been provided

$11 USD / hour
(0 Reviews)