How to Transpose by Key Columns

Step 1: Select your table

On the first step, the add-in picks the entire range with your data:

• To expand the selection into the current table or select a different range, choose the corresponding icon on the right side of the Select your table box.
• You can also select the records right in the worksheet, the add-in will pick up your selection.
• Another option is to type the range address in the Select your table field manually.

Click Next.

Step 2: Choose key columns

On this step, you can see a list of the columns your range contains:

Pick one or more key columns—the columns which contain duplicate values. Data will be transposed and merged according to the repeated values in the key column(s):

If you select more than one column, a record will be considered duplicate if values in all the selected key columns are the same.

• If you have header rows, tick off the My table has 1 header row checkbox at the top and change the number of header rows if necessary. If you do not have labels, look at 1st row content to understand what kind of data the columns contain.
• Select the Skip empty cells option if you have blanks in your key columns. Clear the checkbox to consider blank values as duplicates.
• Take advantage of the Select All and Unselect All buttons if you have a long list of columns.

Click the Next button.

Step 3: Select columns to transpose (optionally)

On this step, tick off the columns to transpose:

For example:

• Check the Remove duplicates box to avoid including duplicate values into the transposed table.
• Tick the Skip empty cells option if you don't want blank cells to be processed as duplicates.
• Take advantage of the Select All and Unselect All buttons if you have a long list of columns.

Click Next.

Step 4: Pick columns with the values to merge (optionally)

On this step, you can select the columns with the entries to merge:

For example:

• Check Delete duplicate values if the records you need to combine may contain identical items but you want to keep only unique values in your results.
• Select the Skip empty cells option to avoid considering empty cells as duplicates.
• To use the same delimiter in each column, click the Choose delimiter drop-down list at the top. You can either enter your own separator or select one from the predefined options. Standard delimiters include a semicolon, comma, period, space, and a line break. Besides, you can select one of the following aggregate functions: AVERAGE, COUNT, COUNTA, MAX, MIN, PRODUCT, STDEV.P, STDEV.S, or SUM:
  
  Note. The tool uses the same calculation algorithm as the standard Excel functions (SUM, AVERAGE, COUNT, COUNTA, MAX, MIN, PRODUCT, STDEV.P, STDEV.S), so if you are new to this topic, please consult a corresponding section here.
• If you wish to have different delimiters for each column, enter or select the needed ones in the Delimiter box next to the column name:
  

Click Finish to get the result transposed table on the new sheet: