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

John,

Thanks for pointing that out.  I caught it on 9/9 and corrected it prior tot he next communication with Andreas.

Thanks again,

EdGolfer

Answer 2

Rohn, Andreas,

I live in Arizona and our golf season never ends.  We only slow a bit for overseeing in October and then resume in November.

Normally all our play is on one 18 hole golf course, but we do have occasion where a new member will join our club and we will allow "away" scores to be used for establishing a temporary handicap.  After 3 rounds, those away scores will be eliminated and the new member will only use scores that were posted from our home course.

I don't have a need for averages or row totals.  My purpose is to display scores and dates they were posted for information to the membership only.  I am evaluating a handicap program that is lacking in reporting structure and I am trying to find an easy way to replicate a report that we use from another software provider.  Here is an example of what that report looks like. The number on the left column is what we use as a Local ID number.  The number immediately to the right of the name is the calculated handicap from the individual scores that are horizontal to the right of the handicap.  The * indicates that the score with the * is utilized in calculating the handicap.  We only use the best 3 of the latest 6 scores in computing a handicap.  I hope you see what I am attempting to do.  Lastly, I know nothing about pivot tables so I will take your examples and  see if I can learn something about them.

Regards, EdGolfer

Answer 3

Pivot Tables are really cool. They allow you to automate generating formulas to create many different summary reports.

.

If you want a brief intro, take a look at this free webinar being offered this week

https://answers.microsoft.com/en-us/msoffice/forum/all/fyi-free-webinar-about-excel-powerquery-powerpivot/09a19e1e-0109-4e19-b4d2-cc0253ca71e2

For example you can create a "top n" filter. So you could use that feature to automate your calculations

! Advanced Pivot Table Tricks for you2020 02 27    Chandoo

https://chandoo.org/wp/advanced-pivot-tables/

Excel Pivot tables make data analysis and visualization easy. With the help of these advanced pivot table skills, you can create powerful data analytics and reports. 

Table of Contents

.  *  #7 - Top 10 Filter

although you would have to do a "double top" process, first find the 6 most recent dates for the player, then find the top 3 scores in that top 6 dates ... don't ask, I haven't reached that point myself <g>

If you want to go a head and start learning more about these "power tools" this web article has links to many free webinars and articles to help you learn

Many of these webinars are shills for courses, but that's OK. The preceding material is still great, and investing in one of these courses would not be a bad thing to do (me, I'm under-employed, so I can't ... )

Power Tool Courses – Wiki – Learn

.

*** Wiki with more specific information and examples *** .

The following Wiki has a collection of links for the various “advanced” Excel “Power” tools. There links to free webinars, short articles and a few free and paid ebooks.

.

I like to download the webinar replay recordings for future reference, and download the example workbooks.  If you are gung ho, you could add time references into the workbook back to the recorded session to make future cross reference easier to find specific features / instructions (I’ve been doing that).

.

These free sessions run roughly 1 hour each followed by a short shill for their related courses (which definitely sound worth the price).

.

Wiki: Get Started with Get & Transform > Tables > PivotTables > PivotCharts > Dashboards ... – PivotTable Wiki

https://answers.microsoft.com/en-us/msoffice/forum/msoffice_excel-mso_win10-mso_o365b/get-started-with-get-transform-tables-pivottables/fea2f31d-7835-4f77-ad34-6fe3e6d07620

Answer 4

Rohn.  I started to watch the Webinar but stopped after 30 minutes or so.  It was above my head and really wasn't aoolicablle to what I am trying do.

I did dabble around with Pivot tables and was able to achieve something that looks like what I am trying to do, but I could not manipulate the columns correctly.

Lastly, I have put together a "Before" and "After" data sheet for you to look at.  Notice that the dates are most recent first to let in the "After" data set.

I just can't get the pivot tables to work correctly for me.

Regards,

EdGolfer

Answer 5

OK, I understand that PivotTables and those videos can get deep fast.

.

Please upload your example file so we don't have to waste time trying to extract the numbers from your screen captures.  Please include the pivot table you started with so we can see what you did.  Add notes on the sheet to explain what you don't like about your attempt

UPLOAD EXAMPLE - TROUBLE SHOOTING - SHARE PERSONAL ONEDRIVE FILE (NOT BUSINESS ONEDRIVE)

.

Trouble shooting problems in files can be like a visit to the dentist, a long, slow painful process of us trying to “extract” the clues needed to recognize the problem in a back and forth flow of questions and answers so that we can come up with a / “the” solution.

.

Often it is faster and easier for everyone if we have a “sample file” get "hands on", to look at, and to “play with”.

.

This next link provides some tips on setting up a sample file and specific instructions for uploading and "sharing" it for us to access:

.

https://answers.microsoft.com/en-us/windows/forum/windows_other-winapps/trouble-shooting-share-onedrive-file/a231a097-bcbf-4e34-ad6c-a33118baf471

**************************************

.

That article links to macros to randomize text in Word and numbers in Excel

.

This article describes another way of sharing Office files:

2020 03 09- Share a cloud stored document from Microsoft Office

https://office-watch.com/2020/share-cloud-stored-document-microsoft-office/

Using “Invites” to specific people from inside Office apps. Modify document access permissions and add a “note” with “how to” instructions for recipient

.

**************************************

.

The first 2 minutes of this video gives an example of What I mean by a “simple” example. Use short simple names and quantities, and just 2 or 3 rows per sample data you want calculations done on.  You want simple numbers so you can do the math checks in your head.

Col- **Stacking Columns of Data (Unpivot, Split Columns by delimiter, and everything!)****(PowerQuery)******2017 04 16

http://ozdusoleil.com/2017/04/16/power-query-stacking-columns-of-data-unpivot-split-columns-by-delimiter-and-everything/

We’ve got headers and a large number of paired columns that need to be condensed down to just 2 columns. 

.

**************************************

.