Moving data from a column to a row

Question

I have a spreadsheet that contains probably 4000+ line items.  Each item is listed with a name and may have as many as 20 line items under the same name.  I would like to move a column of numbers associated with the same name into a horizontal set of cells that will eliminate the need for multiple line items under the same name.

Answer 1

Hi Ed,

To achieve your requirement, you can use TRANSPOSE function. The TRANSPOSE function returns a vertical range of cells as a horizontal range, or vice versa. The TRANSPOSE function must be entered as an array formula in a range that has the same number of rows and columns, respectively.

For detailed steps, see TRANSPOSE function

Let me know if you need any help. Have a nice day and stay safe😊

Regards,

Neha

Answer 2

H4:Q4:  {=IFERROR(TRANSPOSE(OFFSET(C4,0,0,IF(A3<>A4,COUNTIF(A:A,A4),""))),"")}

drag down

Note:

The {} means this is an array formula.

Copy the formula without the {} into the formula bar and press CTRL-SHIFT-ENTER

(If you use a Mac it's COMMAND+RETURN)

After that the {} appears around the formula.

Copy H:Q and paste as values to remove the formula

Home \ Find & Select \ Go To Special \ check Constants and Errors \ Ok

Press DEL to remove the errors

Select a cell inside the data e.g. A3 and apply and Autofilter

Filter column column H to show blanks only

Select all visible rows \ Right-click \ Delete rows

Remove the Autofilter

Done

Andreas.

Answer 3

Neha,  TRANSPOSE works great.  Thank you for the solution.

Now I have another question.  I have about 300 different names with various line items (scores) that need to be transposed.  To write 300 formulas is not doable in a short period of time.  Is that a way to drag and drop the TRANSPOSE formula that would make it easier and cut down on the time necessary to complete 300 entries?

Regards,

EdGolfer

Answer 4

Andreas,  Thank you for your prompt response.  I am working with another solution and if it doesn't work out I'll shift to your solution.

Thanks for taking the time to respond to my problem.

Regards,

EdGolfer

Answer 5

Hi Ed,

Drag and drop will not work here. If you consider, there is another way, a formula free method.

So, instead of using TRANSPOSE function suggested in my last post, we can try Transpose option from Paste drop-down.

1. Select the cells that you want to transpose into rows.
2. Copy the cells. You can do this by either clicking Edit from the menu and selecting Copy or using Command + C on your keyboard.
3. Go to and click the cell where you want to paste your data.
4. Select the arrow next to the Paste button in your ribbon and choose Transpose.

If you would like to apply additional formatting at that time, you can select Paste Special from the Paste menu instead. This will open a window where you can select your formatting options. Then, click the Transpose checkbox when you finish and hit OK.

Reference: Transpose data from rows to columns (or vice versa) in Excel for Mac

Let me know if this works for you.

Regards,

Neha