how to select only matching columns in excel

[genetist]

Hi to all
Good Afternoon
I have example data like this, but in original data i have columns more 1200
ID SNP1 SNP2 SNP3 SNP4 SNP5 SNP6 SNP7 SNP8
1 A/A T/T T/G A/G MISSING T/T MISSING T/T
2 A/A A/A T/G G/G G/G MISSING MISSING T/G

now i would like to select ONLY columns containing any combinations of these A/T,A/G,A/C,T/A,T/G,T/C,G/A,G/T,/G/C,C/A,C/T,C/G, MISSING either in ID 1 or ID2 i.e. to select below columns only
SNP1
SNP3
SNP4
SNP5
SNP6
SNP7
SNP8
can any one help me with formula to find solution to my problem, any help in this regard will be highly appreciated
Thanks in advance
Regards
Genetist

[Glenn Kennedy]

Explain why SNP1 should be extracted?

What is your CURRENT Excel version?

[Glenn Kennedy]

No reply...

Simplest approach is using a helper row (here row 6):
=SUM(MMULT(IFERROR((SEARCH(TRANSPOSE($N$1:$N$12),B4:B5)>0)+0,0),(ROW($N$1:$N$12)>0)+0))

and then, in A9, copied down:
=IFERROR(INDEX($3:$3,AGGREGATE(15,6,COLUMN($B3:$I3)/($B$6:$I$6>0),ROWS(A$9:A9))),"")

You seem to be using an older version of Excel than me. So, please refer to the attached file. If the HELPER ROW formulae are enclosed within a pair of { }, these are array formulae.

These are a little different from ordinary formulae in that they MUST be confirmed in the FIRST CELL ONLY by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. After that, the array can be dragged down as normal, to cover the desired range.

You will know the array is active when you see the curly brackets { } appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly brackets yourself - it won't work...

[genetist]

Dear Glenn
Good Afternoon
Thanks for your help. I need to extract SNP1 and other matching snps to use in my experiment.

[Glenn Kennedy]

That is NOT what I asked. How does it meet your criteria?

[genetist]

I would like to select columns which are having these A/T A/G A/C T/A T/G T/C G/A G/T G/C C/A C/G Missing either in ID1 or ID2 and at the same i want to select columns having same text like A/A in ID1 and A/A in ID2 and rest of the columns i will ignore.
I hope i explained well
Thanks for your help

[Glenn Kennedy]

So... that is a new requirement. It was not mentioned in your original post. Is that correct?

[AliGW]

Why is column 1 included???

[Glenn Kennedy]

No reply...

change the helper to:

=SUM(MMULT(IFERROR((SEARCH(TRANSPOSE($N$1:$N$12),B4:B5)>0)+0,0),(ROW($N$1:$N$12)>0)+0))+IF(SUM(--EXACT(B4:B5,B4))>1,1,0)

[genetist]

Glenn
Its not new requirement but it is to select needed columns in another approach but in opposite way to my original request but both will give same solution.

[Glenn Kennedy]

See my last post, which has a possible solution. However, it was NOT mentioned in the first post: A/A (or entire column matching) was not mentioned anywhere.

[genetist]

Dear Ali
I would like to select columns which are having these A/T, A/G, A/C, T/A, T/G, T/C, G/A, G/T, G/C, C/A, C/G, Missing either in ID1 or ID2 and at the same i want to select columns having same text like A/A in ID1 and A/A in ID2 and rest of the columns i will ignore from my final solution
Thanking you

[genetist]

Dear Glenn
Good Afternoon
Thanks for your help and time to provide solution to me. Apology for not explaining things clearly. Now your solution is working perfectly as expected

[Glenn Kennedy]

No problem. You're welcome!!