Parsing out Questions and Answers Into Separate Columns

So here's a follow up to last night's tip further explaining how to put a series of questions and answers (or statements and responses as in a Rule 56.1 filing) into two different columns in an Excel spreadsheet.

Once you've identified the lines which show the beginning of statements by using the Regex search demonstrated in the Tip of the Night for August 25, 2016, the next step is make a control number column to the left.

1. Just insert a new column to the left, type 1 in the first cell and then select the rest of the cells in the column and go to Home . . . Fill . . . Series. Click off the Trend checkbox, make sure the Step Value is 1, and then click OK. You'll see that a list of sequential numbers is generated.

2. In column A we have the control number. In column B the text we're analyzing. Column C holds the results of the Regex search for the numbered lines, and in column D we have entered the beginning of the responses by, in this example, simply filtering on column B for Begins with . . . 'Response: '., or using =IF(LEFT(B4,10)="Response: ","Response","") .

3. Now filter on column C for just the numbered entries (check off Blanks) and enter 'STATEMENT' in column C. Type the entry in the first cell and then highlight the rest and press CTRL + D.

4. In column E enter this formula:

=IF(D2="STATEMENT","STATEMENT",IF(D2="RESPONSE","RESPONSE",E1))

Pull it down using CTRL + D and you'll see that it indicates which lines are statements (or questions), and which are responses (or answers).

5. Paste the data in columns C, D, and E as values, and then sort by column E and then by A.

6. In cell F2 enter this formula and pull it down for all of the question/statement lines:

=IF(D2="",F1&CHAR(10)&B2,B2)

You'll see that it's collecting the lines for each statement line by line and then resetting when it gets to the beginning of a new statement.

7. Now in column G we're simply going to tag the entries in column F which have the complete statements or responses by entering this formula:

=IF(OR(D3="Statement",D3="Response"),"tag","")

in G2 and pulling it down. Be sure to key in the tag for the last row.

9. Finally we simply paste the values in column F and G and then sort by column G and A. You can copy out the complied entries in column F, and the designations in column E which show what is a statement and what is response. Sort them and cut & paste to line them up.