Editing Data and Comparing lists

[mesolomo]

I'm currently working on sorting a column of data from a PDF file into separate columns to later compare to another sheet. I'm having a few problems getting the data into a workable format, and also don't know how to compare to the other file. These are the specific issues, if someone can help me!

Ultimately, I want the data in columns that look like this:
Name, City, State, Zip, $123.00, mm/dd/yy

Currently it looks like this:

A
1 Name
2 City State Zip
3 $123.00 mm/dd/yy

The sheets I'm working with are thousands of rows long, with these three rows of data repeating for every person. I have two problems with them, however:

1. I ultimately need each of those pieces of information into a seperate cell, in separate columns. I've been transposing the data then doing "Text to Columns" to get it from three separate rows in Column A to one row in Columns A, B, C etc.

The problem with this is the names aren't always uniform. Sometimes there's Mr. or Mrs. in front of them, sometimes they have a middle initial, or an "esq." behind them, which completely messes up the "Text to Columns." Is there a better way to break them up into separate cells?


2. Secondly, not all of the names are individuals- some are businesses or charities. I want to remove all of these (and the two lines of data that go with each of them), so I only have individual people. Do you have any idea of how to make excel know the difference between, for example, "Mr. John Smith" and "Smith and Sons Realty"?

3. Lastly Once I have all of this data spread out into the different columns
(Last Name, First Name, City, State, Zip, Date, Amount), I need to compare it to another list. Basically, I've got one list of data configured in a similar way, and I want to identify any repeats between the two lists. I know that excel has a way to delete repeats, but what I need is a way to delete everything BUT repeats. Any ideas?

Thank you for your help!

 

[Jacophile]

Hi, just to weigh in again, as an initial observation, I think it would simplify things enormously if you first combined your candidate A and B lists into one big list with two extra columns: one for Candidate name and one to designate if it is an A or B record. Then focus your efforts on this single table.

Secondly, you can transform your vertical list to the desired table form using some reasonably straight forward formulae like this:
(with the formulae in columns C to E dragged down as far as you need)

<title>Excel Jeanie HTML</title>Sheet1

<table style="background-color: rgb(255, 255, 255); padding-left: 2pt; padding-right: 2pt; font-family: Arial,Arial; font-size: 10pt;" border="1" cellpadding="0" cellspacing="0"> <colgroup> <col style="width: 30px; font-weight: bold;"> <col style="width: 243px;"> <col style="width: 63px;"> <col style="width: 213px;"> <col style="width: 177px;"> <col style="width: 108px;"></colgroup> <tbody> <tr style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt; font-weight: bold;"> <td> </td> <td>A</td> <td>B</td> <td>C</td> <td>D</td> <td>E</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">1</td> <td> </td> <td> </td> <td> </td> <td> </td> <td> </td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">2</td> <td>Dr. Nancy Petersmeyer, Physician</td> <td> </td> <td>Dr. Nancy Petersmeyer, Physician</td> <td>Philadelphia, PA 19118-4030</td> <td>$500.00 5/2/2007</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">3</td> <td>Philadelphia, PA 19118-4030</td> <td> </td> <td>Jennifer Jean Rickard, Educator</td> <td>Bryn Mawr, PA 19010-2859</td> <td>$500.00 5/2/2007</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">4</td> <td>$500.00 5/2/2007</td> <td></td> <td></td> <td></td> <td></td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">5</td> <td>Jennifer Jean Rickard, Educator</td> <td></td> <td></td> <td></td> <td></td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">6</td> <td>Bryn Mawr, PA 19010-2859</td> <td></td> <td></td> <td></td> <td></td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">7</td> <td>$500.00 5/2/2007</td> <td></td> <td></td> <td></td> <td></td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">8</td> <td>Mr. Alan Bowser, Financial Services</td> <td></td> <td></td> <td></td> <td></td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">9</td> <td>Bedford, NY 10506-1521</td> <td></td> <td></td> <td></td> <td></td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">10</td> <td>$2,500.00 5/2/2007</td> <td></td> <td></td> <td></td> <td></td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">11</td> <td>Mr. Alan Sandman, Physician</td> <td></td> <td></td> <td></td> <td></td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">12</td> <td>Philadelphia, PA 19103-6607</td> <td></td> <td></td> <td></td> <td></td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">13</td> <td>$1,000.00 5/2/2007</td> <td></td> <td></td> <td></td> <td></td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">14</td> <td>Mr. Bruce Foulke, CEO</td> <td></td> <td></td> <td></td> <td></td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">15</td> <td>Philadelphia, PA 19115-1603</td> <td></td> <td></td> <td></td> <td></td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">16</td> <td>$500.00 5/2/2007</td> <td></td> <td></td> <td></td> <td></td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">17</td> <td>Mr. Bruce Koch, CPA</td> <td></td> <td></td> <td></td> <td></td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">18</td> <td>Philadelphia, PA 19119-1846</td> <td></td> <td></td> <td></td> <td></td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">19</td> <td>$500.00 5/2/2007</td> <td></td> <td></td> <td></td> <td></td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">20</td> <td>Mr. Cameron MacTavish, Architect</td> <td></td> <td></td> <td></td> <td></td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">21</td> <td>Philadelphia, PA 19144-3929</td> <td></td> <td></td> <td></td> <td></td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">22</td> <td>$500.00 5/2/2007</td> <td></td> <td></td> <td></td> <td></td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">23</td> <td>Mr. David Hayne, Manager</td> <td></td> <td></td> <td></td> <td></td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">24</td> <td>Philadelphia, PA 19103-6522</td> <td></td> <td></td> <td></td> <td></td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">25</td> <td>$1,500.00 5/2/2007</td> <td></td> <td></td> <td></td> <td></td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">26</td> <td>Mr. David Lipson, President & Publisher</td> <td></td> <td></td> <td></td> <td></td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">27</td> <td>Bryn Mawr, PA 19010-1648</td> <td></td> <td></td> <td></td> <td></td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">28</td> <td>$2,500.00 5/2/2007</td> <td></td> <td></td> <td></td> <td></td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">29</td> <td>Mr. David Pincus, CEO</td> <td></td> <td></td> <td></td> <td></td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">30</td> <td>Philadelphia, PA 19106</td> <td></td> <td></td> <td></td> <td></td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">31</td> <td>$500.00 5/2/2007</td> <td></td> <td></td> <td></td> <td></td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">32</td> <td>Mr. David Stone, Higher Educatoin</td> <td></td> <td></td> <td></td> <td></td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">33</td> <td>New York, NY 10025-2051</td> <td></td> <td></td> <td></td> <td></td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">34</td> <td>$500.00 5/2/2007</td> <td> </td> <td> </td> <td> </td> <td> </td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">35</td> <td> </td> <td> </td> <td> </td> <td> </td> <td> </td></tr></tbody></table>
<table style="border-style: groove; border-color: rgb(0, 255, 0); background-color: rgb(255, 252, 249); font-family: Arial; color: rgb(0, 0, 0); font-size: 10pt;"> <tbody> <tr> <td>Spreadsheet Formulas</td></tr> <tr> <td> <table style="font-family: Arial; font-size: 9pt;" border="1" cellpadding="2" cellspacing="0"> <tbody> <tr style="background-color: rgb(202, 202, 202); font-size: 10pt;"> <td>Cell</td> <td>Formula</td></tr> <tr> <td>C2</td> <td>=OFFSET($A$2,(ROW()-ROW($D$2))*3,0)</td></tr> <tr> <td>D2</td> <td>=INDEX($A$2:$A$34,MATCH($C2,$A$2:$A$34,0)+1)</td></tr> <tr> <td>E2</td> <td>=INDEX($A$2:$A$34,MATCH($C2,$A$2:$A$34,0)+2)</td></tr> <tr> <td>C3</td> <td>=OFFSET($A$2,(ROW()-ROW($D$2))*3,0)</td></tr> <tr> <td>D3</td> <td>=INDEX($A$2:$A$34,MATCH($C3,$A$2:$A$34,0)+1)</td></tr> <tr> <td>E3</td> <td>=INDEX($A$2:$A$34,MATCH($C3,$A$2:$A$34,0)+2)</td></tr></tbody></table></td></tr></tbody></table>

Excel tables to the web >> Excel Jeanie HTML 4

 

[Jacophile]

Just to take it one step further, you can tease out the components in the first column using some helper columns and some simple text manipulation formulae as follows:
(I prefer to use helper columns on purpose to keep things comprehensible)

<title>Excel Jeanie HTML</title>Sheet1

<table style="background-color: rgb(255, 255, 255); padding-left: 2pt; padding-right: 2pt; font-family: Arial,Arial; font-size: 10pt;" border="1" cellpadding="0" cellspacing="0"> <colgroup> <col style="width: 30px; font-weight: bold;"> <col style="width: 243px;"> <col style="width: 14px;"> <col style="width: 21px;"> <col style="width: 21px;"> <col style="width: 21px;"> <col style="width: 213px;"> <col style="width: 31px;"> <col style="width: 59px;"> <col style="width: 89px;"> <col style="width: 89px;"> <col style="width: 177px;"> <col style="width: 108px;"></colgroup> <tbody> <tr style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt; font-weight: bold;"> <td> </td> <td>A</td> <td>B</td> <td>C</td> <td>D</td> <td>E</td> <td>F</td> <td>G</td> <td>H</td> <td>I</td> <td>J</td> <td>K</td> <td>L</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">1</td> <td> </td> <td> </td> <td style="color: rgb(128, 128, 128);">Helper columns:</td> <td> </td> <td> </td> <td> </td> <td>Title</td> <td>Name 1</td> <td>Name 2</td> <td>Ocupation</td> <td> </td> <td> </td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">2</td> <td>Dr. Nancy Petersmeyer, Physician</td> <td> </td> <td style="text-align: right; color: rgb(128, 128, 128);">5</td> <td style="text-align: right; color: rgb(128, 128, 128);">10</td> <td style="text-align: right; color: rgb(128, 128, 128);">22</td> <td style="color: rgb(128, 128, 128);">Dr. Nancy Petersmeyer, Physician</td> <td>Dr. </td> <td>Nancy</td> <td>Petersmeyer</td> <td>Physician</td> <td>Philadelphia, PA 19118-4030</td> <td>$500.00 5/2/2007</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">3</td> <td>Philadelphia, PA 19118-4030</td> <td> </td> <td style="text-align: right; color: rgb(128, 128, 128);">1</td> <td style="text-align: right; color: rgb(128, 128, 128);">9</td> <td style="text-align: right; color: rgb(128, 128, 128);">22</td> <td style="color: rgb(128, 128, 128);">Jennifer Jean Rickard, Educator</td> <td> </td> <td>Jennifer</td> <td>Jean Rickard</td> <td>Educator</td> <td>Bryn Mawr, PA 19010-2859</td> <td>$500.00 5/2/2007</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">4</td> <td>$500.00 5/2/2007</td> <td></td> <td> </td> <td> </td> <td> </td> <td></td> <td> </td> <td> </td> <td> </td> <td> </td> <td></td> <td></td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">5</td> <td>Jennifer Jean Rickard, Educator</td> <td></td> <td> </td> <td> </td> <td> </td> <td></td> <td> </td> <td> </td> <td> </td> <td> </td> <td></td> <td></td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">6</td> <td>Bryn Mawr, PA 19010-2859</td> <td></td> <td> </td> <td> </td> <td> </td> <td></td> <td> </td> <td> </td> <td> </td> <td> </td> <td></td> <td></td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">7</td> <td>$500.00 5/2/2007</td> <td></td> <td> </td> <td> </td> <td> </td> <td></td> <td> </td> <td> </td> <td> </td> <td> </td> <td></td> <td></td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">8</td> <td>Mr. Alan Bowser, Financial Services</td> <td></td> <td> </td> <td> </td> <td> </td> <td></td> <td> </td> <td> </td> <td> </td> <td> </td> <td></td> <td></td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">9</td> <td>Bedford, NY 10506-1521</td> <td></td> <td> </td> <td> </td> <td> </td> <td></td> <td> </td> <td> </td> <td> </td> <td> </td> <td></td> <td></td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">10</td> <td>$2,500.00 5/2/2007</td> <td></td> <td> </td> <td> </td> <td> </td> <td></td> <td> </td> <td> </td> <td> </td> <td> </td> <td></td> <td></td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">11</td> <td>Mr. Alan Sandman, Physician</td> <td></td> <td> </td> <td> </td> <td> </td> <td></td> <td> </td> <td> </td> <td> </td> <td> </td> <td></td> <td></td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">12</td> <td>Philadelphia, PA 19103-6607</td> <td></td> <td> </td> <td> </td> <td> </td> <td></td> <td> </td> <td> </td> <td> </td> <td> </td> <td></td> <td></td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">13</td> <td>$1,000.00 5/2/2007</td> <td></td> <td> </td> <td> </td> <td> </td> <td></td> <td> </td> <td> </td> <td> </td> <td> </td> <td></td> <td></td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">14</td> <td>Mr. Bruce Foulke, CEO</td> <td></td> <td> </td> <td> </td> <td> </td> <td></td> <td> </td> <td> </td> <td> </td> <td> </td> <td></td> <td></td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">15</td> <td>Philadelphia, PA 19115-1603</td> <td></td> <td> </td> <td> </td> <td> </td> <td></td> <td> </td> <td> </td> <td> </td> <td> </td> <td></td> <td></td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">16</td> <td>$500.00 5/2/2007</td> <td></td> <td> </td> <td> </td> <td> </td> <td></td> <td> </td> <td> </td> <td> </td> <td> </td> <td></td> <td></td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">17</td> <td>Mr. Bruce Koch, CPA</td> <td></td> <td> </td> <td> </td> <td> </td> <td></td> <td> </td> <td> </td> <td> </td> <td> </td> <td></td> <td></td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">18</td> <td>Philadelphia, PA 19119-1846</td> <td></td> <td> </td> <td> </td> <td> </td> <td></td> <td> </td> <td> </td> <td> </td> <td> </td> <td></td> <td></td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">19</td> <td>$500.00 5/2/2007</td> <td></td> <td> </td> <td> </td> <td> </td> <td></td> <td> </td> <td> </td> <td> </td> <td> </td> <td></td> <td></td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">20</td> <td>Mr. Cameron MacTavish, Architect</td> <td></td> <td> </td> <td> </td> <td> </td> <td></td> <td> </td> <td> </td> <td> </td> <td> </td> <td></td> <td></td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">21</td> <td>Philadelphia, PA 19144-3929</td> <td></td> <td> </td> <td> </td> <td> </td> <td></td> <td> </td> <td> </td> <td> </td> <td> </td> <td></td> <td></td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">22</td> <td>$500.00 5/2/2007</td> <td></td> <td> </td> <td> </td> <td> </td> <td></td> <td> </td> <td> </td> <td> </td> <td> </td> <td></td> <td></td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">23</td> <td>Mr. David Hayne, Manager</td> <td></td> <td> </td> <td> </td> <td> </td> <td></td> <td> </td> <td> </td> <td> </td> <td> </td> <td></td> <td></td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">24</td> <td>Philadelphia, PA 19103-6522</td> <td></td> <td> </td> <td> </td> <td> </td> <td></td> <td> </td> <td> </td> <td> </td> <td> </td> <td></td> <td></td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">25</td> <td>$1,500.00 5/2/2007</td> <td></td> <td> </td> <td> </td> <td> </td> <td></td> <td> </td> <td> </td> <td> </td> <td> </td> <td></td> <td></td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">26</td> <td>Mr. David Lipson, President & Publisher</td> <td></td> <td> </td> <td> </td> <td> </td> <td></td> <td> </td> <td> </td> <td> </td> <td> </td> <td></td> <td></td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">27</td> <td>Bryn Mawr, PA 19010-1648</td> <td></td> <td> </td> <td> </td> <td> </td> <td></td> <td> </td> <td> </td> <td> </td> <td> </td> <td></td> <td></td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">28</td> <td>$2,500.00 5/2/2007</td> <td></td> <td> </td> <td> </td> <td> </td> <td></td> <td> </td> <td> </td> <td> </td> <td> </td> <td></td> <td></td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">29</td> <td>Mr. David Pincus, CEO</td> <td></td> <td> </td> <td> </td> <td> </td> <td></td> <td> </td> <td> </td> <td> </td> <td> </td> <td></td> <td></td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">30</td> <td>Philadelphia, PA 19106</td> <td></td> <td> </td> <td> </td> <td> </td> <td></td> <td> </td> <td> </td> <td> </td> <td> </td> <td></td> <td></td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">31</td> <td>$500.00 5/2/2007</td> <td></td> <td> </td> <td> </td> <td> </td> <td></td> <td> </td> <td> </td> <td> </td> <td> </td> <td></td> <td></td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">32</td> <td>Mr. David Stone, Higher Educatoin</td> <td></td> <td> </td> <td> </td> <td> </td> <td></td> <td> </td> <td> </td> <td> </td> <td> </td> <td></td> <td></td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">33</td> <td>New York, NY 10025-2051</td> <td></td> <td> </td> <td> </td> <td> </td> <td></td> <td> </td> <td> </td> <td> </td> <td> </td> <td></td> <td></td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">34</td> <td>$500.00 5/2/2007</td> <td> </td> <td> </td> <td> </td> <td> </td> <td> </td> <td> </td> <td> </td> <td> </td> <td> </td> <td> </td> <td> </td></tr></tbody></table>
<table style="border-style: groove; border-color: rgb(0, 255, 0); background-color: rgb(255, 252, 249); font-family: Arial; color: rgb(0, 0, 0); font-size: 10pt;"> <tbody> <tr> <td>Spreadsheet Formulas</td></tr> <tr> <td> <table style="font-family: Arial; font-size: 9pt;" border="1" cellpadding="2" cellspacing="0"> <tbody> <tr style="background-color: rgb(202, 202, 202); font-size: 10pt;"> <td>Cell</td> <td>Formula</td></tr> <tr> <td>C2</td> <td>=LEN(G2)+1</td></tr> <tr> <td>D2</td> <td>=FIND(" ",F2,C2)</td></tr> <tr> <td>E2</td> <td>=FIND(",",F2,D2)</td></tr> <tr> <td>F2</td> <td>=OFFSET($A$2,(ROW()-ROW($K$2))*3,0)</td></tr> <tr> <td>G2</td> <td>=IF(ISERR(FIND(".",F2)),"",LEFT(F2,FIND(".",F2)+1))</td></tr> <tr> <td>H2</td> <td>=MID($F2,C2,D2-C2)</td></tr> <tr> <td>I2</td> <td>=MID($F2,D2,E2-D2)</td></tr> <tr> <td>J2</td> <td>=MID($F2,E2+1,LEN($F2)-E2)</td></tr> <tr> <td>K2</td> <td>=INDEX($A$2:$A$34,MATCH($F2,$A$2:$A$34,0)+1)</td></tr> <tr> <td>L2</td> <td>=INDEX($A$2:$A$34,MATCH($F2,$A$2:$A$34,0)+2)</td></tr> <tr> <td>C3</td> <td>=LEN(G3)+1</td></tr> <tr> <td>D3</td> <td>=FIND(" ",F3,C3)</td></tr> <tr> <td>E3</td> <td>=FIND(",",F3,D3)</td></tr> <tr> <td>F3</td> <td>=OFFSET($A$2,(ROW()-ROW($K$2))*3,0)</td></tr> <tr> <td>G3</td> <td>=IF(ISERR(FIND(".",F3)),"",LEFT(F3,FIND(".",F3)+1))</td></tr> <tr> <td>H3</td> <td>=MID($F3,C3,D3-C3)</td></tr> <tr> <td>I3</td> <td>=MID($F3,D3,E3-D3)</td></tr> <tr> <td>J3</td> <td>=MID($F3,E3+1,LEN($F3)-E3)</td></tr> <tr> <td>K3</td> <td>=INDEX($A$2:$A$34,MATCH($F3,$A$2:$A$34,0)+1)</td></tr> <tr> <td>L3</td> <td>=INDEX($A$2:$A$34,MATCH($F3,$A$2:$A$34,0)+2)</td></tr></tbody></table></td></tr></tbody></table>

Excel tables to the web >> Excel Jeanie HTML 4