Blue Flower

Transpose Multiple Rows and Multiple Columns in Same Row under the Condition

In this Excel tutorial I will show how we can Transpose the Columns to One Row if there are multiple rows in the Duplicate Row Headers that contain duplicate row headers and Transpose all values or text from multiple columns into one row.

In the picture below you see, the situation of our task as a solution Transpose Multiple Columns into Same Row. In column "A" there are Row of Headers. These Row headers can be duplicated, but the 'A' column may also contain empty cells.

In other columns 'B, C and D' are values or text. In the relevant columns there are also Empty Cells. Our goal is to Return Unique Row Headers data and the corresponding value or text from multiple columns if the condition from the 'A' column is met.

So if we take for example, the 'data1' header that appears in multiple rows and contains some values or text in adjacent columns. For this condition in the second 'B' column we have the letter 'a', in the third column 'C', this header also belongs to the letter 'b', in the fourth 'D' column, the header of the order as a condition, belongs to the letter 'c'.

We want to create a formula that will copy across and bottom, return the relevant data for the condition but from different rows.

Transpose multiple columns into multiple rows

Transpose multiple columns in a single row, if the condition is met row header

The solution to this task can be performed in at least two ways. To begin with, we have one formula that will return unique data from the 'A' column.

ARRAY Formula (CSE formula) in 'G2' cell follows the below. (This formula displays Unique Row Headers data. Copy a formula according to below).
=IFERROR(INDEX($A$2:$A$18;MATCH(0;INDEX(COUNTIF($A$2:$A$18;"<"&$A$2:$A$18)-SUMPRODUCT(COUNTIF($A$2:$A$18;G$1:G1)););0));"")

To display all the data from multiple columns in one row, we can use at least two Excel formulas.

The first way to solve a task.

ARRAY Formula (CSE formula) in 'H2' cell follows the below. (This formula is based on the cell 'G2' condition and returns the value or text found in the adjacent 'B' column. If we use this formula below then we need to consider the following limitations: In column A, there must not be empty cells, in adjacent columns 'B, C and D' in at least one unique header of the same order, there must be at least one data in each column. So this formula is not recommended because it has limitations).
=IF($G2="";"";IFERROR(INDEX(B$2:B$18;IFERROR(MATCH(IFERROR(INDEX(B$2:B$18;MATCH(0;IF(LEN(B$2:B$18);COUNTIF(H$1:H1;B$2:B$18);1);0));"");B$2:B$18;0);0));""))

Finally, we come up with a simple formula that solves all the problems. There is no limit on empty cells in the 'A' column, as well as no cell fill limit in other columns.

Another way to solve the task.

ARRAY Formula (CSE formula) in 'H2' cell follows the below. (This simple INDEX/MATCH formula returns all data from multiple columns in one row if the condition in the 'G' column is met. So regardless of the fact that there are multiple rows of duplicates, the formula searches for each relevant data from multiple adjacent columns and returns them to one row when we copy the formula to the right. The formula searches for the condition of the row header in the 'A' column and multiplies it with each data that is greater than zero in the adjacent multiple columns and returns the number of rows in which the data is located. To make it clearer, use the 'F9' key and Evaluate formula to see the flow of calculations. Copy the formula across and down).
=IFERROR(INDEX(B$2:B$18;MATCH(1;($A$2:$A$18=$G2)*(B$2:B$18>0);0));"")

I hope that I'm somewhat, to explain the workings of these two formulas for Transpose multiple columns in multiple rows, if one condition is met.

Please note that you must correctly set absolute and relative addresses in Excel formulas, especially if you copy the formula.

Finally, in the attachment of this tutorial, you have the Download file option on which I did this tutorial.

Pay attention, you may need as a delimiter of arguments formula use a comma (,) instead of a semicolon (;).
Copy formula to Notepad and with Find/Replace change semicolon to comma.