Hi everyone,
I have a worksheet with 10,000+ rows of data across columns A-Q. Every two/three rows are identical apart from one string of text in one of the columns. I need both to be able to identify the column where the discrepancy occurs and display the variations in the text. An example would probably help!
(NB columns R & S are how I would like the results to be displayed - full explanation below!)
<table border=0 cellpadding=0 cellspacing=0 width=1135 style='border-collapse:
collapse;table-layout:fixed'>
<col class=xl30 width=22>
<col class=xl30 width=64>
<col class=xl30 width=39>
<col class=xl30 width=47>
<col class=xl30 width=45>
<col class=xl30 width=47>
<col class=xl30 width=33>
<col class=xl30 width=47>
<col class=xl39 width=60>
<col class=xl30 width=107>
<col class=xl30 width=107 span=2>
<col class=xl30 width=122>
<col class=xl30 width=107>
<col class=xl30 width=102>
<col class=xl30 width=79>
<tr height=21>
<td height=21 class=xl36 width=22> </td>
<td class=xl36 width=64>A</td>
<td class=xl36 width=39>B</td>
<td class=xl36 width=47>C</td>
<td class=xl36 width=45>D</td>
<td class=xl36 width=47>E</td>
<td class=xl36 width=33>…</td>
<td class=xl36 width=47>K</td>
<td class=xl37 width=60>L</td>
<td class=xl36 width=107>M</td>
<td class=xl36 width=107>N</td>
<td class=xl36 width=107>O</td>
<td class=xl36 width=122>P</td>
<td class=xl36 width=107>Q</td>
<td class=xl36 width=102>R</td>
<td class=xl36 width=79>S</td>
</tr>
<tr height=24>
<td height=24 class=xl36>1</td>
<td class=xl31 width=64>Name</td>
<td class=xl31 width=39>ID</td>
<td class=xl31 width=47>Stat #1</td>
<td class=xl31 width=45>stat #2</td>
<td class=xl31 width=47>Stat #3</td>
<td class=xl31 width=33>…</td>
<td class=xl31 width=47>Stat #9</td>
<td class=xl38 width=60>date</td>
<td class=xl31 width=107>text string #1</td>
<td class=xl31 width=107>text string #2</td>
<td class=xl31 width=107>text string #3</td>
<td class=xl31 width=122>text string #4</td>
<td class=xl31 width=107>text string #5</td>
<td class=xl32 width=102>RESULT: col different?</td>
<td class=xl32 width=79>RESULT: text value?</td>
</tr>
<tr height=12>
<td height=12 class=xl36> </td>
<td class=xl31 width=64> </td>
<td class=xl31 width=39> </td>
<td class=xl31 width=47> </td>
<td class=xl31 width=45> </td>
<td class=xl31 width=47> </td>
<td class=xl31 width=33> </td>
<td class=xl31 width=47> </td>
<td class=xl38 width=60> </td>
<td class=xl31 width=107> </td>
<td class=xl31 width=107> </td>
<td class=xl31 width=107> </td>
<td class=xl31 width=122> </td>
<td class=xl31 width=107> </td>
<td class=xl32 width=102> </td>
<td class=xl32 width=79> </td>
</tr>
<tr height=12>
<td height=12 class=xl36>2</td>
<td class=xl31 width=64>Example</td>
<td class=xl31 width=39>20</td>
<td class=xl31 width=47>13</td>
<td class=xl31 width=45>15</td>
<td class=xl31 width=47>49</td>
<td class=xl31 width=33>…</td>
<td class=xl31 width=47>908</td>
<td class=xl38 width=60>6/21/09</td>
<td class=xl31 width=107>some text</td>
<td class=xl31 width=107>more text</td>
<td class=xl33 width=107>other text</td>
<td class=xl31 width=122>some text</td>
<td class=xl31 width=107>some text</td>
<td class=xl32 width=102>text string #3</td>
<td class=xl32 width=79>"other"</td>
</tr>
<tr height=12>
<td height=12 class=xl36> </td>
<td class=xl31 width=64> </td>
<td class=xl31 width=39> </td>
<td class=xl31 width=47> </td>
<td class=xl31 width=45> </td>
<td class=xl31 width=47> </td>
<td class=xl31 width=33> </td>
<td class=xl31 width=47> </td>
<td class=xl38 width=60> </td>
<td class=xl31 width=107> </td>
<td class=xl31 width=107> </td>
<td class=xl33 width=107> </td>
<td class=xl31 width=122> </td>
<td class=xl31 width=107> </td>
<td class=xl32 width=102> </td>
<td class=xl32 width=79> </td>
</tr>
<tr height=12>
<td height=12 class=xl36>3</td>
<td class=xl31 width=64>Example</td>
<td class=xl31 width=39>20</td>
<td class=xl31 width=47>14</td>
<td class=xl31 width=45>16</td>
<td class=xl31 width=47>47.6</td>
<td class=xl31 width=33>…</td>
<td class=xl31 width=47>4250</td>
<td class=xl38 width=60>6/21/09</td>
<td class=xl31 width=107>some text</td>
<td class=xl31 width=107>more text</td>
<td class=xl33 width=107>different text</td>
<td class=xl31 width=122>some text</td>
<td class=xl31 width=107>some text</td>
<td class=xl32 width=102>text string #3</td>
<td class=xl32 width=79>"different"</td>
</tr>
<tr height=12>
<td height=12 class=xl36> </td>
<td class=xl31 width=64> </td>
<td class=xl31 width=39> </td>
<td class=xl31 width=47> </td>
<td class=xl31 width=45> </td>
<td class=xl31 width=47> </td>
<td class=xl31 width=33> </td>
<td class=xl31 width=47> </td>
<td class=xl38 width=60> </td>
<td class=xl31 width=107> </td>
<td class=xl31 width=107> </td>
<td class=xl33 width=107> </td>
<td class=xl31 width=122> </td>
<td class=xl31 width=107> </td>
<td class=xl32 width=102> </td>
<td class=xl32 width=79> </td>
</tr>
<tr height=12>
<td height=12 class=xl36>4</td>
<td class=xl31 width=64>Example</td>
<td class=xl31 width=39>22</td>
<td class=xl31 width=47>16</td>
<td class=xl31 width=45>18</td>
<td class=xl31 width=47>70.2</td>
<td class=xl31 width=33>…</td>
<td class=xl31 width=47>882</td>
<td class=xl38 width=60>1/7/09</td>
<td class=xl31 width=107>the same</td>
<td class=xl33 width=107>lorem</td>
<td class=xl31 width=107>placeholder</td>
<td class=xl31 width=122>nonsense</td>
<td class=xl31 width=107>random</td>
<td class=xl34>text string #2</td>
<td class=xl32 width=79>"lorem"</td>
</tr>
<tr height=12>
<td height=12 class=xl36> </td>
<td class=xl31 width=64> </td>
<td class=xl31 width=39> </td>
<td class=xl31 width=47> </td>
<td class=xl31 width=45> </td>
<td class=xl31 width=47> </td>
<td class=xl31 width=33> </td>
<td class=xl31 width=47> </td>
<td class=xl38 width=60> </td>
<td class=xl31 width=107> </td>
<td class=xl33 width=107> </td>
<td class=xl31 width=107> </td>
<td class=xl31 width=122> </td>
<td class=xl31 width=107> </td>
<td class=xl34> </td>
<td class=xl32 width=79> </td>
</tr>
<tr height=12>
<td height=12 class=xl36>5</td>
<td class=xl31 width=64>Example</td>
<td class=xl31 width=39>22</td>
<td class=xl31 width=47>17</td>
<td class=xl31 width=45>19</td>
<td class=xl31 width=47>73.2</td>
<td class=xl31 width=33>…</td>
<td class=xl31 width=47>882</td>
<td class=xl38 width=60>1/7/09</td>
<td class=xl31 width=107>the same</td>
<td class=xl33 width=107>dolor sit</td>
<td class=xl31 width=107>placeholder</td>
<td class=xl31 width=122>nonsense</td>
<td class=xl31 width=107>random</td>
<td class=xl34>text string #2</td>
<td class=xl32 width=79>"dolor sit"</td>
</tr>
<tr height=12>
<td height=12 class=xl36> </td>
<td class=xl31 width=64> </td>
<td class=xl31 width=39> </td>
<td class=xl31 width=47> </td>
<td class=xl31 width=45> </td>
<td class=xl31 width=47> </td>
<td class=xl31 width=33> </td>
<td class=xl31 width=47> </td>
<td class=xl38 width=60> </td>
<td class=xl31 width=107> </td>
<td class=xl33 width=107> </td>
<td class=xl31 width=107> </td>
<td class=xl31 width=122> </td>
<td class=xl31 width=107> </td>
<td class=xl34> </td>
<td class=xl32 width=79> </td>
</tr>
<tr height=12>
<td height=12 class=xl36>6</td>
<td class=xl31 width=64>Example</td>
<td class=xl31 width=39>22</td>
<td class=xl31 width=47>18</td>
<td class=xl31 width=45>20</td>
<td class=xl31 width=47>68.4</td>
<td class=xl31 width=33>…</td>
<td class=xl31 width=47>3312</td>
<td class=xl38 width=60>1/7/09</td>
<td class=xl31 width=107>the same</td>
<td class=xl35>amet</td>
<td class=xl31 width=107>placeholder</td>
<td class=xl31 width=122>nonsense</td>
<td class=xl31 width=107>random</td>
<td class=xl34>text string #2</td>
<td class=xl34>"amet"</td>
</tr>
<tr height=12>
<td height=12 class=xl36> </td>
<td class=xl31 width=64> </td>
<td class=xl31 width=39> </td>
<td class=xl31 width=47> </td>
<td class=xl31 width=45> </td>
<td class=xl31 width=47> </td>
<td class=xl31 width=33> </td>
<td class=xl31 width=47> </td>
<td class=xl38 width=60> </td>
<td class=xl31 width=107> </td>
<td class=xl35> </td>
<td class=xl31 width=107> </td>
<td class=xl31 width=122> </td>
<td class=xl31 width=107> </td>
<td class=xl34> </td>
<td class=xl34> </td>
</tr>
<tr height=12>
<td height=12 class=xl36>7</td>
<td class=xl31 width=64>Another</td>
<td class=xl31 width=39>20</td>
<td class=xl31 width=47>23</td>
<td class=xl31 width=45>25</td>
<td class=xl31 width=47>63.4</td>
<td class=xl31 width=33>…</td>
<td class=xl31 width=47>811</td>
<td class=xl38 width=60>7/14/09</td>
<td class=xl31 width=107>excel</td>
<td class=xl31 width=107>powerpoint</td>
<td class=xl31 width=107>word</td>
<td class=xl33 width=122>thanks mrexcel</td>
<td class=xl31 width=107>outlook</td>
<td class=xl32 width=102>text string #4</td>
<td class=xl34>"thanks"</td>
</tr>
<tr height=12>
<td height=12 class=xl36> </td>
<td class=xl31 width=64> </td>
<td class=xl31 width=39> </td>
<td class=xl31 width=47> </td>
<td class=xl31 width=45> </td>
<td class=xl31 width=47> </td>
<td class=xl31 width=33> </td>
<td class=xl31 width=47> </td>
<td class=xl38 width=60> </td>
<td class=xl31 width=107> </td>
<td class=xl31 width=107> </td>
<td class=xl31 width=107> </td>
<td class=xl33 width=122> </td>
<td class=xl31 width=107> </td>
<td class=xl32 width=102> </td>
<td class=xl34> </td>
</tr>
<tr height=12>
<td height=12 class=xl36>8</td>
<td class=xl31 width=64>Another</td>
<td class=xl31 width=39>20</td>
<td class=xl31 width=47>24</td>
<td class=xl31 width=45>26</td>
<td class=xl31 width=47>54.3</td>
<td class=xl31 width=33>…</td>
<td class=xl31 width=47>811</td>
<td class=xl38 width=60>7/14/09</td>
<td class=xl31 width=107>excel</td>
<td class=xl31 width=107>powerpoint</td>
<td class=xl31 width=107>word</td>
<td class=xl33 width=122>thank you mrexcel</td>
<td class=xl31 width=107>outlook</td>
<td class=xl32 width=102>text string #4</td>
<td class=xl34>"thank you"</td>
</tr>
<tr height=12>
<td height=12 class=xl36> </td>
<td class=xl31 width=64> </td>
<td class=xl31 width=39> </td>
<td class=xl31 width=47> </td>
<td class=xl31 width=45> </td>
<td class=xl31 width=47> </td>
<td class=xl31 width=33> </td>
<td class=xl31 width=47> </td>
<td class=xl38 width=60> </td>
<td class=xl31 width=107> </td>
<td class=xl31 width=107> </td>
<td class=xl31 width=107> </td>
<td class=xl33 width=122> </td>
<td class=xl31 width=107> </td>
<td class=xl32 width=102> </td>
<td class=xl34> </td>
</tr>
<tr height=12>
<td height=12 class=xl36>9</td>
<td class=xl31 width=64>Another</td>
<td class=xl31 width=39>20</td>
<td class=xl31 width=47>25</td>
<td class=xl31 width=45>27</td>
<td class=xl31 width=47>49.8</td>
<td class=xl31 width=33>…</td>
<td class=xl31 width=47>1754</td>
<td class=xl38 width=60>7/14/09</td>
<td class=xl31 width=107>excel</td>
<td class=xl31 width=107>powerpoint</td>
<td class=xl31 width=107>word</td>
<td class=xl33 width=122>thanks mrexcel!</td>
<td class=xl31 width=107>outlook</td>
<td class=xl32 width=102>text string #4</td>
<td class=xl34>"thanks", "!"</td>
</tr>
</table>
</p>
<p>
First, the macro will need to identify rows where the contents of both columns A and B (i.e. the Name and ID) are identical. Next, it should compare these rows across columns M-Q to identify:
1) Where the cells' contents are not identical
2) The text differences
In the table above, I've mocked up how I'd like the results to be displayed (columns R & S). The results of 1) are displayed in column R, while the results of 2) are displayed in column S.
There are three examples in the table above; here's how they work:
i. Rows 2 & 3 are compared because the Name column ("Example") and ID column ("20") both match. The text strings in columns M through to Q are identical in rows 2 & 3 APART from in column O ("text string 3"). Here, row 2 says "other text" while row 3 says "different text". Thus, in results column R (which identifies the column in which the rows' contents differ) "text string 3" is displayed. Column S displays the variance itself: in row 2 this is "other"; in row 3 this is "different".
ii. Rows 4, 5 & 6 are compared because the Name column ("Example") and ID column ("22") both match. The text strings in columns M through to Q are identical in rows 4, 5 & 6 APART from in column N ("text string 2"). Here, row 4 says "lorem", row 5 says "dolor sit", and row 6 says "amet". Thus, in results column R (again, which identifies the column in which the rows' contents differ) "text string 2" is displayed. Column S displays the variance itself: in row 4 this is "lorem", in row 5 this is "dolor sit" and in row 6 this is "amet".
iii. Rows 7, 8 & 9 are compared because the Name column ("Another") and ID column ("20") both match. The text strings in columns M through to Q are identical in rows 7, 8 & 9 APART from in column P ("text string 4"). Here, row 7 says "thanks mrexcel", row 8 says "thank you mrexcel", and row 9 says "thanks mr excel!". Thus, in results column R, "text string 4" is displayed. Column S, again, displays the variance itself: in row 7 this is "thanks", in row 8 this is "thank you" and in row 9 this is both "thanks" and "!".
I hope this all makes some kind of sense to somebody, and that you might be able to help me get columns R and S to display what I need them to.
Thanks MrExcel!
I have a worksheet with 10,000+ rows of data across columns A-Q. Every two/three rows are identical apart from one string of text in one of the columns. I need both to be able to identify the column where the discrepancy occurs and display the variations in the text. An example would probably help!
(NB columns R & S are how I would like the results to be displayed - full explanation below!)
<table border=0 cellpadding=0 cellspacing=0 width=1135 style='border-collapse:
collapse;table-layout:fixed'>
<col class=xl30 width=22>
<col class=xl30 width=64>
<col class=xl30 width=39>
<col class=xl30 width=47>
<col class=xl30 width=45>
<col class=xl30 width=47>
<col class=xl30 width=33>
<col class=xl30 width=47>
<col class=xl39 width=60>
<col class=xl30 width=107>
<col class=xl30 width=107 span=2>
<col class=xl30 width=122>
<col class=xl30 width=107>
<col class=xl30 width=102>
<col class=xl30 width=79>
<tr height=21>
<td height=21 class=xl36 width=22> </td>
<td class=xl36 width=64>A</td>
<td class=xl36 width=39>B</td>
<td class=xl36 width=47>C</td>
<td class=xl36 width=45>D</td>
<td class=xl36 width=47>E</td>
<td class=xl36 width=33>…</td>
<td class=xl36 width=47>K</td>
<td class=xl37 width=60>L</td>
<td class=xl36 width=107>M</td>
<td class=xl36 width=107>N</td>
<td class=xl36 width=107>O</td>
<td class=xl36 width=122>P</td>
<td class=xl36 width=107>Q</td>
<td class=xl36 width=102>R</td>
<td class=xl36 width=79>S</td>
</tr>
<tr height=24>
<td height=24 class=xl36>1</td>
<td class=xl31 width=64>Name</td>
<td class=xl31 width=39>ID</td>
<td class=xl31 width=47>Stat #1</td>
<td class=xl31 width=45>stat #2</td>
<td class=xl31 width=47>Stat #3</td>
<td class=xl31 width=33>…</td>
<td class=xl31 width=47>Stat #9</td>
<td class=xl38 width=60>date</td>
<td class=xl31 width=107>text string #1</td>
<td class=xl31 width=107>text string #2</td>
<td class=xl31 width=107>text string #3</td>
<td class=xl31 width=122>text string #4</td>
<td class=xl31 width=107>text string #5</td>
<td class=xl32 width=102>RESULT: col different?</td>
<td class=xl32 width=79>RESULT: text value?</td>
</tr>
<tr height=12>
<td height=12 class=xl36> </td>
<td class=xl31 width=64> </td>
<td class=xl31 width=39> </td>
<td class=xl31 width=47> </td>
<td class=xl31 width=45> </td>
<td class=xl31 width=47> </td>
<td class=xl31 width=33> </td>
<td class=xl31 width=47> </td>
<td class=xl38 width=60> </td>
<td class=xl31 width=107> </td>
<td class=xl31 width=107> </td>
<td class=xl31 width=107> </td>
<td class=xl31 width=122> </td>
<td class=xl31 width=107> </td>
<td class=xl32 width=102> </td>
<td class=xl32 width=79> </td>
</tr>
<tr height=12>
<td height=12 class=xl36>2</td>
<td class=xl31 width=64>Example</td>
<td class=xl31 width=39>20</td>
<td class=xl31 width=47>13</td>
<td class=xl31 width=45>15</td>
<td class=xl31 width=47>49</td>
<td class=xl31 width=33>…</td>
<td class=xl31 width=47>908</td>
<td class=xl38 width=60>6/21/09</td>
<td class=xl31 width=107>some text</td>
<td class=xl31 width=107>more text</td>
<td class=xl33 width=107>other text</td>
<td class=xl31 width=122>some text</td>
<td class=xl31 width=107>some text</td>
<td class=xl32 width=102>text string #3</td>
<td class=xl32 width=79>"other"</td>
</tr>
<tr height=12>
<td height=12 class=xl36> </td>
<td class=xl31 width=64> </td>
<td class=xl31 width=39> </td>
<td class=xl31 width=47> </td>
<td class=xl31 width=45> </td>
<td class=xl31 width=47> </td>
<td class=xl31 width=33> </td>
<td class=xl31 width=47> </td>
<td class=xl38 width=60> </td>
<td class=xl31 width=107> </td>
<td class=xl31 width=107> </td>
<td class=xl33 width=107> </td>
<td class=xl31 width=122> </td>
<td class=xl31 width=107> </td>
<td class=xl32 width=102> </td>
<td class=xl32 width=79> </td>
</tr>
<tr height=12>
<td height=12 class=xl36>3</td>
<td class=xl31 width=64>Example</td>
<td class=xl31 width=39>20</td>
<td class=xl31 width=47>14</td>
<td class=xl31 width=45>16</td>
<td class=xl31 width=47>47.6</td>
<td class=xl31 width=33>…</td>
<td class=xl31 width=47>4250</td>
<td class=xl38 width=60>6/21/09</td>
<td class=xl31 width=107>some text</td>
<td class=xl31 width=107>more text</td>
<td class=xl33 width=107>different text</td>
<td class=xl31 width=122>some text</td>
<td class=xl31 width=107>some text</td>
<td class=xl32 width=102>text string #3</td>
<td class=xl32 width=79>"different"</td>
</tr>
<tr height=12>
<td height=12 class=xl36> </td>
<td class=xl31 width=64> </td>
<td class=xl31 width=39> </td>
<td class=xl31 width=47> </td>
<td class=xl31 width=45> </td>
<td class=xl31 width=47> </td>
<td class=xl31 width=33> </td>
<td class=xl31 width=47> </td>
<td class=xl38 width=60> </td>
<td class=xl31 width=107> </td>
<td class=xl31 width=107> </td>
<td class=xl33 width=107> </td>
<td class=xl31 width=122> </td>
<td class=xl31 width=107> </td>
<td class=xl32 width=102> </td>
<td class=xl32 width=79> </td>
</tr>
<tr height=12>
<td height=12 class=xl36>4</td>
<td class=xl31 width=64>Example</td>
<td class=xl31 width=39>22</td>
<td class=xl31 width=47>16</td>
<td class=xl31 width=45>18</td>
<td class=xl31 width=47>70.2</td>
<td class=xl31 width=33>…</td>
<td class=xl31 width=47>882</td>
<td class=xl38 width=60>1/7/09</td>
<td class=xl31 width=107>the same</td>
<td class=xl33 width=107>lorem</td>
<td class=xl31 width=107>placeholder</td>
<td class=xl31 width=122>nonsense</td>
<td class=xl31 width=107>random</td>
<td class=xl34>text string #2</td>
<td class=xl32 width=79>"lorem"</td>
</tr>
<tr height=12>
<td height=12 class=xl36> </td>
<td class=xl31 width=64> </td>
<td class=xl31 width=39> </td>
<td class=xl31 width=47> </td>
<td class=xl31 width=45> </td>
<td class=xl31 width=47> </td>
<td class=xl31 width=33> </td>
<td class=xl31 width=47> </td>
<td class=xl38 width=60> </td>
<td class=xl31 width=107> </td>
<td class=xl33 width=107> </td>
<td class=xl31 width=107> </td>
<td class=xl31 width=122> </td>
<td class=xl31 width=107> </td>
<td class=xl34> </td>
<td class=xl32 width=79> </td>
</tr>
<tr height=12>
<td height=12 class=xl36>5</td>
<td class=xl31 width=64>Example</td>
<td class=xl31 width=39>22</td>
<td class=xl31 width=47>17</td>
<td class=xl31 width=45>19</td>
<td class=xl31 width=47>73.2</td>
<td class=xl31 width=33>…</td>
<td class=xl31 width=47>882</td>
<td class=xl38 width=60>1/7/09</td>
<td class=xl31 width=107>the same</td>
<td class=xl33 width=107>dolor sit</td>
<td class=xl31 width=107>placeholder</td>
<td class=xl31 width=122>nonsense</td>
<td class=xl31 width=107>random</td>
<td class=xl34>text string #2</td>
<td class=xl32 width=79>"dolor sit"</td>
</tr>
<tr height=12>
<td height=12 class=xl36> </td>
<td class=xl31 width=64> </td>
<td class=xl31 width=39> </td>
<td class=xl31 width=47> </td>
<td class=xl31 width=45> </td>
<td class=xl31 width=47> </td>
<td class=xl31 width=33> </td>
<td class=xl31 width=47> </td>
<td class=xl38 width=60> </td>
<td class=xl31 width=107> </td>
<td class=xl33 width=107> </td>
<td class=xl31 width=107> </td>
<td class=xl31 width=122> </td>
<td class=xl31 width=107> </td>
<td class=xl34> </td>
<td class=xl32 width=79> </td>
</tr>
<tr height=12>
<td height=12 class=xl36>6</td>
<td class=xl31 width=64>Example</td>
<td class=xl31 width=39>22</td>
<td class=xl31 width=47>18</td>
<td class=xl31 width=45>20</td>
<td class=xl31 width=47>68.4</td>
<td class=xl31 width=33>…</td>
<td class=xl31 width=47>3312</td>
<td class=xl38 width=60>1/7/09</td>
<td class=xl31 width=107>the same</td>
<td class=xl35>amet</td>
<td class=xl31 width=107>placeholder</td>
<td class=xl31 width=122>nonsense</td>
<td class=xl31 width=107>random</td>
<td class=xl34>text string #2</td>
<td class=xl34>"amet"</td>
</tr>
<tr height=12>
<td height=12 class=xl36> </td>
<td class=xl31 width=64> </td>
<td class=xl31 width=39> </td>
<td class=xl31 width=47> </td>
<td class=xl31 width=45> </td>
<td class=xl31 width=47> </td>
<td class=xl31 width=33> </td>
<td class=xl31 width=47> </td>
<td class=xl38 width=60> </td>
<td class=xl31 width=107> </td>
<td class=xl35> </td>
<td class=xl31 width=107> </td>
<td class=xl31 width=122> </td>
<td class=xl31 width=107> </td>
<td class=xl34> </td>
<td class=xl34> </td>
</tr>
<tr height=12>
<td height=12 class=xl36>7</td>
<td class=xl31 width=64>Another</td>
<td class=xl31 width=39>20</td>
<td class=xl31 width=47>23</td>
<td class=xl31 width=45>25</td>
<td class=xl31 width=47>63.4</td>
<td class=xl31 width=33>…</td>
<td class=xl31 width=47>811</td>
<td class=xl38 width=60>7/14/09</td>
<td class=xl31 width=107>excel</td>
<td class=xl31 width=107>powerpoint</td>
<td class=xl31 width=107>word</td>
<td class=xl33 width=122>thanks mrexcel</td>
<td class=xl31 width=107>outlook</td>
<td class=xl32 width=102>text string #4</td>
<td class=xl34>"thanks"</td>
</tr>
<tr height=12>
<td height=12 class=xl36> </td>
<td class=xl31 width=64> </td>
<td class=xl31 width=39> </td>
<td class=xl31 width=47> </td>
<td class=xl31 width=45> </td>
<td class=xl31 width=47> </td>
<td class=xl31 width=33> </td>
<td class=xl31 width=47> </td>
<td class=xl38 width=60> </td>
<td class=xl31 width=107> </td>
<td class=xl31 width=107> </td>
<td class=xl31 width=107> </td>
<td class=xl33 width=122> </td>
<td class=xl31 width=107> </td>
<td class=xl32 width=102> </td>
<td class=xl34> </td>
</tr>
<tr height=12>
<td height=12 class=xl36>8</td>
<td class=xl31 width=64>Another</td>
<td class=xl31 width=39>20</td>
<td class=xl31 width=47>24</td>
<td class=xl31 width=45>26</td>
<td class=xl31 width=47>54.3</td>
<td class=xl31 width=33>…</td>
<td class=xl31 width=47>811</td>
<td class=xl38 width=60>7/14/09</td>
<td class=xl31 width=107>excel</td>
<td class=xl31 width=107>powerpoint</td>
<td class=xl31 width=107>word</td>
<td class=xl33 width=122>thank you mrexcel</td>
<td class=xl31 width=107>outlook</td>
<td class=xl32 width=102>text string #4</td>
<td class=xl34>"thank you"</td>
</tr>
<tr height=12>
<td height=12 class=xl36> </td>
<td class=xl31 width=64> </td>
<td class=xl31 width=39> </td>
<td class=xl31 width=47> </td>
<td class=xl31 width=45> </td>
<td class=xl31 width=47> </td>
<td class=xl31 width=33> </td>
<td class=xl31 width=47> </td>
<td class=xl38 width=60> </td>
<td class=xl31 width=107> </td>
<td class=xl31 width=107> </td>
<td class=xl31 width=107> </td>
<td class=xl33 width=122> </td>
<td class=xl31 width=107> </td>
<td class=xl32 width=102> </td>
<td class=xl34> </td>
</tr>
<tr height=12>
<td height=12 class=xl36>9</td>
<td class=xl31 width=64>Another</td>
<td class=xl31 width=39>20</td>
<td class=xl31 width=47>25</td>
<td class=xl31 width=45>27</td>
<td class=xl31 width=47>49.8</td>
<td class=xl31 width=33>…</td>
<td class=xl31 width=47>1754</td>
<td class=xl38 width=60>7/14/09</td>
<td class=xl31 width=107>excel</td>
<td class=xl31 width=107>powerpoint</td>
<td class=xl31 width=107>word</td>
<td class=xl33 width=122>thanks mrexcel!</td>
<td class=xl31 width=107>outlook</td>
<td class=xl32 width=102>text string #4</td>
<td class=xl34>"thanks", "!"</td>
</tr>
</table>
</p>
<p>
First, the macro will need to identify rows where the contents of both columns A and B (i.e. the Name and ID) are identical. Next, it should compare these rows across columns M-Q to identify:
1) Where the cells' contents are not identical
2) The text differences
In the table above, I've mocked up how I'd like the results to be displayed (columns R & S). The results of 1) are displayed in column R, while the results of 2) are displayed in column S.
There are three examples in the table above; here's how they work:
i. Rows 2 & 3 are compared because the Name column ("Example") and ID column ("20") both match. The text strings in columns M through to Q are identical in rows 2 & 3 APART from in column O ("text string 3"). Here, row 2 says "other text" while row 3 says "different text". Thus, in results column R (which identifies the column in which the rows' contents differ) "text string 3" is displayed. Column S displays the variance itself: in row 2 this is "other"; in row 3 this is "different".
ii. Rows 4, 5 & 6 are compared because the Name column ("Example") and ID column ("22") both match. The text strings in columns M through to Q are identical in rows 4, 5 & 6 APART from in column N ("text string 2"). Here, row 4 says "lorem", row 5 says "dolor sit", and row 6 says "amet". Thus, in results column R (again, which identifies the column in which the rows' contents differ) "text string 2" is displayed. Column S displays the variance itself: in row 4 this is "lorem", in row 5 this is "dolor sit" and in row 6 this is "amet".
iii. Rows 7, 8 & 9 are compared because the Name column ("Another") and ID column ("20") both match. The text strings in columns M through to Q are identical in rows 7, 8 & 9 APART from in column P ("text string 4"). Here, row 7 says "thanks mrexcel", row 8 says "thank you mrexcel", and row 9 says "thanks mr excel!". Thus, in results column R, "text string 4" is displayed. Column S, again, displays the variance itself: in row 7 this is "thanks", in row 8 this is "thank you" and in row 9 this is both "thanks" and "!".
I hope this all makes some kind of sense to somebody, and that you might be able to help me get columns R and S to display what I need them to.
Thanks MrExcel!
