How to Move All Data from Selected Rows into the Top Most Selected Row in Excel Formula

Sometimes, you may have a data set in Excel where each row contains a single data point for a specific category, and you want to consolidate all the data points into one row per category. For example, you may have a table like this:

And you want to transform it into a table like this:

One way to do this is to use Excel formulas to move all the data from the selected rows into the top most selected row for each category. This can be useful if you want to avoid using macros or manual copy and paste.

Procedures

To move all the data from the selected rows into the top most selected row in Excel formula, you can follow these steps:

1. Select the entire data range that you want to transform, including the headers. In this example, the data range is A1:F11.
2. Go to the Formulas tab in the ribbon, and click on Define Name. This will open the New Name dialog box.
3. In the Name box, enter a name for your data range, such as Data. In the Refers to box, enter the address of your data range, such as =Sheet1!$A$1:$F$11. Click OK to create the name.
4. In a new worksheet, enter the headers for your transformed table in the same order as the original table. In this example, the headers are Category, A, B, C, D, and E, and they are entered in A1:F1.
5. In cell A2, enter the following formula: =INDEX(Data,MATCH(0,COUNTIF($A$1:A1,Data[Category]),0),1). This formula will return the first unique value in the Category column of the Data range. Press Ctrl + Shift + Enter to enter the formula as an array formula. You should see curly braces {} appear around the formula in the formula bar.
6. Drag the formula down until you get an error value, such as #N/A. This means that there are no more unique values in the Category column. In this example, the formula is dragged down to A3.
7. In cell B2, enter the following formula: =IFERROR(INDEX(Data,MATCH($A2,Data[Category],0),MATCH(B$1,Data[#Headers],0)),""). This formula will return the data point for the category in column A and the header in row 1, if it exists, or a blank value, if it does not. Press Ctrl + Shift + Enter to enter the formula as an array formula.
8. Drag the formula down and to the right, until you cover the entire data range. In this example, the formula is dragged down to A3 and to the right to F3.
9. You should now see the transformed table in the new worksheet, with all the data points moved to the top most row for each category.

Explanation

The formulas used in this method are based on the following concepts:

• The INDEX function returns a value from a range or an array, based on a given row and column number. For example, =INDEX(Data,2,3) returns the value in the second row and third column of the Data range, which is 2 in this example.
• The MATCH function returns the relative position of a value in a range or an array, based on a given match type. For example, =MATCH("X",Data[Category],0) returns the position of the first occurrence of “X” in the Category column of the Data range, which is 1 in this example. The match type 0 means an exact match.
• The COUNTIF function counts the number of cells in a range or an array that meet a given criterion. For example, =COUNTIF(Data[Category],"X") counts the number of cells in the Category column of the Data range that contain “X”, which is 5 in this example.
• The IFERROR function returns a value if there is no error, or another value if there is an error. For example, =IFERROR(1/0,"Error") returns “Error” because 1/0 is an error.

The formula in cell A2 uses the INDEX and MATCH functions to return the first unique value in the Category column of the Data range. It works as follows:

• The expression COUNTIF($A$1:A1,Data[Category]) returns an array of numbers that represent how many times each value in the Category column of the Data range has appeared in the range $A$1:A1. For example, the first element of the array is COUNTIF($A$1:A1,"X"), which is 0 because “X” has not appeared in $A$1:A1 yet. The second element of the array is COUNTIF($A$1:A1,"X"), which is 1 because “X” has appeared once in $A$1:A1, and so on.
• The expression MATCH(0,COUNTIF($A$1:A1,Data[Category]),0) returns the position of the first occurrence of 0 in the array, which means the first unique value in the Category column of the Data range. For example, the first time the formula is entered, it returns 1, which is the position of “X” in the Category column. The second time the formula is dragged down, it returns 6, which is the position of “Y” in the Category column.
• The expression INDEX(Data,MATCH(0,COUNTIF($A$1:A1,Data[Category]),0),1) returns the value in the Data range, based on the row number returned by the MATCH function and the column number 1, which is the Category column. For example, the first time the formula is entered, it returns “X”, which is the value in the first row and first column of the Data range. The second time the formula is dragged down, it returns “Y”, which is the value in the sixth row and first column of the Data range.

The formula in cell B2 uses the INDEX, MATCH, and IFERROR functions to return the data point for the category in column A and the header in row 1, if it exists, or a blank value, if it does not. It works as follows:

• The expression MATCH($A2,Data[Category],0) returns the position of the value in cell A2 in the Category column of the Data range. For example, if cell A2 contains “X”, it returns 1, which is the position of the first occurrence of “X” in the Category column.
• The expression MATCH(B$1,Data[#Headers],0) returns the position of the value in cell B1 in the headers row of the Data range. For example, if cell B1 contains “A”, it returns 2, which is the position of “A” in the headers row.
• The expression INDEX(Data,MATCH($A2,Data[Category],0),MATCH(B$1,Data[#Headers],0)) returns the value in the Data range, based on the row number returned by the first MATCH function and the column number returned by the second MATCH function. For example, if cell A2 contains “X” and cell B1 contains “A”, it returns 1, which is the value in the first row and second column of the Data range.
• The expression IFERROR(INDEX(Data,MATCH($A2,Data[Category],0),MATCH(B$1,Data[#Headers],0)),"") returns the value returned by the INDEX function, if there is no error, or a blank value, if there is an error. For example, if cell A2 contains “X” and cell B1 contains “B”, it returns 2, which is the value in the first row and third column of the Data range. However, if cell A2 contains “Y” and cell B1 contains “B”, it returns a blank value, because there is no value in the sixth row and third column of the Data range.

 Example

To illustrate how this method works, let’s use a scenario where we have a table of sales data for different products and regions, and we want to move all the data from the selected rows into the top most selected row for each product. Here is the original table:

We want to transform it into a table like this:

To do this, we can follow the same steps as before, with some minor changes:

1. Select the entire data range that you want to transform, including the headers. In this example, the data range is A1:F9.
2. Go to the Formulas tab in the ribbon, and click on Define Name. This will open the New Name dialog box.
3. In the Name box, enter a name for your data range, such as Sales. In the Refers to box, enter the address of your data range, such as =Sheet1!$A$1:$F$9. Click OK to create the name.
4. In a new worksheet, enter the headers for your transformed table in the same order as the original table. In this example, the headers are Product, Region, Q1, Q2, Q3, and Q4, and they are entered in A1:F1.
5. In cell A2, enter the following formula: =INDEX(Sales,MATCH(0,COUNTIF($A$1:A1,Sales[Product]),0),1). This formula will return the first unique value in the Product column of the Sales range. Press Ctrl + Shift + Enter to enter the formula as an array formula. You should see curly braces {} appear around the formula in the formula bar.
6. Drag the formula down until you get an error value, such as #N/A. This means that there are no more unique values in the Product column. In this example, the formula is dragged down to A3.
7. In cell B2, enter the following formula: =IFERROR(INDEX(Sales,MATCH($A2,Sales[Product],0),2),""). This formula will return the region name for the product in column A, if it exists, or a blank value, if it does not. Press Ctrl + Shift + Enter to enter the formula as an array formula.
8. Drag the formula down and to the right, until you cover the entire data range. In this example, the formula is dragged down to A3 and to the right to F3.
9. You should now see the transformed table in the new worksheet, with all the data points moved to the top most row for each product.