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

I am not understanding the second half of the instruction, "paste as values to remove the formula"?  

Here is a sample file, take a look:

https://www.dropbox.com/s/xiugcblqx8xc6h9/a4cc2820-4e0d-4e2e-b222-977df6e845cf.xlsx?dl=1

Select column H:Q

Right-click and copy

Right-click and paste as values

https://support.microsoft.com/en-us/office/delete-or-remove-a-formula-193dbbed-6fcf-4f07-9119-5acff81b89c5

Andreas.

Answer 2

Andreas,

The new instruction is clear and seems to work just fine.  Now for my last two questions.

How do I modify the formula to include both "Date and Score"?

Lastly, is there any way to "Clear the Contents" from the cells that are no longer needed once all fo the transposing is complete?  Here is a screen shot of what I mean.

Andreas, thank you for your patience and understanding.

EdGolfer

Answer 3

How do I modify the formula to include both "Date and Score"?

Too expensive. Use a Pivot Table in tabular format...

Sample file updated, take a look.

Andreas.

Answer 4

PivotTables are a good way to go.  While this is the end of the golf season, if you are going to be doing this again next year, a PivotTable is definitely the best way to go.

You can add new data after each game and with a right click on the pivot table, "refresh" to pick up the new information and recalculate.

You can also auto generate row totals/averages, ie (oops I forgot to change the column header text):

.

Do the scores relate to more than one golf course? It looks like Tommy Bahama may have played a couple of different coursed (based on "slope", what ever that is). You could add the course names to the pivottable.

.

In this example I added a course column, and filtered the output to show only "Course B"

Andreas:

Just curious, why didn't you turn the input data into an Excel Table? How did you not turn it?

Answer 5

PS, in your example data you have rows for both "bacon,shane" and "shane bacon". Andreas appears to have cleaned that up.