Pivot Tables

Isabella · Jan 8, 2011

Hi,

Could someone please show me how i can perform a pivot table that will give me the same result as show in Col J-P. I want to avoid using formula if possible.

Excel Workbook

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

4

2-29

30-59

5

Type

Amount

CCY

Age

Source

CCY

Rate

Source

No. of items

Value (AUD)

ABS (AUD)

No. of items

Value (AUD)

ABS (AUD)

6

SCR

8268.57

AUD

15

Team1

AUD

1

Team1

3

29,268.57

29268.57

7

SCR

20000

AUD

20

Team1

EUR

0.75581

Team2

1

6,844.94

6844.94067

8

SCR

1000

AUD

5

Team1

GBP

0.645411

Team3

0

0.00

0

9

SCR

4417.8

GBP

7

Team2

JPY

82.56223

Team4

17

29,314.60

1803231.35

10

SCR

2208.9

GBP

30

Team2

USD

1.00655

11

SCR

100000

AUD

40

Team3

12

LCR

1163.22

EUR

12

Team4

13

LDR

-3320.14

EUR

39

Team4

14

LCR

9646.51

EUR

15

Team4

15

LDR

-12618.8

EUR

40

Team4

16

LCR

476017.8

EUR

15

Team4

17

LDR

-1688137

JPY

38

Team4

18

LCR

7187517

JPY

4

Team4

19

LDR

-6303971

JPY

15

Team4

20

LCR

108431.3

USD

15

Team4

21

LCR

15652.36

USD

15

Team4

22

LDR

-280467

USD

17

Team4

23

LCR

52025.14

USD

17

Team4

24

LCR

7589.03

USD

17

Team4

25

LCR

1965.98

EUR

3

Team4

26

LDR

-98555.5

EUR

17

Team4

27

LDR

-33658.3

EUR

17

Team4

28

LDR

-19670

EUR

10

Team4

29

LDR

-247310

EUR

17

Team4

30

LDR

-2068.46

EUR

13

Team4

31

LDR

-800

EUR

28

Team4

Sheet1

Isabella · Jan 10, 2011

Ok, i think i am getting somewhere now.

I got to the part where i have 2 boxes in the Query Wizard, in the first box is Forex and Maintable, i click the headings i need for each named range by using the right arrow key to move them into the second box, i then press OK. Is this what yuo got?

I got a message "The Query Wizard can not continue because it cannot join the tables in your query. You must join the tables manually in Micorsoft Query by dragging the fields to joint between the tables

Fazza said:
OK, Isabella. Thanks for clarifying the steps. I don't have Excel 2010, sorry.

FWIW, prior to Excel 2007 the steps are,

ALT-D-P to start pivot table wizard

choose "External data source", Next

Get Data...

from the Databases strip, "Excel files*", OK

then follow wizard to the end & take the option to edit in MS Query

This might help you to work out the steps in Excel 2010. Suggest you refer to the help files. Maybe google the Microsoft site?

regards

Fazza · Jan 10, 2011

YES, Isabella.

Keep going.

You can continue from there, or you can just choose data from one table. It doesn't matter what you do, because when you get to MS Query you edit the SQL to change it anyway.

Isabella · Jan 11, 2011

Fazza said:
YES, Isabella. Keep going.

You can continue from there, or you can just choose data from one table. It doesn't matter what you do, because when you get to MS Query you edit the SQL to change it anyway.

Fazza, i need the SQL to produce data similar to J-P, Where i have age criteria breaks for each team so you can see in row 4 i have 2-29 and then 30-59 and it will go up to 120 days, the values in Col L and O etc will be converted to AUD based on the rates in G-H.

I found how to get 2010 working, so i've learn something new thats for sure.

Excel Workbook

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

4

2-29

30-59

5

Type

Amount

CCY

Age

Source

CCY

Rate

Source

No. of items

Value (AUD)

ABS (AUD)

No. of items

Value (AUD)

ABS (AUD)

6

SCR

8268.57

AUD

15

Team1

AUD

1

Team1

3

29,268.57

29268.57

7

SCR

20000

AUD

20

Team1

EUR

0.75581

Team2

1

6,844.94

6844.941

8

SCR

1000

AUD

5

Team1

GBP

0.645411

Team3

0

9

SCR

4417.8

GBP

7

Team2

JPY

82.56223

Team4

17

29,314.60

1803231

10

SCR

2208.9

GBP

30

Team2

USD

1.00655

11

SCR

100000

AUD

40

Team3

12

LCR

1163.22

EUR

12

Team4

13

LDR

-3320.14

EUR

39

Team4

14

LCR

9646.51

EUR

15

Team4

15

LDR

-12618.8

EUR

40

Team4

16

LCR

476017.8

EUR

15

Team4

17

LDR

-1688137

JPY

38

Team4

18

LCR

7187517

JPY

4

Team4

19

LDR

-6303971

JPY

15

Team4

20

LCR

108431.3

USD

15

Team4

21

LCR

15652.36

USD

15

Team4

22

LDR

-280467

USD

17

Team4

23

LCR

52025.14

USD

17

Team4

24

LCR

7589.03

USD

17

Team4

25

LCR

1965.98

EUR

3

Team4

26

LDR

-98555.5

EUR

17

Team4

27

LDR

-33658.3

EUR

17

Team4

28

LDR

-19670

EUR

10

Team4

29

LDR

-247310

EUR

17

Team4

30

LDR

-2068.46

EUR

13

Team4

31

LDR

-800

EUR

28

Team4

Sheet2

Fazza · Jan 11, 2011

Good work, Isabella.

Include the Age field in the pivot table and then 'group' on that field to achieve the different age brackets. This is normal pivot table functionality. Here is an on-line description http://www.contextures.com/xlpivot07.html, from a site that has lots of other good info on pivot tables.

regards

Fazza · Jan 11, 2011

PS

If you joined both tables - the one I called MainData and the Forex - in the SQL en route to creating the pivot table, you should already all amounts in AUD. The conversion to AUD was done by the SQL. Are you OK with that conversion?

F

Isabella · Jan 12, 2011

Fazza said:
PS

If you joined both tables - the one I called MainData and the Forex - in the SQL en route to creating the pivot table, you should already all amounts in AUD. The conversion to AUD was done by the SQL. Are you OK with that conversion?

F

Fazza i am loving this SQL, i amended the SQL to include M.Age before M.Source, i then opened Via pivot table. Couple of questions though.

1.How would i get the ABS for each age bracket similar to my example i shown for 2-29 bracket

2. My raw data is not fixed so everyday the raw data will change ranges , the header row will remain the same though, the name range must change to suit.

3. I need this pivot in the same workbook where my rata is so in sheet 3 as example

Fazza · Jan 12, 2011

Good morning, Isabella.

To get the absolute values, create another field within the SQL and then sum on it in the pivot table, as a datafield. Such as.

Code:

SELECT M.Amount/F.Rate AS [Value], M.Age, M.Source, ABS(M.Amount/F.Rate) AS [AbsValue]
FROM Forex F, MainData M
WHERE F.CCY = M.CCY

As the data changes each day, change the named range MainData to suit. So if today it covers A5:E31 and tomorrow A5:E1234, edit the named range to suit. Such as via CTRL-F3, select MainData and edit directly in the "refers to" box. It might help to use the F2 key when editing? Or have some VBA update the range each time,
range("A5").currentregion.name = "MainData"

You could have VBA do all the work each day to accommodate changing data. It is little more than change the extent of the named range and then refresh the pivot table.

Move the worksheet containing the pivot table into the workbook you want it in.

Isabella · Jan 12, 2011

Hi Fazza

I think i am getting close to what i want.

1. With the pivot table when i Click on Group i selected Begin with 2 and end with 120, but i cannot get 2-29, 30-59, 60-89, 90-119, >120,

2. How do i get the Count of Age Field next to the source because when i move the Age to Row it remove my Age brackets from Column field,

I want a layout similar to this.

<TABLE style="WIDTH: 462pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=616 border=0 x:str><COLGROUP><COL style="WIDTH: 66pt; mso-width-source: userset; mso-width-alt: 3218" span=7 width=88><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 66pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=88 height=17> </TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 198pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=264 colSpan=3 x:str="'2-29">2-29</TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 198pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=264 colSpan=3 x:str="'3-59">3-59</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:str="Source ">Source </TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Age Count</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Value</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">AbsValue</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Age Count</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Value</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">AbsValue</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Team1</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Team2</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Team3</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD></TR></TBODY></TABLE>

Fazza said:
Good morning, Isabella.
To get the absolute values, create another field within the SQL and then sum on it in the pivot table, as a datafield. Such as.

Code:

SELECT M.Amount/F.Rate AS [Value], M.Age, M.Source, ABS(M.Amount/F.Rate) AS [AbsValue] FROM Forex F, MainData M WHERE F.CCY = M.CCY

As the data changes each day, change the named range MainData to suit. So if today it covers A5:E31 and tomorrow A5:E1234, edit the named range to suit. Such as via CTRL-F3, select MainData and edit directly in the "refers to" box. It might help to use the F2 key when editing? Or have some VBA update the range each time,
range("A5").currentregion.name = "MainData"

You could have VBA do all the work each day to accommodate changing data. It is little more than change the extent of the named range and then refresh the pivot table.

Move the worksheet containing the pivot table into the workbook you want it in.

Fazza · Jan 12, 2011

For the grouping, try from 0 to 120 by 30. Gives 0-29, 30-59, 60-89, 90-120, >120.

Put the field in twice. Once as a datafield & once as a row field. (I counted the Source field, btw.)

OK?

Isabella · Jan 12, 2011

Fazza said:
For the grouping, try from 0 to 120 by 30. Gives 0-29, 30-59, 60-89, 90-120, >120.

Put the field in twice. Once as a datafield & once as a row field. (I counted the Source field, btw.)

OK?

Fazza, the grouping looks right now but the Age count and Abs value does not look right, my field setting is this, the issue is with Age count and AbsValue

Row = Source
Column = Age

Data= Sum of Value
= Sum of AbsValues
=Age Count

Pivot Tables

Isabella

Well-known Member

Isabella

Well-known Member

Excel Facts

Fazza

MrExcel MVP

Isabella

Well-known Member

Fazza

MrExcel MVP

Fazza

MrExcel MVP

Isabella

Well-known Member

Fazza

MrExcel MVP

Isabella

Well-known Member

Fazza

MrExcel MVP

Isabella

Well-known Member

Similar threads

Forum statistics

Share this page

Pivot Tables

Well-known Member

Well-known Member

Excel Facts

MrExcel MVP

Well-known Member

MrExcel MVP

MrExcel MVP

Well-known Member

MrExcel MVP

Well-known Member

MrExcel MVP

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock