Pivot Table for Casino log

acura123

Board Regular
Joined
Jul 13, 2003
Messages
77
So I am trying to create a Casino log to track my trips in Excel. My data is entered so that the Column headings go along the first column and the Trip Dates go along the top row.
The problem I'm having is how do I perform a pivot table on this data in this format so that I can generate useful information like total win/loss to Date or amount of food Comped?
Does the Data have to be rearranged or can it be used in it's current format?

Sample Casino Diary.xlsx
BCDEFG
2Date1/1/20211/15/20211/20/20212/1/20213/1/2021
3CasinoHarrrahsBallysBorgataHarrahsTropicana
4Win Loss-100300250-500
5Food10205010025
Sheet1



Thank You!
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

mrshl9898

Well-known Member
Joined
Feb 6, 2012
Messages
1,951
So unlike the 4 Rows you showed? In 4 columns you should have no issues with Pivoting the data
 

alansidman

Well-known Member
Joined
Feb 26, 2007
Messages
6,666
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

If this is what you want, then I rearranged your data in Power Query with the following Mcode

Book5
FGH
1CasinoSum of Win LossSum of Food
2Ballys30020
3Borgata25050
4Harrahs-50100
5Harrrahs-10010
6Tropicana025
Table1


Power Query:
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Demoted Headers" = Table.DemoteHeaders(Source),
    #"Changed Type" = Table.TransformColumnTypes(#"Demoted Headers",{{"Column1", type text}, {"Column2", type any}, {"Column3", type any}, {"Column4", type any}, {"Column5", type any}, {"Column6", type any}}),
    #"Transposed Table" = Table.Transpose(#"Changed Type"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Date", type date}, {"Casino", type text}, {"Win Loss", Int64.Type}, {"Food", Int64.Type}})
in
    #"Changed Type1"

which arranged your data to look like this

Book5
ABCD
1DateCasinoWin LossFood
21/1/2021Harrrahs-10010
31/15/2021Ballys30020
41/20/2021Borgata25050
52/1/2021Harrahs-50100
63/1/2021Tropicana025
Table1


and then just pivot the data as you normally would.
 

acura123

Board Regular
Joined
Jul 13, 2003
Messages
77
So to be clear you can't pivot data when it is formatted like I first listed?
 

mrshl9898

Well-known Member
Joined
Feb 6, 2012
Messages
1,951

ADVERTISEMENT

You can pivot any table, but the headers will determine what fields are in there. (the top row)
 

alansidman

Well-known Member
Joined
Feb 26, 2007
Messages
6,666
Office Version
  1. 365
Platform
  1. Windows
So to be clear you can't pivot data when it is formatted like I first listed?
You can certainly pivot the data from your table, however, it will not give you your expected results. You will need to change the format of your current table.
 

acura123

Board Regular
Joined
Jul 13, 2003
Messages
77
So what I did was create a table out of the original data and then created a separate sheet with a transposed the table so that the data labels are across the top so that they can easily be pivoted.
 

alansidman

Well-known Member
Joined
Feb 26, 2007
Messages
6,666
Office Version
  1. 365
Platform
  1. Windows
I believe I did what you want in Post #5. Advise if you are looking for something different.
 

Forum statistics

Threads
1,148,143
Messages
5,745,043
Members
423,917
Latest member
Frank1931

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top