Folyamatban

Excel macro: analyzing a pivot table

Please develop 2 similar macros for analyzing a pivot table.

The table is as follows:

There are various classes of drugs (see the numbers: 3, 4, 5, ...). Inside each class, there are drug names (e.g. Accutane, Volmax, Novantrone, each in bold letters). And there are companies selling these drugs (e.g. other, Abbott, Merck).

Columns indicate if the drugs where marketed in the years 2002-2010, as shown by values of 1, 2 or 3 for each drug and each company.

What I actually want to analyze is how many entries of new companies (not the same company!) took place per drug class and 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 and year (if there is only one new entry, then 1, if two entries, then 2, etc.).

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.

Let's take class 14 as an example. There are 3 drugs marketed by three different companies. Privinil from Merck entered the class in 2003. So there should be one entry for "other" (as "other" occurs twice, it is "one company", to there should be only one entry - so please check the companies inside a class for dublettes...)

In class 15: no new entry.

In class 96, "other" is confronted with an entry in 2003 and 2010.

In class 117: no new entry.

In class 135: entry for "other" in 2003

In class 143: entry for "other" in 2010.

In class 306, Wyeth received ONE entry in 2005. Why one instead of two? The drug Ortho-Tri-Cyclen was not on the market in 2004. It seems that this is an error as the drug was on the market before! Look at class 143: Here, the break on the market was much longer, therefore, the relaunch in 2010 in class 143 was considered to be a new intro. So, please do not consider breaks of only one year as new entries.

As far as I see, these examples are all the company entries in the dataset (in the actual dataset, there are much, much more).

I would like to have a table with the results for each class next to the pivot table, giving the companies that face entry and the frequency of the entry.

The results may look like as in the example. They may also look differently (e.g. starting directly in the line where the new class starts) if this is faster for you (and cheaper for me).

I want to do an extra analysis considering only entries of so-called NCEs. This may be an extra, but very similar macro. Note that in the sample file, only one drug has NCE status = 1.

There are at least two possibilities for providing the results. First, you could provide a further line for NCE (after class > drug name > company, and then > NCE) and look where the 1 is.

Or you could use NCE instead of the counter for filling values into the table. Then every value <> 0 is an entry, with the 0s indicating that there are drugs in the corresponding years. Maybe the latter approach is easier to implement as there may be more synergies in the macro in comparison to the first.

Note:

My actual table is much larger (and, depending on the class (I have more than one type of class, i.e. not only numbers), varies in table size (lines)). So I would like to be able to define the pivot table to be analyzed via giving the cell range, and eventually give the area for the results.

As I have several other definitions of classes (not all are numbers), it may be possible to recognize classes in the macro via the formatting of the cells (as may be useful for detecting drugs and companies).

I also don't need a user interface. I am fine with the plain code and some comments how to adjust cell ranges, etc.

In case of doubt, drop me a line, please.

Only freelancers will be considered that give an estimate how much time they will need for completing the project.

Készségek: Excel, Visual Basic

Bővebben: excel dublettes, user interface companies, starting with visual basic, need interface freelancers, market place freelancers, market place for freelancers, macro freelancers, how to market to freelancers, how to be a freelancers, how many freelancers are there, freelancers in my area, freelancers formatting, freelancers excel, excel macro how to, companies in need of freelancers, code for me freelancers, a place for freelancers, abbott, what is macro in excel, define user interface

A munkaadóról:
( 65 értékelés ) Leipzig, Germany

Projektazonosító: #5345419

9 szabadúszó tett átlagosan $13/óra árajánlatot erre a munkára

dpune

Hi, I have more than 14 years of exp and I am expert in this kind of work. I have completed more than 200 projects. Please look at the feedback left by my employer to know more about my work. Waiting for your positive Több

$7 USD / óra
(27 vélemény)
5.0
funnyhoney

Prince 2 certified IT Project Manager, MBA with experience in website admin, website management, testing, Quality control etc. Excerpts from previous test reports on request.

$15 USD / óra
(5 vélemény)
4.4
AndroidAll

Dear Sir, I'm interesting in your job. I have much experience in the MS Excel and VBA. I can do this job. Regards.

$10 USD / óra
(7 vélemény)
3.7
paris2785

VB, VBA and Databases expert for over a decade. Master in Information Systems. I have delivered similar projects in the past. Please check https://www.freelancer.gr/projects/Data-Processing-Excel/data-translation-e Több

$10 USD / óra
(5 vélemény)
3.6
XFastSolution

Dear sir, I"ve read you requirement, also check the attached carefully. With expertise in Excel, VBA, Statistics, I would be a good fit. Please see works done in my profile. My bid is $30 is for Totally completed wo Több

$30 USD / óra
(3 vélemény)
1.9
codetod

Dear CS01 I just thought of sending you an alternate solution to the problem. The data volume is large, requirement is bit complex and you will need more statistical reports in future, so why dont you think of doing Több

$8 USD / óra
(3 vélemény)
1.7
amigosu

Hi I am work on one of the biggest Brazilian banks, using Excel all the time, so I can help you with your task. Please feel free to check my profile and message me if you want.

$25 USD / óra
(2 vélemény)
0.4
hfjaa

stop faking people please stop faking people pleasestop faking people pleasestop faking people pleasestop faking people pleasestop faking people pleasestop faking people pleasestop faking people pleasestop faking peopl Több

$5 USD / óra
(0 vélemény)
0.0
rahulyen02

I have vast experience in developing VBA code. I am confident that I can provide solution to you, the way you want it as soon as possible.

$5 USD / óra
(0 vélemény)
0.0