Find the most frequently used set of values

alexisL

New Member
Joined
Feb 9, 2021
Messages
3
Office Version
  1. 2013
Platform
  1. Windows
  2. MacOS
  3. Web
I have a spreadsheet with more than 200 columns, and each column has different values with different sizes.

1612915786034.png


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)

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

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
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.
 
Upvote 0
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!
 
Upvote 0
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.
 
Upvote 0
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!
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,114,002
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top