How to Transpose by Key Columns
With Ultimate Suite for Excel
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:
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:
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:
Related links
