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

A handicap in golf is used to equalize players of different abilities so that they can compete against each other.  Very good players like professionals (Tiger Woods, Phil Michelson) would be players that would have handicaps in the range of +3 to +6 or so.  Average amateurs have handicaps of 1 to 36.  The average golfer can't break 90, when 72 is what they call scratch and would yield a handicap of "0".

Instead of using "'+" I can use -3.  Is that acceptable in a pivot table?

To address the Hdcp column, having the "Sum of Hdcp"  between each set of scores doesn't make sense to me.  I would like to have a single column adjacent to the players names as in the screen shot in our conversation dated 9/15.

Can you give me an example of an Excel date format?

Rohn,

Let me digest this and see what I get with changing the +3 to a -3.  Also, if you could find a way to have the Hdcp column to the right of the Players Names, that would be great.

Thanks again Rohn,

Almost there.

Regards,

EdGolfer

Answer 2

HDCP = Handicap.  OK, I got it now.

.

• / - either is OK, you just have to make sure that Excel knows the value is a number not text.

.

Question: The handicap is always going to be the same?  It's not going to change over time?  Then it should not be on each row, it should be in a separate table ...

.

That being said, I don't how to show that value just once ... yet. I'll keep looking (I'm learning this stuff too).

.

OK, a little research, I came up with this (in the example file)

EDIT: Sorry, I don't know why the Handicap is not showing the correct sign. I formatted the field to show it, but it is not showing up.  I'll keep noodling on it ...

The date format is in part defined by you system long and short date/time formats.  If you click in one of the dates, and go to the Home tab > Number group > you'll see it is displayed a "General" format, not date ie:

In this example I entered the date in a format that MY Excel on computer recognizes as a date, ie 2020-04-11, so Excel said

"... cool, that's a date. Just to be confusing I'll display it in the default display format"

And to be even more annoying, MS stores dates as a "serial" number. So if in the example date I reset the date type to General, it shows the underlying value that Excel translates to display format:

Here endeth the sermon ...

Here are some general articles about how Excel handles dates:

! The truth behind Excel dates, time and durationhttps://office-watch.com/2018/truth-behind-excel-dates/****Date handling in Excel can be confusing and it’s not your fault.  Microsoft could do a better job. Here’s a quick guide to how Excel handles dates and date arithmetic with the settings you should know.

.  *  What you see isn’t what’s saved-----------         .  *  Excel date and times

.  *  Date and Time arithmetic--------------------         .  *  Displaying dates and times

.  *  How Windows changes Excel dates------         .  *  Time Duration in Excel

ET MR Dates and Times.docx.

! Use Date & Time values in Excel – a handy guidehttps://chandoo.org/wp/date-time-tips-ms-excel/

Excel date time features are very handy and knowing how to use Excel date values can help you save a ton of time in your day to day spreadsheet chores. Let us prepare for your date with the sheet using these 10 handy tips. So you see, Date and Time are in fact numbers in Excel. Just enter a date in your excel sheet and format it as number to see its equivalent numeric value. If a date is 20-June-2018 and excel represents it as 43271. Similarly, 9PM on 20-June-2018 is represented as 43271.875

.  *  Test whether a date is future or past

.  *  Find the number of days between two dates

.  *  Formatting dates

.  *  Auto-filling only weekdays

.  *  Find out the day of week from a given date

.  *  Highlight weekends using conditional formatting

.  *  Adding / Subtracting dates

.  *  Ensuring a valid date or time is entered in a cell

.  *  Insert today’s date, current time using key board shortcuts

.  *  Top Date functions for you

.  *  Common problems when working with dates in Excel

.

I've collected all of the various Date/Time format codes I could find into this one place

!   Custom Date / Time format codesDate CodesThe letters d, m, and y are used to represent days, months, and years in Excel date formats.

Date Mask Displays ****.  d                days (1-31) without a leading zero

.  dd              days (01-31) with a leading zero

.  ddd            days (Sun-Sat) using 3-letter abbreviations

.  dddd          days (Sunday-Saturday) full day spelled out

.  m               months (1-12) without a leading zero

.  mm            months (01-12) with a leading zero

.  mmm         months (Jan-Dec) using 3-letter abbreviations

.  mmmm      months (January-December) full month spelled out

.  mmmmm   months (J-D) the first letter of the month

.  yy               years (00-99) last two-digits of year

.  yyyy            years (1900-9999) all four-digits of the year

Personally, I avoid the mmmmm format. How can you (or anyone else reading your worksheet) distinguish between January, June, and July? Between March and May? Or April and August?

Time CodesThe letters h, m, and s are used to represent hours, minutes, and seconds in Excel time formats. Additionally, you can specify elapsed time and AM or PM in a custom format -

Time Mask Displays ****.  h                       hours (0-23)   without a leading zero

.  hh                     hours (00-23) with a leading zero

.  m                       minutes (0-59)   without a leading zero

.  mm                    minutes (00-59) with a leading zero

.  s                        seconds (0-59)  without a leading zero

.  ss                      seconds (00-59) with a leading zero

.  [h]:mm               elapsed time in hours      (such as 28:30)

.  [mm]:ss             elapsed time in minutes   (such as 65:20)

.  [ss]                     elapsed time in seconds (such as 90)

.  ss.000                Decimal seconds to thousands

.  [h]:mm:ss.000    Decimal Seconds to thousands

.  h:mm AM/PM     hours (such as 6:00 AM or 6:45 PM)

.  h:mm A/P           hours (such as 6:00 A    or 6:45 P)

NOTE - When a format contains AM or PM, the hour is based on the 12-hour clock. "AM" or "A" indicates times from midnight until noon; 12 AM is Midnight. "PM" or "P" indicates times from noon until midnight; 12 PM is Noon.

Sample Custom Date And Time CodesThese are just a few examples to give you some ideas of what is possible with custom date and time formats. To display text next to a format code, enclose the text in quotes.

CODE DISPLAYS ****.  d mmm yyyy                     3 Apr 2000

.  mmm/dd/yyyy                   Apr/03/2000

.  mmmm dd, yyyy (ddd)     April 3, 2000 (Mon)

.  hh:mm "GMT"                  14:15 GMT

.  [h]:mm "elapsed hrs"       28:30 elapsed hrs

.  h A/P                                8 A

.  [blue]dd-mm-yyyy            03/04/2000  (displayed in blue)

.

!   Format a date the way you want

https://support.microsoft.com/en-us/office/format-a-date-the-way-you-want-8e10019e-d5d8-47a1-ba95-db95123d273e

When you enter some text into a cell such as "2/2", Excel assumes that this is a date and formats it according to the default date setting in Control Panel. Excel might format it as "2-Feb". If you change your date setting in Control Panel, the default date format in Excel will change accordingly. If you don’t like the default date format, you can choose another date format in Excel, such as "February 2, 2012" or "2/2/12". You can also create your own custom format in Excel desktop.

.  *  Choose from a list of date formats

.  *  Create a custom date format

.  *  Tips for displaying dates

.

Answer 3

Fixed that -3 problem.

Copied the source data into a new tab

Defined it as a table (irrelevant, but I prefer work from a table rather than a range)

Defined pivot table the same way as last time

But this time the handicap works ...

.

I don't know why.

I don't know what the difference is.

It just works now ...

The new Pivot is in the example file.

PS: I changed the handicap on the first row to -10, it is now displayed in the pivot, just like that after refreshing the pivottable ...

Answer 4

Such output is complicated to produce, Excel does not offer a direct way of doing this.

It may be useful for you personally, but it's a disaster for a data analyst!

It is possible with Excel, but we have to use everything Excel has to offer. Therefore:

In my opinion, a macro may even be easier to understand than a complex structure with Power Query.

Open your sample file:

https://1drv.ms/x/s!AhIN7XAB43D8qDViqezkON4HoOxP

Execute the macro below.

Andreas.

Sub Test()

  Dim Source As Range

  Dim Score As Object 'Scripting.Dictionary

  Dim Hdcp As Object 'Scripting.Dictionary

  Dim Data, Temp, Players, Scores

  Dim i As Long, m As Long, j As Long

  Dim Player As String

  Set Source = Range("A5", Range("D" & Rows.Count).End(xlUp))

  Data = Source.Value

  Set Score = CreateObject("Scripting.Dictionary")

  Score.CompareMode = vbTextCompare

  Set Hdcp = CreateObject("Scripting.Dictionary")

  Hdcp.CompareMode = vbTextCompare

  For i = 1 To UBound(Data)

    Player = Data(i, 1)

    If Score.Exists(Player) Then

      Temp = Score.Item(Player)

      ReDim Preserve Temp(0 To 1, 0 To UBound(Temp, 2) + 1)

    Else

      ReDim Temp(0 To 1, 0 To 0)

      Hdcp.Item(Player) = Data(i, 4)

    End If

    If UBound(Temp, 2) > m Then m = UBound(Temp, 2)

    Temp(0, UBound(Temp, 2)) = Data(i, 2)

    Temp(1, UBound(Temp, 2)) = Data(i, 3)

    Score.Item(Player) = Temp

  Next

  ReDim Data(1 To Score.Count * 2, 1 To m + 3)

  Players = Score.Keys

  Scores = Score.Items

  For i = 0 To UBound(Players)

    Data(i * 2 + 2, 1) = Players(i)

    Data(i * 2 + 2, 2) = Hdcp.Item(Players(i))

    Temp = Scores(i)

    For j = 0 To UBound(Temp, 2)

      Data(i * 2 + 1, j + 3) = Temp(0, j)

      Data(i * 2 + 2, j + 3) = Temp(1, j)

    Next

  Next

  Sheets.Add

  Range("A1:B1") = Array("Name", "Hdcp")

  With Range("A2").Resize(UBound(Data), UBound(Data, 2))

    .Value = Data

    .EntireColumn.AutoFit

  End With

End Sub

Answer 5

Andreas,

I am completely lost.  I copied your macro into the spreadsheet but on a different sheet.  I went to YouTube to attempt to find a tutorial on how to load the Macro and get it activated.  Fo Ruth life of me, I can't find a video that shows me how to do that.

I have uploaded the workbook in hopes that you can help me get the macro loaded and activated.

https://1drv.ms/x/s!AhIN7XAB43D8qDnrssAYcydnTTB5

Thank you in advance for all your assistance.

Regards,

Ed Sarlo