VBA code needed to compare rows in excel 2010

genetist

Board Regular
Joined
Mar 29, 2013
Messages
75
Office Version
  1. 2016
Platform
  1. Windows
hi to all VBA programmers,
I have data like this with 6 columns</SPAN>
LINES</SPAN></SPAN>
AB1</SPAN></SPAN>
AB2</SPAN></SPAN>
AB3</SPAN></SPAN>
AB4</SPAN></SPAN>
AB5</SPAN></SPAN>
P1</SPAN></SPAN>
Z/Z</SPAN></SPAN>
T/T</SPAN></SPAN>
-/-</SPAN></SPAN>
T/T</SPAN></SPAN>
T/T</SPAN></SPAN>
P2</SPAN></SPAN>
A/A</SPAN></SPAN>
A/A</SPAN></SPAN>
G/G</SPAN></SPAN>
Z/Z</SPAN></SPAN>
T/T</SPAN></SPAN>
1</SPAN></SPAN>
G/G</SPAN></SPAN>
T/T</SPAN></SPAN>
G/G</SPAN></SPAN>
T/T</SPAN></SPAN>
G/G</SPAN></SPAN>
2</SPAN></SPAN>
T/T</SPAN></SPAN>
A/A</SPAN></SPAN>
C/C</SPAN></SPAN>
C/C</SPAN></SPAN>
T/T</SPAN></SPAN>
3</SPAN></SPAN>
T/T</SPAN></SPAN>
G/G</SPAN></SPAN>
T/T</SPAN></SPAN>
G/G</SPAN></SPAN>
T/T</SPAN></SPAN>
4</SPAN></SPAN>
A/A</SPAN></SPAN>
C/C</SPAN></SPAN>
A/A</SPAN></SPAN>
A/A</SPAN></SPAN>
A/A</SPAN></SPAN>
5</SPAN></SPAN>
A/A</SPAN></SPAN>
A/A</SPAN></SPAN>
T/T</SPAN></SPAN>
T/T</SPAN></SPAN>
A/A</SPAN></SPAN>
P1</SPAN></SPAN>
Z/Z</SPAN></SPAN>
T/T</SPAN></SPAN>
-/-</SPAN></SPAN>
T/T</SPAN></SPAN>
T/T</SPAN></SPAN>
P2</SPAN></SPAN>
A/A</SPAN></SPAN>
A/A</SPAN></SPAN>
G/G</SPAN></SPAN>
Z/Z</SPAN></SPAN>
T/T</SPAN></SPAN>
1</SPAN></SPAN>
G/G</SPAN></SPAN>
T/T</SPAN></SPAN>
G/G</SPAN></SPAN>
T/T</SPAN></SPAN>
G/G</SPAN></SPAN>
2</SPAN></SPAN>
T/T</SPAN></SPAN>
A/A</SPAN></SPAN>
C/C</SPAN></SPAN>
C/C</SPAN></SPAN>
T/T</SPAN></SPAN>
3</SPAN></SPAN>
T/T</SPAN></SPAN>
G/G</SPAN></SPAN>
T/T</SPAN></SPAN>
G/G</SPAN></SPAN>
T/T</SPAN></SPAN>
4</SPAN></SPAN>
A/A</SPAN></SPAN>
C/C</SPAN></SPAN>
A/A</SPAN></SPAN>
A/A</SPAN></SPAN>
A/A</SPAN></SPAN>
5</SPAN></SPAN>
A/A</SPAN></SPAN>
A/A</SPAN></SPAN>
T/T</SPAN></SPAN>
T/T</SPAN></SPAN>
A/A</SPAN></SPAN>

<TBODY>
</TBODY>


  1. First I want to find how many columns (from AB1 to AB5) are different for P1 and P2 , </SPAN>
Eq means: Both P1 and P2 should contain same letters (alleles) or if any one of P1 or P2 contains Z/Z or -/- I should consider them as eq only. Now I am doing this using this formula =IF(D2=D3,"mono",IF(OR(D2="Z/Z",D2="-/-"),"mono",IF(OR(D3="Z/Z",D3="-/-"),"mono","poly")))</SPAN>


  1. I will compare lines column values from 1 with P2 across all the columns (from AB1 to AB5) in horizontal way and continue for remaining lines from 2 to 5. if they match I would like to give 1 else 0 and I would like to continue this till my programme encounters second set of P1 and P2. Presently I am doing this with this formula =if(D4=D$3,1,0).</SPAN>
  2. I will make sum for lines 1 to 5 across all the columns from columns AB1 to AB5, but I will include only columns showing different for P1 and P2 in my sum count. Now I am working on this with sumif formula.</SPAN>
  3. I will calculate percentage of matching lines 1 to 5 with P2 by dividing sum came from SUMIF with number of different markers between P1 and P2.</SPAN>
  4. I want to repeat this for remaining set of P1 and P2.</SPAN>
I am expecting like this</SPAN>
LINES</SPAN></SPAN>
XY1</SPAN></SPAN>
XY2</SPAN></SPAN>
XY3</SPAN></SPAN>
XY4</SPAN></SPAN>
XY5</SPAN></SPAN>
P1</SPAN></SPAN>
EQ</SPAN></SPAN>
NE</SPAN></SPAN>
EQ</SPAN></SPAN>
EQ</SPAN></SPAN>
EQ</SPAN></SPAN>
SUM</SPAN></SPAN>
%</SPAN></SPAN>
P2</SPAN></SPAN>
1</SPAN></SPAN>
1</SPAN></SPAN>
0</SPAN></SPAN>
0</SPAN></SPAN>
1</SPAN></SPAN>
0</SPAN></SPAN>
0</SPAN></SPAN>
0</SPAN></SPAN>
0</SPAN></SPAN>
2</SPAN></SPAN>
0</SPAN></SPAN>
1</SPAN></SPAN>
0</SPAN></SPAN>
0</SPAN></SPAN>
1</SPAN></SPAN>
1</SPAN></SPAN>
100</SPAN></SPAN>
3</SPAN></SPAN>
0</SPAN></SPAN>
0</SPAN></SPAN>
0</SPAN></SPAN>
0</SPAN></SPAN>
1</SPAN></SPAN>
0</SPAN></SPAN>
0</SPAN></SPAN>
4</SPAN></SPAN>
1</SPAN></SPAN>
0</SPAN></SPAN>
0</SPAN></SPAN>
0</SPAN></SPAN>
0</SPAN></SPAN>
0</SPAN></SPAN>
0</SPAN></SPAN>
5</SPAN></SPAN>
1</SPAN></SPAN>
1</SPAN></SPAN>
0</SPAN></SPAN>
0</SPAN></SPAN>
0</SPAN></SPAN>
1</SPAN></SPAN>
100</SPAN></SPAN>

<TBODY>
</TBODY>

Like this I have data in more than 5000 rows and at present I am doing in excel 2010 with different formulas but it is taking lot of my energy. If anyone provide VBA code for this task I will be more happy and it would be appreciated.
</SPAN>
Regards,</SPAN>
Genetist.</SPAN>
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

Forum statistics

Threads
1,213,527
Messages
6,114,144
Members
448,552
Latest member
WORKINGWITHNOLEADER

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