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

[Alejandro Giovanni Otero]

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

 

[p45cal]

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?

 

[Alejandro Giovanni Otero]

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

 

[p45cal]

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.

 

[Alejandro Giovanni Otero]

the first pivot was exactly what i want
and in addition with the second i can work

if you can say me what you do, and how i can get a mix of these two

muchas graciass

 

[p45cal]

Some videos for you to look at.
1. Plain pivot table:

Video_2024-01-22_163010.wmv | Powered by Box

app.box.com

2. Pivot table using a Data Model added measure. Much the same as the previous video but a little different at the end:

Video_2024-01-22_163356.wmv | Powered by Box

app.box.com

These are .wmv video files. You may have to download them if they don't play straightaway.

I still don't know what you want regarding 'percentage interaction'.

 

[Alejandro Giovanni Otero]

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

 

[p45cal]

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).