How To Transpose / Convert Multiple Rows Into Single Row without VBA and Array Formula

How To Transpose / Convert Multiple Rows Into Single Row without VBA and Array Formula

If you are looking for a solution to Transpose / Convert Multiple Rows Into Single Row without VBA and Array Formula then you have landed on the right page.

Sometimes you just need to apply a simple formula as other functions won’t suffice the purpose including pivot tables also.

In this excel tip, you will learn a combination of  Index, Offset, Match and Countif.

Here is the solution

Assuming you have a dataset in “sheet1” and you want to Transpose / Convert Multiple Rows Into Single Row without VBA and Array Formula into “sheet2” or Maybe on the same sheet.

You can download the sample excel file, the download link is given below

Sheet 1 Image

How To Transpose or Convert Multiple Rows Into Single Row without VBA and Array Formula

# Step 1 – Go to “Sheet 1” and select the range

# Step 2 – Sort by Employee ID or Employee Name in (A to Z) order, go to  Data>>Sort or press “Alt+d+sfrom the keyboard 

#Step 3 – Go to “Sheet 2” or the range where you want to covert your data into a single row (we have taken sheet2 in this example).

You need to check in your data what is the maximum possibility of multiple rows for the unique data type. In our data, we have taken employee id as a unique record and it has the maximum repeat of  3, hence we have created 3 set of columns for multiple rows to be transposed into 1 single row. It is a one time exercise. You can check the maximum possibility by applying the Countif formula in the data range.

Formula –

=COUNTIF($C$2:$C$15,C2)
  • Apply the above formula in “Sheet 1” 
  • copy the formula to the last in the column “Employee ID”
  • Filter a range to see the maximum repeats
  • Use that max repeat number to create column sets in the “Sheet 2”

#Step 4 – Apply the below formula in “sheet2” in Cell “D3”

Make sure you have unique records in “sheet2” for the column which you want to use it for extracting the data. Our unique column is “Employee ID”

=IF(COUNTIF(sheet1!$A$2:$A$15,$A3)>0,OFFSET(INDEX(sheet1!$A$2:$F$15,MATCH($A3,sheet1!$A$2:$A$15,0),MATCH(D$2,sheet1!$A$1:$F$1,0)),0,0),"")

Remember this is not an Array formula so you don’t need to press “Ctrl+Shift+Enter” again and again.

#Step 5 – Copy the formula to the right cell until the last column

Sheet 2 Image

How To Transpose or Convert Multiple Rows Into Single Row without VBA and Array Formula 2

#Step 6 – Increase the red highlighted value in the Countif section by 1 for each data set column

For example – for the first record we are assuming there is only one record in the data set. For the second record, there is more than 1 record, then more than 2 records

#Step 7 – Increase the green highlighted value in the Offset section by 1 for each data set column

For example – for the first record we are assuming there is only one record in the data set. For the second record, there is more than 1 record, then more than 2 records

#Step 8 – Select the first row (only formula cells) and then copy the formula until the last row (no need to change anything).

Download Sample Excel File (84 downloads)

 

COMMENTS