Matching one row with another row of same column iteratively and getting sum at bottom of the column?

[genetist]

Dear All
good morning

I have data like this
IND M1 M2 M3 M4 M5
1 A/A G/G C/C T/T T/T
2 T/T C/C C/C G/G G/G
3 C/C T/T C/C C/C C/C
4 G/G T/T T/T G/C A/A
5 G/G T/T G/G C/T A/A


Step1: First i would like to give zero if any M column (2nd Row) from M1 to M5 contains different letters like A/T,A/G,A/C,T/A,T/G,T/C,G/A,G/T,G/C,C/A,C/T,C/G
Step3: MATCHING SINGLE ROW WITH ALL THE ROWS AND LIKE THIS ALL THE ROWS WITH REMAINING ROWS for example Matching B3 with B4,B5,B6,B7 and then matching B4 with B3,B5,B6,B7 and I will do same thing with B5,6 and B7 if matches then 0 otherwise 1 and finally I want to sum all the non matches i.e.1's. Now i would like to repeat same rocedure to columns C,D,E,F
Step3: i would like to get sum of all the one's at bottom of each column from B to F


Here i am sharing example for better understanding of my issue.


https://www.dropbox.com/s/iq7m5a2e4e...0PIC.xlsx?dl=0


Here i tried normal if conditions and i can match one row with other and it is taking lot of time but i want to compare all at one time may be through macro or very good formula. I am requesting you all to help and any help in this regard is highly appreciable.
Thanks in advance
Genetist

 

[Fluff]

The link to your file doesn't work, comes back as 404 We can’t find what you’re looking for.

 

[genetist]

The link to your file doesn't work, comes back as 404 We can’t find what you’re looking for.

sorry for that please find here. i do not know why it went wrong and showing that error
https://www.dropbox.com/s/iq7m5a2e4ekt35x/SNP PIC.xlsx?dl=0
regards,
Genetist

 

[Peter_SSs]

See if this formula, copied across, does what you want.

Excel Workbook
	A	B	C	D	E	F
1	IND	M1	M2	M3	M4	M5
2	1	A/A	G/G	C/C	T/T	T/T
3	2	T/T	C/C	C/C	G/G	G/G
4	3	C/C	T/T	C/C	C/C	C/C
5	4	G/G	T/T	T/T	G/C	A/A
6	5	G/G	T/T	G/G	C/T	A/A
7
8		18	14	14	12	18
Sheet3

 

[genetist]

Dear Peter_SSs
sorry for my late reply. your formula worked perfectly on my data. Thanks lot for your help and time spent on this
Regards

 

[Peter_SSs]

Good news. Thanks for letting us know - eventually.

 

[genetist]

Dear

Dear Peter_SSs

is it possibile to ommit from calculation ((i mean no comparisons this cell with any other cells even if did outcome should be 0)) if any cell having text MISSING from B2 to F6
Thanking you very much in Advance

 

[Peter_SSs]

I don't understand what you are asking.

What about some representative sample data and expected results with XL2BB and further explanation in relation to that sample data?

Also, please update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[genetist]

I don't understand what you are asking.

What about some representative sample data and expected results with XL2BB and further explanation in relation to that sample data?

Also, please update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Dear Peter_Sss
please check my example data

SNP PIC.xlsx
	A	B	C	D	E	F
1	IND	M1	M2	M3	M4	M5
2	1	A/A	G/G	MISSING	T/T	T/T
3	2	T/T	C/C	C/C	G/G	G/G
4	3	C/C	T/T	C/C	C/C	C/C
5	4	G/G	T/T	T/T	G/C	A/A
6	5	G/G	T/T	G/G	C/T	A/A
7
8	I wiss to get
9	IND	M1	M2	M3	M4	M5
10	1	A/A	G/G	MISSING	T/T	T/T
11	2	T/T	C/C	C/C	G/G	G/G
12	3	C/C	T/T	C/C	C/C	C/C
13	4	G/G	T/T	T/T	G/C	A/A
14	5	G/G	T/T	G/G	C/T	A/A
15		18	14	10	6	18
16
17	example calculation
18	A/A	A/A	0
19	A/A	T/T	1
20	A/A	C/C	1
21	A/A	G/G	1
22	A/A	G/G	1
23	T/T	A/A	1
24	T/T	T/T	0
25	T/T	C/C	1
26	T/T	G/G	1
27	T/T	G/G	1
28	C/C	A/A	1
29	C/C	T/T	1
30	C/C	C/C	0
31	C/C	G/G	1
32	C/C	G/G	1
33	G/G	A/A	1
34	G/G	T/T	1
35	G/G	C/C	1
36	G/G	G/G	0
37	G/G	G/G	0
38	G/G	A/A	1
39	G/G	T/T	1
40	G/G	C/C	1
41	G/G	G/G	0
42	G/G	G/G	0
43			18
Sheet2

Cell Formulas
Range	Formula
F15,B15:C15	B15	=SUMPRODUCT(ROWS(B10:B14)-COUNTIF(B10:B14,B10:B14))-(ROWS(B10:B14)-1)*SUMPRODUCT(--(LEFT(B10:B14,1)<>RIGHT(B10:B14,1)))

Thanks in advance

 

[Peter_SSs]

Sorry, I am still not following.
The example for the calculation that you have provided seems to be the same as column B from post #4 and the formula provided there still produces 18 for that column.
The previous formula also produces the expected results here for columns C and F.

What I think I need is the steps for columns D & E as those results appear to be different. As well as providing the list of 0/1 values, I need to know how you determine each of those 0/1 values.