hi to all VBA programmers,
I have data like this with 6 columns</SPAN>
<TBODY>
</TBODY>
<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>
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>
- First I want to find how many columns (from AB1 to AB5) are different for P1 and P2 , </SPAN>
- 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>
- 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>
- 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>
- I want to repeat this for remaining set of P1 and P2.</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>