I have an Excel file with more than 500 rows of data arranged across three columns (A-C). I would like to move some of the rows from A-C to new columns depending on the values in column A. Is there a formula to do this?
Before
| A | B | C |
|---|---|---|
| 1 | 51 | 81 |
| 1 | 61 | 91 |
| 2 | 52 | 82 |
| 2 | 72 | 92 |
| 3 | 53 | 83 |
| 3 | 73 | 93 |
After
| A | B | C | A | B | C | A | B | C |
|---|---|---|---|---|---|---|---|---|
| 1 | 51 | 81 | 2 | 52 | 82 | 3 | 53 | 83 |
| 1 | 61 | 91 | 2 | 72 | 92 | 3 | 73 | 93 |
Thank you.
To achieve the desired result, you can use a formula called “INDEX-MATCH” in Excel.
First, create a new column with unique values from column A. You can do this by selecting an empty column (let’s say column E) and entering the following formula in cell E2:
=IFERROR(INDEX($A$2:$A$7, MATCH(0, COUNTIF($E$1:E1, $A$2:$A$7), 0)), "")
Drag the formula down to fill all the cells in column E. This will give you a list of unique values from column A.
Next, in cell F2, enter the following formula:
=IFERROR(INDEX($B$2:$C$7, SMALL(IF($A$2:$A$7=$E2, ROW($A$2:$A$7)-MIN(ROW($A$2:$A$7))+1), COLUMN(A$1))), "")
This formula uses the INDEX function to pull the values from columns B and C based on the condition that the corresponding value in column A matches the value in column E. The SMALL and IF functions are used to handle multiple matches.
Drag the formula across and down to fill the desired range of cells. The data will be arranged as shown in the “After” table.
Note: Adjust the range references ($A$2:$A$7, $B$2:$C$7) in the formulas according to the actual range of your data.