Find the most frequently used set of values

[alexisL]

I have a spreadsheet with more than 200 columns, and each column has different values with different sizes.

I'm trying to find some sets of values and here are my questions;
1) How can I find the most frequently used set with size 3? (eg. CD8, CD3, CD4 or CD8, FoxP3, PanCK)
2) After I find the answer to question 1, I want to find a larger size of sets with it.
(eg. Size 4: CD8, CD3, CD4, FoxP3 or CD8, FoxP3, PanCK, CD4
Size 5: CD8, CD3, CD4, FoxP3, PanCK or CD8, FoxP3, PanCK, CD20, Cd68)

Panel	AB
1	CD20, CD4, CD68, CD8, FoxP3, PanCK
2	Cytokeratin 10, Cytokeratin 14, FISH-Ccl2, FISH-Mertk, Involucrin, Ki67, Loricrin
3	CD11b, CD14, CD15, HLA-DR, panCK, pPERK
4	CD4, CD8, PD-1, PD-L1
5	E-cadherin, Vimentin
6	AhR, CCR6, CD4
7	Cytokeratin 20, E-cadherin, EpCAM, GFP, Ki67, OLFM4
8	Cytokeratin 10, Cytokeratin 14, Involucrin, Ki67, Loricrin
9	CD142, CD68, EBOV , HepPar1, MPO
10	CX3CR1, FOXJ1, SCGB1A1
11	CD3, CD4, CD68, CD8, FoxP3, PanCK
12	CD15, CD20, CD3, CD4, CD68, CD8
13	CD20, CD4, CD68/163, CD8, FoxP3, PanCK
14	ER, Cleaved Caspase 3, Glucocorticoid Receptor, Ki67, p53, PR
15	CD19, CD3, CD56, CD8, FoxP3, PanCK
16	CD3, CD8, FoxP3, PD-1, Tbet , Tim3
17	CD11c, CD14, CD163, CD33, CD68, PD-L1
18	CD206, 0, 0
19	CD127, CD15, CD163, CD1a, CD3, CD33, CD4, CD79a, CD8, IL-17, Myeloperoxidase, S100, Tryptase
20	CD34, NRP1, VEGFR2

Right now, I tried with COUNTIF after I had converted those values to a text with a comma in one cell. I can get a result with only one criteria, and I'm stuck.
COUNTIF(E$3:E$202, "*, CD8, *") + COUNTIF(E$3:E$202, "CD8") + COUNTIF(E$3:E$202, "CD8, *") + COUNTIF(E$3:E$202, "*, CD8")

Any advice??

 

[mabbutt]

I think you need to explain this in a different way as I cannot work out what you are trying to achieve and have absolutely no idea what or where 'size' is referenced.

 

[alexisL]

I think you need to explain this in a different way as I cannot work out what you are trying to achieve and have absolutely no idea what or where 'size' is referenced.

I'm sorry about my terrible explanation.
I have a table with columns that refer to a panel, and each column has a different number of values. The size I mentioned is how many values each column has, and the values are not sorted.

Basically, I want to find patterns from the table. Some columns have the same values but in a different order, some columns have sub-set only, and many of them even don't have the same values.
I'm trying to find;
1) How to find the most frequently used set with 3 values? (eg. CD8, CD3, CD4 or CD8, FoxP3, PanCK)
2) How to find the most frequently used set with 4, 5, 6 values based on the first answer.
(eg. set with 4 values: CD8, CD3, CD4, FoxP3 or CD8, FoxP3, PanCK, CD4
set with 5 values: CD8, CD3, CD4, FoxP3, PanCK or CD8, FoxP3, PanCK, CD20, Cd68)

Hopefully, I rephrased my questions better..
Thanks!

 

[mabbutt]

That is a little clearer but still very confusing.

My advice would be to provide a basic working example in a worksheet showing the data and the desired outcome without worrying about the formulas.

 

[alexisL]

That is a little clearer but still very confusing.

My advice would be to provide a basic working example in a worksheet showing the data and the desired outcome without worrying about the formulas.

Thank you for your advice. I will rewrite with a worksheet!