Hi everyone,
I have a worksheet with 10,000+ rows of data across columns A-Q. Every few 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!)
<html><table><tr height=21><td width=22> </td><td width=64>A</td><td width=39>B</td><td width=60>C</td><td width=60>D</td><td width=60>E</td><td width=33>…</td><td width=60>L</td><td width=80>M</td><td width=80>N</td><td width=80>O</td><td width=110>P</td><td width=53>Q</td><td width=80>R</td><td width=80>S</td> </tr> <tr height=24><td width=22>1</td><td width=64>Name</td><td width=39>ID</td><td width=60>Stat #1</td><td width=60>Stat #2</td><td width=60>Stat #3</td><td width=33>…</td><td width=60>Stat #10</td><td width=80>text #1</td><td width=80>text #2</td><td width=80>text #3</td><td width=110>text #4</td><td width=53>text #5</td><td width=80>RESULT:</td><td width=80>RESULT:</td> </tr> <tr height=12><td width=22>2</td><td width=64>Example</td><td width=39>20</td><td width=60>13</td><td width=60>15</td><td width=60>49</td><td width=33>…</td><td width=60>908</td><td width=80>some text</td><td width=80>more text</td><td width=80>other text</td><td width=80>text again</td><td width=110>lots text</td><td width=80>text #3</td><td width=80>"other"</td> </tr> <tr height=12><td width=22>3</td><td width=64>Example</td><td width=39>20</td><td width=60>14</td><td width=60>16</td><td width=60>47.6</td><td width=33>…</td><td width=60>4250</td><td width=80>some text</td><td width=80>more text</td><td width=80>extra text</td><td width=110>text again</td><td width=53>lots text</td><td width=80>text #3</td><td width=80>"different"</td> </tr> <tr height=12><td width=22>4</td><td width=64>Another</td><td width=39>20</td><td width=60>23</td><td width=60>25</td><td width=60>63.4</td><td width=33>…</td><td width=60>811</td><td width=80>excel</td><td width=80>ppt</td><td width=80>word</td><td width=110>thanks mrexcel</td><td width=53>outlook</td><td width=80>text #4</td><td width=80>"thanks"</td> </tr> <tr height=12><td width=22>5</td><td width=64>Another</td><td width=39>20</td><td width=60>24</td><td width=60>26</td><td width=60>54.3</td><td width=33>…</td><td width=60>811</td><td width=80>excel</td><td width=80>ppt</td><td width=80>word</td><td width=110>thank u mrexcel</td><td width=53>outlook</td><td width=80>text #4</td><td width=80>"thank u"</td> </tr> <tr height=12><td width=22>6</td><td width=64>Another</td><td width=39>20</td><td width=60>25</td><td width=60>27</td><td width=60>49.8</td><td width=33>…</td><td width=60>1754</td><td width=80>excel</td><td width=80>ppt</td><td width=80>word</td><td width=110>thanks mrexcel!</td><td width=53>outlook</td><td width=80>text #4</td><td width=80>"thanks", "!"</td> </tr></table></html>
I guess the first step is to identify rows where the contents of both columns A and B (i.e. the Name and ID) are identical. Then, I'll need to be able to 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 two 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 #3"). Here, row 2 says "other text" while row 3 says "extra text". Thus, in results column R (which identifies the column in which the rows' contents differ) "text #3" is displayed. Column S displays the variance itself: in row 2 this is "other"; in row 3 this is "extra".
ii. Rows 4, 5 & 6 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 4, 5 & 6 APART from in column P ("text #4"). Here, row 4 says "thanks mrexcel", row 5 says "thank u mrexcel", and row 6 says "thanks mr excel!". Thus, in results column R, "text #4" is displayed. Column S, again, displays the variance itself: in row 4 this is "thanks", in row 5 this is "thank u" and in row 6 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 few 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!)
<html><table><tr height=21><td width=22> </td><td width=64>A</td><td width=39>B</td><td width=60>C</td><td width=60>D</td><td width=60>E</td><td width=33>…</td><td width=60>L</td><td width=80>M</td><td width=80>N</td><td width=80>O</td><td width=110>P</td><td width=53>Q</td><td width=80>R</td><td width=80>S</td> </tr> <tr height=24><td width=22>1</td><td width=64>Name</td><td width=39>ID</td><td width=60>Stat #1</td><td width=60>Stat #2</td><td width=60>Stat #3</td><td width=33>…</td><td width=60>Stat #10</td><td width=80>text #1</td><td width=80>text #2</td><td width=80>text #3</td><td width=110>text #4</td><td width=53>text #5</td><td width=80>RESULT:</td><td width=80>RESULT:</td> </tr> <tr height=12><td width=22>2</td><td width=64>Example</td><td width=39>20</td><td width=60>13</td><td width=60>15</td><td width=60>49</td><td width=33>…</td><td width=60>908</td><td width=80>some text</td><td width=80>more text</td><td width=80>other text</td><td width=80>text again</td><td width=110>lots text</td><td width=80>text #3</td><td width=80>"other"</td> </tr> <tr height=12><td width=22>3</td><td width=64>Example</td><td width=39>20</td><td width=60>14</td><td width=60>16</td><td width=60>47.6</td><td width=33>…</td><td width=60>4250</td><td width=80>some text</td><td width=80>more text</td><td width=80>extra text</td><td width=110>text again</td><td width=53>lots text</td><td width=80>text #3</td><td width=80>"different"</td> </tr> <tr height=12><td width=22>4</td><td width=64>Another</td><td width=39>20</td><td width=60>23</td><td width=60>25</td><td width=60>63.4</td><td width=33>…</td><td width=60>811</td><td width=80>excel</td><td width=80>ppt</td><td width=80>word</td><td width=110>thanks mrexcel</td><td width=53>outlook</td><td width=80>text #4</td><td width=80>"thanks"</td> </tr> <tr height=12><td width=22>5</td><td width=64>Another</td><td width=39>20</td><td width=60>24</td><td width=60>26</td><td width=60>54.3</td><td width=33>…</td><td width=60>811</td><td width=80>excel</td><td width=80>ppt</td><td width=80>word</td><td width=110>thank u mrexcel</td><td width=53>outlook</td><td width=80>text #4</td><td width=80>"thank u"</td> </tr> <tr height=12><td width=22>6</td><td width=64>Another</td><td width=39>20</td><td width=60>25</td><td width=60>27</td><td width=60>49.8</td><td width=33>…</td><td width=60>1754</td><td width=80>excel</td><td width=80>ppt</td><td width=80>word</td><td width=110>thanks mrexcel!</td><td width=53>outlook</td><td width=80>text #4</td><td width=80>"thanks", "!"</td> </tr></table></html>
I guess the first step is to identify rows where the contents of both columns A and B (i.e. the Name and ID) are identical. Then, I'll need to be able to 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 two 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 #3"). Here, row 2 says "other text" while row 3 says "extra text". Thus, in results column R (which identifies the column in which the rows' contents differ) "text #3" is displayed. Column S displays the variance itself: in row 2 this is "other"; in row 3 this is "extra".
ii. Rows 4, 5 & 6 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 4, 5 & 6 APART from in column P ("text #4"). Here, row 4 says "thanks mrexcel", row 5 says "thank u mrexcel", and row 6 says "thanks mr excel!". Thus, in results column R, "text #4" is displayed. Column S, again, displays the variance itself: in row 4 this is "thanks", in row 5 this is "thank u" and in row 6 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!
