• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User


  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

I want to combine multiple rows. I'm making a database, I'm working with drugs

Hello!! I'm making a database where I want to group several drugs, each interacting with specific proteins (these are in the first column) and the drugs are in the titles in the first row.

What I want to do is to leave only one of each type in the protein zone. and on the right, all the drugs that are related to this protein.

I've already done a little bit of the manual work. But it's 1991 values that will take me hours

please if you have any recomendation I'm all ears


  • Ansiedad y depresion in older people (1).xlsx
    595.7 KB · Views: 4
In the attached, two new Pivot tables:
  1. At cell H4 of the Suggestion1 sheet where there's 1 line for each Target, and a list of the drugs in one cell for each target.
  2. At cell K4 of the same sheet a more traditional pivot where the Targets are listed on the left and the unique (actually distinct) drugs are listed in separate cells to the right.
Is this the sort of thing you're looking for?


  • Chandoo55911Ansiedad y depresion in older people (1).xlsx
    769.3 KB · Views: 5
That's what I needed, the next step is to make a percentage interaction, which would be, the number of drugs among the total drugs.

muchas muchas gracias.

If you could tell me how it was done
1. Which pivot did you prefer?
2. Could you attach my file with a few percentages calculated manually so that I know exactly what you want?
3. How was it done? With Power Query - details later.
i think is a barrier language, but i will do de best to explain myself
in the next column, by the tabkle i have a simple formula that i want to replicate in every row
is a division, the number of drugs that interact with a target divided in the total drugs
the first is ZAP60=2/30 = 0.0016 X 100
See the pivots I mentioned before, each with an added column Distinct Count of Drug.
This requires both pivots' data to be added to the data model (allows distinct count).


  • Chandoo55911Ansiedad y depresion in older people (1).xlsx
    774.3 KB · Views: 3