Counting unique text sequences after applying 2 criteria

Hi there... and welcome to the Excel Forum.

Please attach a sample workbook. Make sure there is enough data to demonstrate your need. Make sure your desired results are shown, mock them up manually if necessary. Remember to remove ALL confidential information first!!!

Click on GO ADVANCED and use the paperclip icon to open the upload window.

The paperclip icon

Hi.

Array formula**:

=SUM(IF(FREQUENCY(IF($E$2:$E$19152=AH$51,IF(F$2:F$19152=1,MATCH($E$2:$E$19152&$C$2:$C$19152,$E$2:$E$19152&$C$2:$C$19152,0))),ROW($C$2:$C$19152)-MIN(ROW($C$2:$C$19152))+1),1))

though this will be significantly quicker to calculate if you use an additional column within your worksheet to concatenate the values in columns C and E. Performing those concatenations within the actual formula costs a lot of resource.

For example, if you put, in G2:

=E2&C2

and copy down to G19152, the above becomes:

=SUM(IF(FREQUENCY(IF($E$2:$E$19152=AH$51,IF(F$2:F$19152=1,MATCH($G$2:$G$19152,$G$2:$G$19152,0))),ROW($C$2:$C$19152)-MIN(ROW($C$2:$C$19152))+1),1))

I'm afraid you can't improve on this set-up in terms of efficiency. A full recalculation using some dummy data took about 3 seconds in my workbook.

Regards


**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

Edit: that time was for the first formula.

The version using the newly-concatenated values in column G took only 1 second.

Regards

Sure. But I'm not quite sure what you mean.

Could you post a vastly-reduced workbook example together with your expected result(s)?

Regards

Some sample data is attached. As you will see the text sequences in red are recognised by more than one group id so are not 'unique', other sequences in black are only present in one group so can be considered unique to that group. I need to calculate how many sequences are unique to each group id in my list. As with the calculation before this should not include duplicates. I couldn't see how this could be done so if you know of a way that would be great!!!

Thanks for your help

I'm not clear about the rsult you want. Is it:

For each accession number, list unique text values; but only if there's a 1 in the corresponding cell in column C???? or what????

I also am unclear as to what you require here.

Perhaps you could clarify by giving a few expected results?

Regards

Sorry maybe I should have included a larger sample of data. The sequences of text are fragments of protein sequences and the accession numbers identify the protein sequence it belongs to. Some protein sequences belonging to the same or different isoforms of the same protein may have a similar sequence so the text sequences or fragments might be observed in more that one accession.
For some accession numbers I have around 300 unique sequences (as calculated using the previous formula) and I want to know how many of those sequences only appear under that accession. Some sequences appear under multiple accession numbers and are therefore shared. These sequences not suitable as identifiers of the presence of an accession number in a data set and need to be excluded.I hope that makes more sense? So I expect to see something like:

For E5G076
578 total fragments for this accession
266 fragments, after removing the duplicates, for this accession
3 unique fragments only seen in this accession

And the presence of a 1 in column C indicates the sequence was found in the data set.

Nope. E5G5076 isn't on your posted sheet; so the explanation doesn't help...

What's the data in columns C to AC for?

Where are the results for these unique counts to go? Have you set-up a table ready to house them?

Regards

I can't see that additional table. And I'm still not sure how that data in columns C to AC is to be considered.

Regards

Originally Posted by Rebekah84

Column C:AC can be thought of as independent data acquisitions in which I am looking for the same sequences and recording if they are present with a 1 or if they are not present 0.

I have no idea what that means - sorry. And unfortunately you have not included any expected results in your new sheet, so it is difficult to know what is being aimed for.

Regards