snijesh / anuja Goto Github PK

Step1: In the column of F2 the data from A and C was concatenated (merged with a seprator ":")

Formula
=CONCATENATE(A3,":",C3) Double click the cell to repeat across F column

Copy Paste (as values) the data of F column to H Column:

Step2: Next, select the data from H column and remove the duplicates so that the genes repeated in the same sample will be removed

In excel Remove Duplicates is available under Data


Now the number of values in the column has reduced to 18 from 21

Now Your data looks as follows:


Step3: Gene names merged to the samples were removed by using REPLACE method.

In excel press Ctrl+F go to Replace tab and enter :* so that values after the symbol : will be removed

Take a copy of H column and put in J column:

Remove Duplicates from the J Column:

Step3: Use COUNTIF formula to get the frequency of genes in a given range
Formula : =COUNTIF(H2:H22,J2:J15) #You can change the range accordingly