-1

I have a set of multiple choices questions with ABCD as answers, all answers are stacked on a column, but not every question has the same amount of answers.

Instead of transposing every nth value, my sheet has a random number of rows with answers(2-5), and between those data are a random amount of empty rows(2-3). How do I transpose all of those rows to clean columns?

basically from original col to sorted

1
  • Filter columns?
    – Lee
    Nov 30, 2022 at 7:41

3 Answers 3

0

Given that your questions do not have a set amount of answers and the fact that empty lines vary between 1 and 2 between questions, you have to match the nth instance of A-E.
I'd prefer to go with offsets, but with your setting, this is done by an array formula, that's nicely described here.

I don't see the coordinates for your cells, so I'll be referring my replica of your workbook: enter image description here

  • In column A I have ABCD choices, empty lines and some text in-between them.
  • In column B I have the actual answers to choices.
  • Column C is used for a question number.
  • Columns DEFGH are the choices as per your second screenshot.

The fun part comes below the last mentioned columns. Formula will be like: {=INDEX($B:$B,SMALL(IF(D$1=$A:$A,ROW($A:$A)-ROW($A$1)+1),$C2))} It should be typed with Ctrl+Shift+Enter, as it's an array formula. Once typed you may copy-paste it to the other cells.

You'll have to readjust this formula for you workbook. There aren't many variables here:

  • $B:$B — question answers.
  • $A:$A — used twice in this formula. Responds to your first screenshot and first column, the choices keys.
  • $A$1 — first cell in the above mentioned column that will be shifted.
  • $C2 — question numbers frozen at column.
  • D$1 — choices in the header just as on your second screenshot.
0

This can also be accomplished using Power Query, available in Windows Excel 2010+ and Excel 365 (Windows or Mac)

To use Power Query

  • Select some cell in your Data Table
  • Data => Get&Transform => from Table/Range or from within sheet
  • When the PQ Editor opens: Home => Advanced Editor
  • Make note of the Table Name in Line 2
  • Paste the M Code below in place of what you see
  • Change the Table name in line 2 back to what was generated originally.
  • Read the comments and explore the Applied Steps to understand the algorithm

M Code

let

//Change next line to reflect actual data source
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],

//Change next line to reflect actual Column Names
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}}),
    #"Removed Blank Rows" = Table.SelectRows(#"Changed Type", 
        each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null}))),

//Split column 1 into two columns to separate questions and the answer letter
    newTable = Table.FromColumns(
        {List.Transform(#"Removed Blank Rows"[Column1], each if List.Contains({"A".."Z"}, _) then null else _ )} &
        {List.Transform(#"Removed Blank Rows"[Column1], each if List.Contains({"A".."Z"}, _) then _ else null)} & 
        {#"Removed Blank Rows"[Column2]},
        type table[Question=nullable text, Answer Letter=nullable text, Answer text=nullable text]),

//Fill down the Questions column and remove the rows containing nulls in the Answer letter column
    #"Filled Down" = Table.FillDown(newTable,{"Question"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Down", each ([Answer Letter] <> null)),

//Pivot on the Answer letter column with no aggregation
    #"Pivoted Column" = Table.Pivot(#"Filtered Rows", List.Distinct(#"Filtered Rows"[#"Answer Letter"]), "Answer Letter", "Answer text")
in
    #"Pivoted Column"

Source
enter image description here

Results
enter image description here

0

Completely agree with the solutions already given. If you still have not completed the task manually, but are waiting for a ready-made solution, then I will offer my own version.

Turn on macro recording.

Specify the "hot key" you will use to launch (for example, Ctrl-g)

Start pressing the buttons in a sequence that should lead you to copy and transpose one group of answers in the right place:

Ctrl-Home, 
Right Arrow, 
Ctrl-Down Arrow, 
Ctrl-Shift-Down Arrow (the first group of questions is highlighted), 
Ctrl-C (copy to clipboard), 
Ctrl-End, 
Ctrl-Left Arrow, 
Ctrl-Alt-V (specify "transposed"), 
Enter, 
and again Ctrl-Home, 
Right Arrow, 
Ctrl-Down Arrow, 
Ctrl-Shift-Down Arrow 
and Delete (the first group of questions is deleted).

Stop macro recording.

You may need to change one or two lines of code to select first empty cell at the end of table. Somethig like as

    ActiveCell.SpecialCells(xlLastCell).Select
    Range("A" & (Selection.Row + 1)).Select

I hope that the nickname you have chosen indicates that this will not be a problem for you.

Now press Ctrl-g until you reach the end of the list. Column B is now cleared and all the answers from it are collected in the table at the bottom of the page.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .