Evening Excel geniuses,
I’ve hit a brick wall, I wonder if you can help.
I’m trying to analyse some data based on Cancer type. Such that if a certain Cancer type is selected in Cell A1 (‘Patient demographics’ worksheet), I want to filter my data in Sheet 2 (column B for example – which has a list of genes); based on the subset of genes being analysed for that cancer type listed in sheet 3 (e.g. in sheet 3 – column A are the colorectal genes, B = Breast Cancer, etc). I.e. selected cancer type in sheet 1, dataset in sheet 2, being sorted/filtered based on gene lists decided in sheet 3. I tried writing a macro but failed so I thought I’d see what the formula would look like (logically) in Excel and then I can try to convert it to VBA. Below is the formula in Excel (a combination of IFs, that should work logically but there are too many arguments – I can’t use more than 1 IF function without getting an error). The idea was to match those that correspond to the right gene, and then filter by that column and copy paste into a new worksheet within the workbook – but I’ve stumbled at the 1st hurdle. Can anyone out there make sense of my shortcomings please? Or have you a simpler way of achieving this please? In VBA if possible please.
I’ve hit a brick wall, I wonder if you can help.
I’m trying to analyse some data based on Cancer type. Such that if a certain Cancer type is selected in Cell A1 (‘Patient demographics’ worksheet), I want to filter my data in Sheet 2 (column B for example – which has a list of genes); based on the subset of genes being analysed for that cancer type listed in sheet 3 (e.g. in sheet 3 – column A are the colorectal genes, B = Breast Cancer, etc). I.e. selected cancer type in sheet 1, dataset in sheet 2, being sorted/filtered based on gene lists decided in sheet 3. I tried writing a macro but failed so I thought I’d see what the formula would look like (logically) in Excel and then I can try to convert it to VBA. Below is the formula in Excel (a combination of IFs, that should work logically but there are too many arguments – I can’t use more than 1 IF function without getting an error). The idea was to match those that correspond to the right gene, and then filter by that column and copy paste into a new worksheet within the workbook – but I’ve stumbled at the 1st hurdle. Can anyone out there make sense of my shortcomings please? Or have you a simpler way of achieving this please? In VBA if possible please.
Code:
- if you can call it that
[I]=IF('Patient demographics'!$F$2="Colorectal",ISNUMBER(MATCH(B:B, 'PanCancer Panels'!A:A,0)),"False",if('Patient demographics'!$F$2="Breast",ISNUMBER(MATCH(B:B, 'PanCancer Panels'!B:B,0)),"False", if('Patient demographics'!$F$2="GIST",ISNUMBER(MATCH(B:B, 'PanCancer Panels'!C:C,0)),"False",if('Patient demographics'!$F$2="Glioma",ISNUMBER(MATCH(B:B, 'PanCancer Panels'!D:D,0)),"False",if('Patient demographics'!$F$2="HN",ISNUMBER(MATCH(B:B, 'PanCancer Panels'!E:E,0)),"False",if('Patient demographics'!$F$2="Lung",ISNUMBER(MATCH(B:B, 'PanCancer Panels'!F:F,0)),"False",if('Patient demographics'!$F$2="Melanoma",ISNUMBER(MATCH(B:B, 'PanCancer Panels'!G:G,0)),"False",if('Patient demographics'!$F$2="Ovarian",ISNUMBER(MATCH(B:B, 'PanCancer Panels'!H:H,0)),"False",if('Patient demographics'!$F$2="Prostate",ISNUMBER(MATCH(B:B, 'PanCancer Panels'!I:I,0)),"False",if('Patient demographics'!$F$2="Thyroid",ISNUMBER(MATCH(B:B, 'PanCancer Panels'!J:J,0)),"False"))[/I]
[End code]
Thank you.
