Sampling Without Repetition

Hello,
I have a set of data which includes 10 samples each having 775 possible data points. I want to see what is shared between these samples when I go down to 9, 8, 7, 6... n samples. I am hoping that I can do this with Visual Basic, or other Excel functions where I could essentially determine the average of this similarity after calculating what is shared between all possible combinations given the sample total I am on. For example if I want to see what is shared across 9 out of the 10 samples, then I would need to determine the average of this shared-ness between all possible combinations of 9. I would upload a dataset but they are too large. Let's say in A175 I have an ID and in columns A2 through A11 are the values corresponding to that ID for each sample, I want to take all possible combinations of n samples for this value. I apologize if this doesn't provide enough detail or is vague, I am relatively new to this type of analysis and am trying to teach myself in a limited timeframe. Thank you in advance.

-Provoxt

My apologies for the vagueness.

I have a 775 x 11 table basically. Each row consists of an ID followed by specific values for each column header (sample). I am trying to determine how well values are shared between all of these samples. The way I have to do that is by looking at what is shared between all 10 samples, which I can easily do with =Countif(), and seeing what all comes up with a count of 10 in each row. It gets harder to do as I start getting rid of 1 sample to only look at 9, in which case I have to look at every combination of 9 samples (1-2-3-4-5-6-7-8-9/1-2-3-4-5-6-7-8-10, etc.) and then continue to do this with combinations of 8 samples, 7 samples, and so on. I have used Visual Basic before, but it was EXTREMELY introductory. I want to try and write a script that would assess all of the possible combinations without repeating any, total up each row and then either take the average, or output each total so I can average them myself. I hope this helps, again please accept my apologies for lack of knowledge/understanding.

-Provoxt

Maybe this will help.

X'ed cells indicate those to be included
Blacked cells indicate exclusion in combination

Sum would be sum for that ID at each combination

Average would hopefully be the average of all Sums

Again I apologize.

I have attached an excel sheet this time. I think I need a script to create each possible combination, since the number of them will increase as the sampling size decreases (with 8 as a sampling size the number of combinations becomes 45). I really appreciate the help with this, I hope it's becoming clearer what I am trying/needing to do. Please keep in mind that the attached sheet is only a tiny example of the overall actual table.

What I have done is illustrate what happens with a few different IDs. As you can see, in the top rows A occurs 10 times only once and so would be considered a shared value among these samples, but only for that one ID. When I look at 9 samples ID 2 becomes shared over the total number of samples assessed, however when we look at the actual sample combinations we see that it's only shared in one combination. This is what I am looking to write a script for, but instead of my manually arranging each sample to create a combination, I want to automate that, especially given that as I decrease the number of samples to assess the possible combinations increases quite a bit. What I don't know how to do is write the script to pick each of these combinations without repeating. So if there are possible combinations of S1-S10, I only want to look at each combination once. Hope that makes it clearer

I am really intrigued by your reply, could you explain how the functions actually work? Are there any good sources out there for understanding Excel functions?

I follow everything but the last line... and how do I take into account the exclusion of more cells as I decrease the sampling group?

As I decrease the sampling groups from 10 to 9 to 8 etc. how do I indicate that? Because that will lead to an increase in excluded cells and result in more combinations. Unless I am not understanding this enough and have overlooked what to do..