Organizing data within a formatted irregular list

[jaakovs]

Hello Mr. Excel!

I've got questionnaire data in an Excel workbook that's extensively formatted and does not follow a regular order. I'm working to create a clean master list of that data so that I can run PivotTables and create graphs and such. Here is an example of what my source data looks like:

For my clean master list, I need to pull each of the questions into one column (but not any subquestions - crossed out), the associated score with each question (circled in red), and then decode the columns to the left into adjacent columns in my master list according to corresponding text strings (circled in green). Each question has a unique, consecuritve number associated with it (circled in yellow).

I've experimented with vlookup and index+match, as well as with a nested IF, OR, and INDEX formula, which takes advantage of the fact that each row with the questions I need to pull has that "P" letter to the left of it, or an S, or an I. But none of these approaches produces a consistent result because the questions are not x amount of rows apart from each other, etc. What do you suggest?

Thanks!

 

[inactiveuserps07]

I may be misunderstanding the goal, but it seems to me that if this were a table, you could just use sorting to do what you want:

1. Be sure the entire form range is a table.

2. Sort the column that has "P / S / I" in descending order.

3. Scroll down to the first row that is blank for that column. Select all rows from there down and delete them.

4. Clear sorting on the "P / S / I" column.

5. Sort the yellow numbers column in ascending order.

In my mind, this works to give you what you want.

You can then create uniform row heights, remove any column data you don't want and otherwise format as you wish for the new mast list.

 

[jaakovs]

Hi ErikTyler,


Thanks for your response! I think your solution worked for what I'm trying to do. Quick follow-up question: Is there an easy way for me to replace the codes (circled in green in my original post) with a corresponding list of clear-text equivalents? Would vlookup or index+match suffice for this?

 

[inactiveuserps07]

Hi, jaakovs. That is a quick question. However, it's not such a quick answer.

There is an "easy" way to replace your codes with plain-text equivalents; but it's not necessary quick to explain. I'll do my best for you. For the sake of ease in explaining, I'll assume that your codes on the main sheet are in Columns A, B, C, D, E, F (since they are not visible in your screen shot above) and that the first entry begins in row 2.

1. Double-click on an unused tab name (e.g., "Sheet2," etc.) and change the name of that tab to "Legend."

2. Go to the "Legend" tab and create some column headers in A and B, perhaps "ORIGINAL" and "PLAINTEXT" (it doesn't really matter what they are).

3. Complete that legend, placing all original codes used in the sheet into Column A and your corresponding equivalents into Column B.

4. Go back to your main sheet. Click-and-hold the "H" at the top of Column H, drag to the "N" at the top of Column N and release. This will select all columns from H to N. Right-click one of the column letters, and click "Insert." New columns will be inserted.

5. Enter the following formula into cell H2: =IFERROR(INDIRECT("Legend!"&ADDRESS(MATCH(A2,Legend!$A$2:$A$200,0)+1,2)),IF(A2="","","MISSING"))

(Concerning the above formula, if your first entry of codes doesn't begin in A2, that you change the highlighted "A2" parts of the formula to reflect where your first code entry actually lies.)

6. Once that formula is entered, be sure that the cell is selected. You'll see a heavy black border around it, with a small black square in the lower right corner of the cell. Click on that little square, hold, and drag across the row to Column M. Release. The formula will be copied.

7.Now the heavy black border will be around H2:M2. Click the little lower-corner box, hold and drag DOWN until you reach the last row that has codes in A:F. Release. The formula will be copied down.

If you've already filled in the "Legend" tab with values, you should see them filled in now.

Any entries that couldn't find a match in your "Legend" tab will say "MISSING."

I'll give you some steps to easily pick those out:

1. Go to your main data tab.

2. Be sure your "Home" tab is active on the ribbon.

3. Click-hold-drag the column letters to select Columns A, B, C, D, E, F. They should be highlighted.

4. From the ribbon, choose: Conditional Formatting > "New Rule"

5. Under "Select a rule type:" choose "Use a formula to determine which cells to format." Under "Edit the rule description:" enter this formula:

=INDIRECT(ADDRESS(ROW(),COLUMN()+7))="MISSING"

6. Beside Preview: click the [Format ...] button. Select the "Fill" tab. Choose a fill color (e.g., yellow).
7. Click "OK" ... "OK" to accept the rule.

Now we're going to repeat the process on the results cells.

1. Click-hold-drag the column letters to select Columns H, I, J, K, L, M. They should be highlighted.2. From the ribbon, choose: Conditional Formatting > "New Rule"

3. Under "Select a rule type:" choose "Format only cells that contain." Under "Edit the rule description:" enter the following values: Cell Value ... equal to .... and enter the following formula into the last box: ="MISSING"

4. Beside Preview: click the [Format ...] button. Select the "Fill" tab. Choose a fill color (e.g., yellow).
5. Click "OK" ... "OK" to accept the rule.Now, any corresponding cells that didn't find a match in "Legend" should be highlighted yellow. Once you add those values to the legend in the "Legend" tab, the highlighting will disappear.


Once you are satisfied with all results, I'd recommend KEEPING the originals, in case you or someone else needs them for reference later. Just HIDE the columns, and you can UNHIDE them later if you need to.

1. Click-hold-drag the column letters to select Columns A, B, C, D, E, F, G.

2. Right-click your mouse on any column-letter and choose "Hide" from the drop-down list. The original code columns will be hidden. (If you ever need to Unhide them, click the column-letter for the first column of plain-text replacements, hold the mouse down, and drag left. Release. Right click the column letter and choose "Unhide.")

Hope that helps.

 

[inactiveuserps07]

One more thing ...

If you want the actual TEXT of the results instead of the FORMULA that results in that text (once your plain-text conversions are complete), you can do a Paste-Special:

1. Select the entire range of cells that contains the formulas that produced the plain-text results.

2. Hit Ctrl-C to copy this to the clipboard.

3. White the selection is still active, hit Ctrl-Atl-V to open the Paste dialog. Select "Values" and click "OK."

 

[jaakovs]

Hello ErikTyler! I just wanted to check in and seriously thank you for your assistance on making this work. Apologies for letting this thread slide. I was able to create a master list and your assistance was invaluable!

I've got another similar procedural question for you - or anyone else on this awesome forum! What would you suggest for a procedure for extracting the data in my source document, pictured below first, into a specific destination formatting, pictured second below? (For now, we can forget about response 2, response 3, and so on). I'm struggling to figure out a procedure that would allow me to do that easily. The source data consists of umbrella questions (as in our earlier workbook) identified by a unique question number on the column to the left of the question and a varying number of subquestions. The score of the umbrella question (on a scale from 1 to 4) reads from the answer choices of "Yes" and "No" given to each subquestion.

The formula that makes that work is =IF(I31="N/A","...",IF(I31="No",1,1.8+COUNTIF(I33:I35,"Yes")*2.2/COUNTA(I33:I35))), for example.

I'm looking to create a master list just for this data while preserving the subquestions and the structure that sets the scores for the umbrella questions.

Source:

And destination:

Do you have any ideas on how to do this?