With Ultimate Suite for Excel

How to Transpose by Key Columns

Transpose by Key Columns is an add-in for Microsoft Excel created to transpose data in the selected range by the chosen key columns and simultaneously combine rows if needed.

The add-in searches for duplicates in key columns. If duplicate values are found, the tool transposes and/or merges data according to the options you choose.

For example:

Transpose table by key columns.

Before you start

Please note the following:

The add-in will copy all the visible cells of the selected range to the new sheet and then process them according to the options you pick.
To avoid considering empty cells as duplicates, tick Skip empty cells on Step 2 and Step 4.
The tool inserts value results, not formulas.

Start Transpose by Key Columns

On the Ablebits Tools tab, in the Transform group, click the Transpose by Key Columns icon:

Start Transpose by Key Columns for Excel.

Step 1: Select your table

On the first step, the add-in picks the entire range with your data:
Select your table to transpose.

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.

Note. If your data is formatted as a table, the add-in will always get the entire table.

Note. The add-in copies and pastes only visible cells to the new sheet.

Click Next.

Step 2: Choose key columns

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

Select key columns.

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):

Select key columns with duplicates.

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.

Tip. If you have a lot of columns in your table, you can expand the dialogue box window by dragging the bottom right corner down and to the right until you get a suitable size.

Click the Next button.

Step 3: Select columns to transpose (optionally)

On this step, tick off the columns to transpose:
Select columns to transpose.

For example:

Pick columns with the values to transpose.

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.

Note. If you do not check any columns on this step, only the first occurrences of duplicates in key columns will be preserved in the result table.

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:
Pick columns with the values to merge.

For example:
Merged values.

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: