VBA Macros and Advanced Excel Training

VBA macros trainingBy Ayush Jain(Microsoft MVP)

After the course, you will be able to:Record / Run Macros

Write Code Manually

Perform Loops, Controls and decision making

Create UDF Functions

Protecting macros

Use Events

Learning MacrosWhy VBA Macros An Introduction

Where we write macros Introduction to VB Editor

Recording Macros

Personal Macro workbook

Declaring Variables

Interactive Macros - Msgbox / Inputbox

Decision Making (If, Else)

Looping (Do Loop, For Loop)

Why VBA Macros ?VBA is "Visual Basic for Application.

It is a programming language that allows users to program macros to accomplish complex tasks within an application like Excel, Word, Power Point, Access, etc.

With VBA for Excel you can develop small procedures (macros) that will make your professional life easier and allow you to do more in less time.

With VBA for Excel you can develop a program that does EXACTLY what you need and VBAis easy to learn.

Programming background is useful but not mandatory to learn macros.

Where we write macros ?VBA macros are written into Visual Basic editorDeveloper Visual BasicOR [ ALT + F11 ]

Insert a Module in the editor to write the codeInsertModule

All VBA procedures (macros) start with Sub & end with End Sub

You can run the code by pressing F5 or clicking the Run symbol on the toolbar

Recording MacrosExcel provides Macro Recorder as the most user friendly tool.

It is very useful to see what properties and methods belong to various objects. You can then use this as the basis for building your own code.

When you record a macro, a module is created and the code is written into it.

You can view this code and see exactly how Excel has tackled the problem in VBA. You can modify this code or use it in other procedures.

You should not assume that a recorded macro will work under every circumstance.How to record a macro?Tools Macro Record new macro

You can give the macro another name and a shortcut key if needed

Click OK and a small Stop Recording window appears.

Until you click the Stop Recording button, everything that you do on the spreadsheet is translated into VBA code using the Excel object model

Look in the code window by pressing Alt-F11, and you will find that a new module has been inserted that contains the code for the macro you recorded

How to create variables in VBA?Whenever you want to use a variable, you must create them first. This is your way of telling computer to set aside some memory units so that your variable can be used.

In Excel VBA, you can do this by the DIM statement.Examples :

Dim someNumber As IntegerDim bigNumber As LongDim otherNumber As DoubleDim someText As String

DecisionsPrograms usually have to make decisions according to data retrieved or input by the user.

It specifies what will happen when different events occur.

We use If-then-end if & Select Case to implement decisions

ExamplesWhen there is only one condition and one action, you will use the simple statement: If Application.ActiveCell = 5 ThenMsgBox "Cell is 5"ElseMsgBox "Cell is not 5"End If

LoopingLooping allows a block of code to be repeated until a condition or a specified value is met.Without looping facilities, programs would be extremely tedious and difficult to maintain.We use For next ,For each, Do Until, While wend etc. to reduce the code & make it simpleExample You wanted to display the numbers from 1 to 5. You could write the program as follows:MsgBox "1"MsgBox "2"Msgbox "3"Msgbox "4"MsgBox "5" This code can be reduced and made easier to maintain by using the For..Next looping statement as follows:For n = 1 to 5MsgBox nNext n

Early Exit of Loops Under some circumstances, you may want your procedure to exit a loop early before it has worked all the way through and satisfied its criteria.

You exit a loop by using an Exit For statement in a For..Next loop or a For Each loop

Here is an example:Sub test_exit()

For x = 1 To 100If x = 50 ThenExit ForEnd IfNext xMsgBox xEnd Sub

The Excel Object ModelIn Excel, the whole application is oriented toward a structure of workbooks and spreadsheets, so the object model is written around this.

The objects are arranged in a hierarchy.

The Excel object model contains a large number of objectsfor example Workbooks, Worksheets, Ranges, Shapes & Charts

Under the Application object is the Workbook object, and within the Workbook object are Worksheet objects. Within each Worksheet object are Range objects, and so on. The Excel Object Model

Excel beyond Microsoft

The Application objectWhenever you want Excel to do something or you want to change a property of Excel, you will use the object Application.

ExamplesCutCopyModeAfter each Copy/Paste operation, you should empty the clipboard with the following line of code to make sure that the computer memory doesn't overload.

ActiveSheet.PasteApplication.CutCopyMode=False

DisplayAlertsWhen you don't want Excel to ask you things like "A file already exists....." or "Do you want to save this file..." you will use the following line of code at the beginning of your VBA procedure.

Application.DisplayAlerts = FalseThen at the endApplication.DisplayAlerts = True

ScreenUpdatingWhen you don't want to see your screen follow the actions of your VBA procedure, you start and end your code with the following sentences:

Application.ScreenUpdating = FalseApplication.ScreenUpdating = TrueThe Workbook object The Workbook object represents an entire workbook loaded into Excel.

Activeworkbook represents the workbook in the active window (the window on top)

ExamplesTo Open a WorkbookWorkbooks. open C:\Test.xls

To activate the Sheet3 of workbook book1Workbooks("book1").Worksheets("Sheet3").Activate

To save the workbookWorkbooks("book1").SaveActiveworkbook.save

To print the active sheet of workbookWorkbooks("book1").PrintOut

To close the workbookWorkbooks("book1").Close Activeworkbook.closeExercise Pre-work : Create a blank excel file in C drive with the name Income.xlsx

Tasks :Open a workbookCopy Incomestatement from current workbookPaste Incomestatement in first sheet worksheet of Income.xlsxSave workbookClose workbook

The Worksheet objectThis object represents the actual worksheet that you work on.

In the hierarchy of the Excel object model, it sits below the Workbook object because all Worksheets are part of a Workbook.

ExamplesTo add a worksheetWorksheets.add

To change the tab color of the sheetWorksheets("Sheet1").tab.color = vbGreen To rename a worksheetWorksheets("Sheet1").Name = "Balance"

To select a worksheet Worksheets("Balance").Activate

To hide or unhide a worksheetWorksheets("Sheet1").Visible= TrueWorksheets("Sheet1").Visible= False

To delete a sheetWorksheets("Sheet1").deleteExercise Tasks :Add a new sheet Name the sheet as income statement Copy the income statement from previous sheet to new sheet Change the tab color to green Hide the previous sheetThe Range objectThis object communicates with a range of cells or individual cells and makes changes in them.

Examples:To select a range of cells in codeRange(F19:G20").Select

To change the background color of the cellRange(A1").Interior.color = vbRed To clear the contents of the range of cells Range(A3:D12").ClearContents

To copy and paste the data in rangeRange(" F19:G20").CopyRange(" H21").PasteSpecial

To write data into a spreadsheet Range(F26").Value = 10

To merge the cellsRange(F26:H26).merge

Famous ranges and cellsIM21 The legal drinking age cellAK47 The assault weapon cellAH:HA The discovery rangeAM:FM The radio rangeBY:BY The farewell rangeIQ100 The average intelligence cell HO:HO The Santa Claus range GO2 The destination cellEX2 The second former spouse cellAC:DC The electric rangeI1:U1 The tied game cell

Exercise Tasks :Copy and paste the income st. to different range in the same sheetColor the header of the Income statement in red colorColor the particulars in green colorMerge the cells on the topAdd the title INCOME STATEMENTColor the negative values in red colorUser defined functions A User defined function (or UDF) - A function accepts some inputs and returns a result- It can only return a value to the cell when it is called - It must not modify the contents or formatting of any cell

Example :Function Area(Length As Double, Width As Double) Area = Length * Width End Function

Exercise :Create a function to calculate tax by taking income and investment as input and return tax payable as outputInputs : Income , InvestmentOutput : Tax Payable

Error Handling in VBAVBA tells you when the code is wrong but what if the logic is wrong or what if the user gives a wrong answer. We have two methods to handle these errors

Method 1 (When you want VBA to do something if there is an error)

The first thing you create is an address where VBA will jump if there is an error. In this example the address is addJump with the NOT OPTIONAL colon at the end. Below addJump is what is supposed to happen of there is an error and above is the procedure that should run if there are no errors.

ExampleSub proTestErrorHandler() On Error GoTo addJump Workbooks.Open "xxxxxx" Exit Sub addJump: MsgBox "An error has occurred, call Peter at 1 514-257-0734" End SubError Handling in VBAMethod 2 (When you want VBA to do nothing & continue with rest of the code)

If you just want errors to be ignore you write On Error Resume Next at the beginning of the procedure. Copy/Paste the following procedure in a module of your own and run it. It will generate a message box saying An error has occurred but we have ignored it.

ExampleSub proTestErrorHandlerIgnore() On Error Resume Next Workbooks.Open "xxxxxx" MsgBox "An error has occurred but we have ignored it." End Sub

VBA Code General TipsDo not hesitate to use the Macro recorder to avoid typos.

Write your code in lower case letters. If the spelling is right, VBE will capitalize the necessary letters. If it doesn't.... check your spelling.

Declare all your variables (Dim) at the beginning of the procedure, it will simplify the testing of your code.

You can insert a comment using in the comment. It will convert text of the comment in green colorExample : This is a comment

Nine tips to learn VBA#1 Think Thru before Coding#2 Use the Macro Recorder#3 Break Your Work in to Smaller Chunks#4 Take up Challenges#5 Reuse code : Search Google for available codes #6 Keep a Good Reference Handy#7 Use VBA only when you need it#8 Join Excel forums and ask queries#9 Use F1 for quick VBA help

27Pivot TablesIt sums up large amount of information in a small amount of space

PivotTables allow you to pivot data using drag-and-drop techniques and receive results immediately.

You can rotate rows & columns to view the data in an interactive way.

You can link to external data sources

It helps in organizing, analyzing & comparing data

Example

Memory GameMacroPivot tableclearcontentsVBAFor LoopAddInputboxDo LoopValueMsgboxIf conditionDoubleDimMacro RecorderFont.colorLongFunctionTab.colorIntegerSubStop recordingStringVariablesDiscussexcel.comThe object modelPersonal macro workbookUser defined functionWorkbooksWorksheetseventsRangeActive cellVisibleVB EditorThis workbookActivateModuleMergeAlt+F11ProtectionInterior.colorAlt+F8Resourceswww.discussexcel.com

http://groups.google.com/group/excel-macros

www.ozgrid.com

www.cpearson.com

www.chandoo.org

www.exceluser.com

QUERIES ?PivotWHERE(All)WHEN(All)Row LabelsSum of REVENUEMap case2820How to Win Foreign Friends8866Hat - polar explorer10200Hammock18370Geo positioning system107970Exploring in 10 Easy Lessons6260Elephant polo stick111232Compass2608Camel saddle202124Boots - snakeproof13015Grand Total483465

Pivot Table Raw DataTransidWHENWHEREWHATHOWQUANTITYREVENUE111/29/98ParisExploring in 10 Easy LessonsStore480210/19/98LondonCamel saddleCatalog31794311/6/98ParisCompassStore21647/27/98LondonGeo positioning systemStore388558/4/98ParisHammockStore316568/6/98ParisCamel saddleStore2119676/26/98ParisGeo positioning systemStore259085/10/98LondonGeo positioning systemStore51475911/2/98ParisHammockStore31651010/18/98LondonCamel saddleCatalog42392117/15/98LondonBoots - snakeproofStore2190129/13/98ParisGeo positioning systemStore25901312/19/98LondonHow to Win Foreign FriendsStore51301410/24/98LondonCompassCatalog540153/29/98New YorkMap caseStore575169/6/98ParisGeo positioning systemCatalog2590177/29/98LondonHammockStore42201812/12/98New YorkElephant polo stickStore31056194/28/98TokyoCamel saddleCatalog21196207/9/98LondonHammockWeb5275212/6/98ParisElephant polo stickCatalog51760221/24/98ParisHat - polar explorerStore3102239/14/98LondonHow to Win Foreign FriendsCatalog4104243/11/98ParisHammockCatalog5275256/29/98SydneyCompassStore432264/26/98ParisGeo positioning systemStore41180278/17/98ParisCamel saddleStore21196289/7/98LondonCompassWeb2162910/3/98New YorkCompassStore3243011/27/98LondonHammockStore3165311/16/98LondonExploring in 10 Easy LessonsStore240327/13/98LondonGeo positioning systemStore12953311/5/98LondonCamel saddleCatalog423923412/3/98LondonElephant polo stickStore310563511/3/98SydneyCompassStore216364/27/98ParisExploring in 10 Easy LessonsStore360379/24/98ParisExploring in 10 Easy LessonsCatalog2403812/2/98New YorkHammockWeb4220395/20/98LondonHat - polar explorerStore134401/27/98ParisCompassStore540411/8/98ParisHammockStore4220422/26/98ParisMap caseStore230431/17/98New YorkExploring in 10 Easy LessonsStore120445/10/98ParisHat - polar explorerWeb134454/3/98TokyoHow to Win Foreign FriendsStore252466/7/98LondonHammockStore2110478/11/98LondonHat - polar explorerStore134482/4/98TokyoCamel saddleStore21196498/30/98LondonCamel saddleStore21196505/21/98ParisHat - polar explorerCatalog5170514/20/98LondonElephant polo stickCatalog2704526/4/98TokyoHow to Win Foreign FriendsStore5130532/9/98ParisBoots - snakeproofStore54755412/9/98ParisCompassStore432555/2/98ParisCompassWeb3245610/14/98New YorkHat - polar explorerStore4136574/16/98SydneyGeo positioning systemWeb1295585/29/98LondonHat - polar explorerCatalog3102594/17/98New YorkGeo positioning systemCatalog514756011/30/98LondonElephant polo stickStore27046112/3/98ParisCompassStore3246210/17/98ParisHow to Win Foreign FriendsCatalog378632/17/98ParisGeo positioning systemStore1295645/18/98LondonHammockStore4220657/2/98ParisHow to Win Foreign FriendsStore1266611/19/98LondonMap caseStore3456711/30/98ParisHow to Win Foreign FriendsStore378683/23/98TokyoHammockStore3165692/23/98LondonHat - polar explorerStore41367012/22/98New YorkHammockStore2110717/2/98ParisHow to Win Foreign FriendsStore4104728/27/98LondonExploring in 10 Easy LessonsStore3607311/17/98TokyoCompassStore187410/13/98ParisHat - polar explorerWeb3102759/24/98ParisHow to Win Foreign FriendsStore4104765/4/98ParisHammockCatalog4220775/13/98ParisMap caseStore575789/4/98LondonExploring in 10 Easy LessonsStore240797/30/98ParisElephant polo stickStore310568012/15/98LondonCompassStore216814/17/98LondonMap caseStore5758210/31/98SydneyCompassStore324834/27/98LondonCamel saddleStore21196843/3/98LondonHammockStore42208510/15/98ParisCompassWeb432866/1/98ParisMap caseStore345874/14/98ParisHammockStore5275885/14/98LondonMap caseCatalog345895/26/98ParisHat - polar explorerStore268908/25/98LondonHammockStore4220919/25/98LondonHat - polar explorerCatalog4136922/7/98ParisGeo positioning systemStore3885936/25/98ParisHat - polar explorerStore268941/6/98ParisHow to Win Foreign FriendsStore252951/21/98ParisCamel saddleCatalog42392965/14/98LondonElephant polo stickCatalog51760979/19/98TokyoHammockStore2110983/2/98LondonHammockStore4220991/23/98LondonGeo positioning systemStore12951005/17/98TokyoExploring in 10 Easy LessonsCatalog24010111/27/98LondonBoots - snakeproofWeb438010212/18/98ParisHammockStore31651037/31/98ParisGeo positioning systemCatalog388510411/8/98ParisGeo positioning systemStore12951056/6/98ParisGeo positioning systemCatalog25901067/16/98ParisCompassCatalog21610712/6/98ParisCompassStore2161082/14/98TokyoCamel saddleStore317941099/13/98LondonGeo positioning systemStore129511011/30/98TokyoGeo positioning systemStore411801114/18/98ParisHow to Win Foreign FriendsStore2521127/6/98ParisCamel saddleStore529901135/25/98LondonCompassStore3241141/8/98LondonHat - polar explorerCatalog41361151/2/98LondonExploring in 10 Easy LessonsStore1201166/24/98LondonElephant polo stickCatalog310561171/9/98LondonHat - polar explorerStore413611810/14/98ParisHow to Win Foreign FriendsStore41041195/31/98LondonGeo positioning systemStore4118012011/9/98SydneyCompassStore2161215/14/98New YorkHammockStore1551229/27/98LondonHow to Win Foreign FriendsStore3781236/27/98New YorkExploring in 10 Easy LessonsCatalog51001247/30/98LondonHow to Win Foreign FriendsStore3781251/30/98LondonElephant polo stickStore27041264/6/98LondonHow to Win Foreign FriendsWeb410412711/12/98LondonCamel saddleStore15981286/15/98LondonGeo positioning systemCatalog38851297/12/98ParisExploring in 10 Easy LessonsCatalog24013012/12/98TokyoExploring in 10 Easy LessonsStore3601313/18/98ParisHammockCatalog31651328/7/98LondonExploring in 10 Easy LessonsStore4801331/5/98ParisHammockStore1551343/4/98LondonGeo positioning systemStore38851353/2/98ParisCamel saddleWeb3179413611/24/98ParisHow to Win Foreign FriendsStore2521378/29/98LondonGeo positioning systemStore4118013811/9/98LondonCompassStore181399/4/98New YorkExploring in 10 Easy LessonsStore4801409/14/98ParisHammockStore21101415/29/98ParisHow to Win Foreign FriendsCatalog3781428/6/98ParisExploring in 10 Easy LessonsStore4801432/16/98SydneyHow to Win Foreign FriendsStore1261445/16/98LondonElephant polo stickWeb310561456/11/98ParisCompassStore2161463/16/98TokyoHammockCatalog52751472/11/98LondonHat - polar explorerStore2681488/29/98TokyoElephant polo stickStore13521495/14/98SydneyExploring in 10 Easy LessonsStore3601503/8/98ParisHammockStore21101511/14/98LondonGeo positioning systemStore129515211/24/98ParisGeo positioning systemStore4118015311/21/98TokyoCamel saddleStore15981546/15/98LondonCompassStore32415511/8/98TokyoCompassStore32415610/26/98LondonCamel saddleStore15981575/3/98TokyoCompassStore5401584/8/98LondonBoots - snakeproofStore438015911/11/98ParisBoots - snakeproofStore219016011/20/98TokyoElephant polo stickStore517601614/5/98TokyoGeo positioning systemStore38851628/3/98New YorkHow to Win Foreign FriendsStore37816310/21/98LondonHow to Win Foreign FriendsStore2521647/5/98SydneyExploring in 10 Easy LessonsStore4801658/19/98ParisHat - polar explorerStore51701665/25/98TokyoGeo positioning systemStore411801675/1/98SydneyBoots - snakeproofStore21901683/28/98ParisCompassStore4321698/25/98LondonCompassCatalog1817012/17/98LondonExploring in 10 Easy LessonsCatalog2401717/4/98ParisHow to Win Foreign FriendsStore51301727/1/98ParisMap caseStore2301737/15/98ParisHow to Win Foreign FriendsStore41041749/4/98LondonBoots - snakeproofCatalog21901756/19/98TokyoHat - polar explorerStore51701763/9/98LondonExploring in 10 Easy LessonsStore51001775/30/98TokyoHow to Win Foreign FriendsCatalog3781786/9/98New YorkHow to Win Foreign FriendsCatalog2521798/8/98TokyoCamel saddleCatalog529901807/13/98ParisCamel saddleStore317941819/23/98LondonElephant polo stickWeb414081821/29/98LondonCompassStore4321838/11/98SydneyCompassStore181849/16/98TokyoCompassStore4321854/5/98ParisHow to Win Foreign FriendsWeb513018611/12/98ParisExploring in 10 Easy LessonsStore24018711/24/98LondonCamel saddleStore423921888/9/98TokyoHat - polar explorerStore26818911/17/98LondonHow to Win Foreign FriendsStore41041908/22/98LondonCompassWeb3241911/16/98ParisHow to Win Foreign FriendsStore51301925/17/98LondonCompassStore181932/15/98ParisHow to Win Foreign FriendsStore3781945/16/98ParisHammockStore52751952/10/98LondonExploring in 10 Easy LessonsStore51001966/2/98SydneyElephant polo stickWeb517601971/17/98TokyoHammockStore21101984/14/98TokyoCompassStore2161996/11/98ParisElephant polo stickStore414082005/24/98LondonElephant polo stickWeb135220111/2/98New YorkHat - polar explorerStore2682026/18/98SydneyHammockStore15520310/18/98SydneyBoots - snakeproofStore32852041/12/98ParisHow to Win Foreign FriendsStore41042056/13/98SydneyHat - polar explorerStore1342067/28/98SydneyGeo positioning systemCatalog259020712/28/98New YorkCamel saddleStore15982088/13/98SydneyHow to Win Foreign FriendsCatalog1262095/24/98LondonElephant polo stickCatalog310562101/13/98TokyoCamel saddleCatalog3179421110/31/98SydneyBoots - snakeproofStore54752124/7/98ParisGeo positioning systemStore411802131/10/98LondonMap caseStore4602146/16/98LondonCamel saddleStore2119621512/29/98LondonHammockStore21102162/14/98LondonCamel saddleStore15982173/29/98ParisGeo positioning systemStore38852182/21/98ParisHammockStore42202199/28/98LondonExploring in 10 Easy LessonsWeb3602207/17/98ParisHammockCatalog211022112/30/98LondonHammockCatalog42202221/13/98TokyoGeo positioning systemStore411802232/16/98LondonElephant polo stickCatalog4140822410/4/98LondonCompassStore43222512/6/98SydneyHow to Win Foreign FriendsStore3782265/22/98ParisHammockStore31652274/7/98LondonHat - polar explorerStore13422811/18/98LondonBoots - snakeproofStore21902295/21/98LondonMap caseStore2302304/22/98New YorkGeo positioning systemStore25902316/22/98ParisCompassStore21623212/19/98LondonHammockStore21102337/9/98ParisCompassStore3242342/14/98LondonGeo positioning systemCatalog25902353/14/98ParisCamel saddleCatalog3179423612/22/98LondonBoots - snakeproofStore21902371/6/98LondonMap caseStore2302381/12/98New YorkHat - polar explorerWeb41362395/15/98TokyoHammockStore52752405/7/98SydneyExploring in 10 Easy LessonsStore4802415/25/98ParisHammockCatalog1552429/21/98New YorkGeo positioning systemStore38852435/27/98TokyoCamel saddleStore3179424411/30/98LondonExploring in 10 Easy LessonsStore2402452/2/98LondonGeo positioning systemStore25902464/1/98TokyoBoots - snakeproofStore32852471/25/98ParisCompassCatalog3242484/5/98ParisGeo positioning systemWeb25902498/15/98New YorkHow to Win Foreign FriendsStore41042504/27/98New YorkElephant polo stickStore517602512/25/98LondonBoots - snakeproofCatalog21902526/15/98LondonBoots - snakeproofCatalog54752532/16/98LondonElephant polo stickStore517602546/8/98New YorkHow to Win Foreign FriendsWeb2522555/28/98LondonMap caseStore4602563/29/98ParisExploring in 10 Easy LessonsCatalog51002578/20/98LondonExploring in 10 Easy LessonsStore3602581/22/98LondonElephant polo stickStore310562594/10/98ParisElephant polo stickStore310562602/9/98ParisGeo positioning systemStore4118026112/27/98ParisCompassStore3242626/7/98LondonHammockStore31652634/17/98LondonBoots - snakeproofStore21902647/5/98ParisExploring in 10 Easy LessonsStore2402657/8/98SydneyHat - polar explorerStore31022666/23/98TokyoHammockStore211026712/28/98New YorkElephant polo stickStore27042683/29/98LondonCamel saddleStore3179426910/9/98ParisGeo positioning systemStore38852706/24/98ParisMap caseStore2302719/26/98SydneyCamel saddleStore423922722/15/98LondonHammockCatalog21102733/25/98ParisMap caseStore4602742/8/98LondonHammockStore52752757/22/98LondonExploring in 10 Easy LessonsStore3602768/14/98New YorkElephant polo stickStore310562771/8/98LondonHammockCatalog52752781/23/98ParisHat - polar explorerWeb1342792/10/98TokyoHow to Win Foreign FriendsStore12628011/17/98LondonCamel saddleStore529902812/12/98LondonGeo positioning systemStore411802821/24/98ParisBoots - snakeproofCatalog43802839/30/98New YorkGeo positioning systemStore129528412/2/98LondonCompassStore2162855/20/98LondonCamel saddleCatalog529902867/20/98ParisCamel saddleStore317942874/21/98ParisCamel saddleStore211962888/16/98TokyoHat - polar explorerCatalog2682892/20/98ParisCompassStore2162906/27/98New YorkExploring in 10 Easy LessonsWeb24029111/12/98LondonCompassStore182929/24/98New YorkMap caseCatalog2302939/26/98New YorkHat - polar explorerStore310229411/10/98ParisCompassCatalog182956/9/98ParisElephant polo stickCatalog27042968/3/98LondonGeo positioning systemWeb411802974/13/98SydneyBoots - snakeproofCatalog19529812/27/98TokyoGeo positioning systemCatalog411802999/18/98LondonCamel saddleStore4239230010/13/98SydneyElephant polo stickCatalog310563015/28/98New YorkHammockStore31653027/20/98LondonElephant polo stickCatalog414083039/27/98LondonHow to Win Foreign FriendsStore1263043/21/98LondonGeo positioning systemStore259030511/30/98SydneyCamel saddleStore3179430612/1/98New YorkCompassStore183073/7/98LondonHow to Win Foreign FriendsStore1263082/8/98LondonHat - polar explorerStore1343099/30/98ParisGeo positioning systemCatalog4118031011/9/98LondonHammockStore42203115/22/98New YorkHammockStore42203127/30/98TokyoCamel saddleStore15983137/1/98ParisGeo positioning systemStore25903142/26/98ParisHow to Win Foreign FriendsStore41043152/24/98ParisHammockStore42203168/9/98LondonHat - polar explorerWeb1343173/25/98LondonElephant polo stickStore4140831811/7/98ParisHow to Win Foreign FriendsStore41043195/14/98LondonHat - polar explorerStore41363209/25/98ParisHow to Win Foreign FriendsStore25232110/21/98TokyoHow to Win Foreign FriendsCatalog2523221/5/98LondonElephant polo stickStore135232312/3/98LondonHat - polar explorerStore1343248/1/98LondonCamel saddleStore15983257/20/98LondonCamel saddleStore529903269/9/98ParisElephant polo stickStore414083276/10/98LondonBoots - snakeproofStore21903289/27/98LondonExploring in 10 Easy LessonsStore4803293/20/98LondonHammockStore42203306/7/98LondonCamel saddleStore211963313/2/98New YorkHat - polar explorerStore31023322/11/98LondonExploring in 10 Easy LessonsStore2403334/23/98LondonHow to Win Foreign FriendsStore410433411/21/98ParisHat - polar explorerStore413633512/16/98New YorkHow to Win Foreign FriendsStore2523366/3/98LondonHow to Win Foreign FriendsStore2523372/27/98LondonElephant polo stickStore310563381/7/98LondonHammockStore211033910/14/98LondonElephant polo stickCatalog3105634011/2/98LondonCamel saddleWeb211963417/10/98ParisHammockStore52753425/29/98ParisCompassWeb21634312/2/98SydneyHammockStore1553441/14/98New YorkCompassCatalog3243457/29/98TokyoCamel saddleStore159834611/14/98ParisElephant polo stickStore310563474/17/98TokyoGeo positioning systemStore411803482/18/98LondonCamel saddleStore2119634910/15/98SydneyHow to Win Foreign FriendsStore2523504/7/98LondonMap caseCatalog46035110/14/98LondonExploring in 10 Easy LessonsStore1203528/20/98ParisHat - polar explorerCatalog31023532/14/98SydneyHat - polar explorerStore31023543/13/98LondonBoots - snakeproofCatalog54753558/8/98New YorkElephant polo stickStore27043565/22/98LondonHat - polar explorerCatalog1343575/12/98New YorkCamel saddleCatalog2119635810/23/98TokyoMap caseStore3453598/13/98LondonExploring in 10 Easy LessonsStore36036011/14/98LondonHow to Win Foreign FriendsStore3783614/1/98ParisExploring in 10 Easy LessonsStore51003625/15/98LondonCompassStore2163636/1/98LondonBoots - snakeproofStore438036412/27/98LondonHammockCatalog21103657/19/98TokyoElephant polo stickWeb27043668/28/98ParisCompassStore4323674/22/98ParisCamel saddleStore423923683/31/98LondonGeo positioning systemStore12953692/25/98ParisGeo positioning systemStore411803703/7/98SydneyElephant polo stickStore310563713/10/98LondonGeo positioning systemCatalog388537211/23/98ParisHow to Win Foreign FriendsCatalog3783738/19/98LondonHat - polar explorerStore31023743/20/98TokyoGeo positioning systemStore5147537511/25/98ParisExploring in 10 Easy LessonsStore1203762/6/98SydneyGeo positioning systemCatalog25903773/17/98ParisHat - polar explorerStore2683781/19/98New YorkExploring in 10 Easy LessonsCatalog51003799/12/98LondonHow to Win Foreign FriendsStore3783803/7/98TokyoExploring in 10 Easy LessonsStore51003819/4/98LondonHat - polar explorerStore1343825/23/98New YorkHammockWeb31653837/5/98ParisCompassStore4323843/29/98ParisCamel saddleStore211963852/18/98LondonCamel saddleStore423923865/11/98LondonHow to Win Foreign FriendsStore37838711/26/98LondonHat - polar explorerStore26838810/31/98TokyoCompassStore4323893/31/98ParisGeo positioning systemStore25903904/30/98ParisHammockStore52753918/9/98LondonExploring in 10 Easy LessonsStore36039212/6/98New YorkCompassStore183933/23/98New YorkCamel saddleStore211963946/7/98SydneyExploring in 10 Easy LessonsStore36039512/19/98SydneyHammockStore21103965/13/98New YorkMap caseCatalog2303972/26/98LondonExploring in 10 Easy LessonsStore51003986/21/98New YorkExploring in 10 Easy LessonsStore2403996/24/98ParisHat - polar explorerStore1344007/13/98TokyoHammockCatalog21104012/2/98LondonElephant polo stickStore27044023/19/98ParisExploring in 10 Easy LessonsStore51004037/4/98LondonCamel saddleStore211964044/4/98LondonHat - polar explorerStore51704054/17/98LondonExploring in 10 Easy LessonsStore36040611/23/98ParisCamel saddleStore423924078/2/98ParisCamel saddleStore4239240812/3/98ParisElephant polo stickStore414084096/1/98TokyoHow to Win Foreign FriendsStore2524101/26/98LondonElephant polo stickStore414084119/18/98LondonBoots - snakeproofStore43804128/8/98LondonCamel saddleCatalog423924134/19/98New YorkBoots - snakeproofCatalog43804146/5/98LondonMap caseCatalog57541512/20/98LondonExploring in 10 Easy LessonsStore36041612/19/98ParisHow to Win Foreign FriendsStore41044177/26/98New YorkCamel saddleCatalog4239241812/1/98LondonHammockCatalog42204199/20/98LondonElephant polo stickStore27044205/8/98LondonCompassStore184218/28/98LondonCompassStore2164224/7/98LondonCompassStore3244233/30/98LondonGeo positioning systemStore5147542410/30/98New YorkCompassStore21642510/15/98LondonExploring in 10 Easy LessonsCatalog51004261/27/98LondonHow to Win Foreign FriendsStore2524272/11/98LondonMap caseStore3454283/8/98ParisExploring in 10 Easy LessonsStore4804293/29/98TokyoCamel saddleStore529904304/22/98ParisHammockCatalog21104315/31/98LondonElephant polo stickStore414084327/16/98TokyoCamel saddleStore211964334/6/98ParisHammockWeb31654344/5/98LondonGeo positioning systemStore12954359/29/98New YorkHammockStore31654367/22/98LondonCamel saddleStore211964378/3/98LondonHat - polar explorerStore1344383/6/98LondonExploring in 10 Easy LessonsStore3604398/27/98LondonCamel saddleStore529904405/15/98New YorkHow to Win Foreign FriendsStore3784415/1/98LondonHow to Win Foreign FriendsStore513044212/23/98LondonHow to Win Foreign FriendsCatalog41044431/11/98New YorkExploring in 10 Easy LessonsStore12044412/2/98LondonElephant polo stickStore414084459/2/98TokyoHow to Win Foreign FriendsCatalog3784468/26/98LondonHow to Win Foreign FriendsStore51304472/20/98LondonHammockStore52754481/28/98LondonExploring in 10 Easy LessonsStore12044910/1/98LondonCamel saddleStore3179445011/15/98SydneyHat - polar explorerStore2684517/6/98New YorkHow to Win Foreign FriendsWeb3784527/4/98New YorkElephant polo stickStore13524537/16/98LondonCompassWeb4324543/6/98LondonCamel saddleStore3179445511/9/98LondonCompassStore32445610/21/98ParisHammockStore31654578/6/98SydneyHow to Win Foreign FriendsStore41044586/30/98LondonCamel saddleStore3179445911/16/98ParisHat - polar explorerStore2684608/21/98TokyoCamel saddleStore317944616/11/98ParisCamel saddleStore15984623/16/98TokyoHammockStore211046312/27/98New YorkExploring in 10 Easy LessonsWeb51004644/6/98ParisHat - polar explorerStore31024654/10/98TokyoExploring in 10 Easy LessonsCatalog3604668/13/98ParisBoots - snakeproofStore21904679/8/98LondonHat - polar explorerStore31024689/23/98LondonCamel saddleStore211964693/31/98TokyoCamel saddleCatalog317944702/11/98LondonExploring in 10 Easy LessonsWeb3604719/29/98New YorkExploring in 10 Easy LessonsStore51004728/18/98LondonHat - polar explorerStore41364731/26/98ParisHammockStore21104749/23/98ParisGeo positioning systemStore411804752/26/98LondonGeo positioning systemStore25904768/24/98ParisHammockStore21104773/24/98ParisExploring in 10 Easy LessonsStore48047810/24/98TokyoElephant polo stickCatalog414084798/29/98LondonCompassStore4324805/10/98SydneyBoots - snakeproofStore54754815/7/98SydneyMap caseStore1154828/28/98ParisGeo positioning systemStore411804834/27/98LondonCompassStore1848412/4/98LondonGeo positioning systemStore38854851/9/98ParisElephant polo stickStore4140848611/27/98LondonCamel saddleStore15984875/15/98New YorkGeo positioning systemStore129548812/9/98TokyoMap caseStore46048911/9/98ParisCompassStore1849011/26/98New YorkHammockCatalog42204919/2/98LondonHammockStore21104927/8/98ParisHow to Win Foreign FriendsCatalog41044932/19/98ParisHammockCatalog42204948/30/98SydneyHow to Win Foreign FriendsStore41044955/18/98New YorkExploring in 10 Easy LessonsStore12049610/28/98ParisBoots - snakeproofStore21904975/24/98New YorkHow to Win Foreign FriendsStore12649811/27/98LondonExploring in 10 Easy LessonsCatalog4804992/17/98ParisHat - polar explorerStore41365003/10/98SydneyHow to Win Foreign FriendsCatalog1265015/9/98LondonCamel saddleWeb211965023/24/98LondonElephant polo stickStore270450310/30/98LondonElephant polo stickStore517605046/24/98ParisHow to Win Foreign FriendsCatalog25250511/15/98LondonGeo positioning systemStore514755066/30/98ParisHammockCatalog211050710/3/98ParisCompassCatalog4325088/31/98ParisExploring in 10 Easy LessonsCatalog2405095/19/98ParisHat - polar explorerWeb31025109/22/98ParisCamel saddleCatalog529905119/28/98LondonHow to Win Foreign FriendsWeb410451210/9/98New YorkExploring in 10 Easy LessonsStore2405132/9/98ParisHow to Win Foreign FriendsCatalog41045149/28/98LondonCamel saddleCatalog4239251511/30/98LondonHow to Win Foreign FriendsStore2525165/11/98SydneyCompassCatalog4325175/14/98SydneyMap caseStore3455183/5/98SydneyCompassCatalog3245198/3/98ParisHammockCatalog15552011/14/98LondonHow to Win Foreign FriendsStore3785212/1/98ParisGeo positioning systemStore259052210/18/98New YorkExploring in 10 Easy LessonsStore2405233/11/98ParisGeo positioning systemWeb12955243/12/98ParisExploring in 10 Easy LessonsWeb2405251/20/98SydneyCompassStore2165263/19/98LondonElephant polo stickStore13525278/3/98LondonHat - polar explorerCatalog310252811/15/98LondonGeo positioning systemStore38855294/26/98SydneyHat - polar explorerCatalog41365302/8/98ParisGeo positioning systemStore38855319/25/98TokyoCompassStore4325329/21/98LondonHow to Win Foreign FriendsStore51305331/19/98ParisCamel saddleStore15985343/17/98SydneyCamel saddleStore317945353/17/98ParisCompassStore2165362/1/98ParisMap caseStore4605375/10/98ParisHow to Win Foreign FriendsStore3785381/20/98ParisGeo positioning systemStore129553910/3/98TokyoElephant polo stickStore27045404/20/98LondonCompassStore3245414/12/98SydneyElephant polo stickCatalog414085424/22/98TokyoMap caseWeb34554312/20/98LondonHat - polar explorerStore31025447/31/98TokyoHat - polar explorerStore26854510/28/98ParisExploring in 10 Easy LessonsStore4805468/16/98New YorkExploring in 10 Easy LessonsStore24054712/23/98LondonElephant polo stickStore13525488/5/98ParisHammockStore21105496/19/98LondonCompassStore5405504/11/98ParisCompassStore5405516/12/98SydneyHammockStore42205524/12/98LondonMap caseStore2305536/1/98ParisExploring in 10 Easy LessonsStore1205542/1/98LondonGeo positioning systemStore259055510/25/98ParisHat - polar explorerStore413655610/7/98LondonGeo positioning systemStore514755573/20/98LondonCamel saddleWeb15985587/31/98TokyoBoots - snakeproofStore54755595/5/98ParisCamel saddleStore529905606/18/98TokyoCamel saddleStore4239256112/24/98ParisElephant polo stickStore4140856210/4/98New YorkCompassStore32456311/15/98ParisCompassStore5405649/18/98New YorkExploring in 10 Easy LessonsStore48056512/3/98LondonHat - polar explorerStore413656612/22/98TokyoElephant polo stickStore4140856712/27/98LondonCompassStore21656812/27/98New YorkCompassStore3245698/6/98LondonHow to Win Foreign FriendsStore51305706/30/98LondonHow to Win Foreign FriendsStore25257111/8/98LondonElephant polo stickCatalog13525726/8/98ParisExploring in 10 Easy LessonsStore36057312/9/98ParisMap caseStore2305741/11/98New YorkCamel saddleStore4239257512/28/98ParisHat - polar explorerStore41365765/12/98LondonCompassStore2165777/31/98ParisBoots - snakeproofStore32855786/4/98LondonHammockStore422057912/15/98LondonCamel saddleStore317945801/27/98ParisCamel saddleStore529905816/9/98ParisHammockStore52755828/18/98New YorkCompassStore43258311/23/98LondonExploring in 10 Easy LessonsCatalog2405844/28/98New YorkHammockStore527558510/18/98ParisGeo positioning systemCatalog4118058611/19/98LondonMap caseStore4605874/27/98New YorkGeo positioning systemCatalog12955881/28/98ParisBoots - snakeproofStore43805893/23/98ParisHow to Win Foreign FriendsCatalog51305908/2/98ParisHat - polar explorerStore31025916/25/98LondonBoots - snakeproofStore21905922/23/98ParisHow to Win Foreign FriendsStore410459310/11/98LondonElephant polo stickStore4140859410/20/98ParisCompassStore1859512/4/98SydneyHow to Win Foreign FriendsStore2525962/7/98LondonMap caseStore3455972/18/98ParisHat - polar explorerStore1345982/25/98TokyoMap caseCatalog2305993/30/98SydneyHammockCatalog211060010/13/98TokyoHow to Win Foreign FriendsStore2526015/28/98New YorkElephant polo stickStore27046029/1/98LondonExploring in 10 Easy LessonsStore2406039/14/98LondonCamel saddleCatalog211966046/30/98LondonCamel saddleStore317946055/27/98New YorkHow to Win Foreign FriendsStore2526063/22/98LondonExploring in 10 Easy LessonsStore4806073/17/98TokyoMap caseStore2306084/17/98LondonHat - polar explorerStore2686091/20/98LondonGeo positioning systemStore259061010/9/98ParisCompassStore5406117/10/98ParisGeo positioning systemStore25906128/22/98New YorkGeo positioning systemStore12956137/10/98ParisHow to Win Foreign FriendsStore2526148/12/98LondonCamel saddleStore211966155/22/98TokyoGeo positioning systemStore411806165/18/98ParisHat - polar explorerCatalog51706176/15/98ParisMap caseStore5756185/21/98LondonExploring in 10 Easy LessonsStore1206196/4/98LondonMap caseStore3456208/22/98LondonHat - polar explorerStore517062110/12/98TokyoMap caseStore1156224/11/98ParisElephant polo stickStore27046238/25/98LondonHat - polar explorerCatalog26862412/27/98LondonBoots - snakeproofWeb1956256/5/98ParisElephant polo stickStore414086269/30/98ParisHat - polar explorerStore1346278/9/98ParisHammockStore21106285/15/98SydneyHat - polar explorerStore41366297/21/98LondonHow to Win Foreign FriendsStore12663010/28/98ParisMap caseStore2306311/7/98LondonElephant polo stickStore414086322/23/98LondonCompassStore4326336/6/98ParisCamel saddleCatalog3179463412/28/98LondonHow to Win Foreign FriendsStore3786359/30/98LondonCamel saddleCatalog211966363/3/98LondonGeo positioning systemCatalog25906377/8/98ParisHammockStore52756385/2/98ParisHat - polar explorerStore51706391/27/98SydneyExploring in 10 Easy LessonsCatalog3606404/26/98LondonMap caseStore3456417/30/98LondonExploring in 10 Easy LessonsStore2406421/26/98LondonExploring in 10 Easy LessonsStore4806434/3/98LondonGeo positioning systemCatalog129564410/19/98ParisGeo positioning systemStore12956455/27/98New YorkHat - polar explorerStore2686467/27/98LondonCamel saddleStore15986477/23/98LondonGeo positioning systemWeb12956485/22/98LondonGeo positioning systemStore25906491/21/98LondonHammockStore15565010/22/98LondonGeo positioning systemStore25906514/9/98ParisCamel saddleStore317946528/14/98ParisCompassStore5406536/14/98LondonHammockStore31656546/23/98LondonMap caseStore3456556/5/98ParisHammockCatalog21106567/2/98LondonHow to Win Foreign FriendsStore37865711/19/98LondonHat - polar explorerStore31026584/1/98LondonMap caseCatalog11565910/10/98TokyoMap caseWeb46066012/15/98LondonElephant polo stickCatalog13526618/5/98LondonGeo positioning systemStore129566212/8/98LondonGeo positioning systemStore411806631/5/98TokyoHow to Win Foreign FriendsStore41046643/29/98LondonGeo positioning systemStore259066512/21/98TokyoGeo positioning systemStore514756661/15/98LondonBoots - snakeproofStore547566712/6/98TokyoHat - polar explorerCatalog13466810/17/98ParisHammockStore42206698/22/98SydneyExploring in 10 Easy LessonsCatalog51006703/21/98TokyoHat - polar explorerWeb1346715/26/98LondonHow to Win Foreign FriendsStore25267212/7/98ParisHammockStore52756733/22/98New YorkGeo positioning systemCatalog129567410/18/98ParisCamel saddleStore423926756/23/98LondonHat - polar explorerStore31026769/27/98ParisGeo positioning systemStore514756774/6/98SydneyElephant polo stickStore414086783/5/98ParisCompassCatalog2166796/22/98ParisHammockCatalog21106808/31/98LondonExploring in 10 Easy LessonsStore510068110/5/98LondonCamel saddleCatalog317946829/9/98SydneyExploring in 10 Easy LessonsCatalog2406834/24/98LondonBoots - snakeproofStore1956845/6/98ParisHat - polar explorerStore2686852/8/98LondonHat - polar explorerStore2686862/11/98ParisElephant polo stickStore13526879/29/98ParisGeo positioning systemStore259068811/12/98New YorkCompassStore2166897/7/98LondonHammockCatalog21106907/13/98ParisHow to Win Foreign FriendsStore25269112/17/98New YorkCamel saddleCatalog2119669210/24/98LondonHammockStore211069312/14/98ParisHat - polar explorerStore2686945/19/98ParisHat - polar explorerCatalog13469511/3/98LondonElephant polo stickStore414086968/11/98ParisElephant polo stickStore310566974/21/98ParisMap caseStore11569810/1/98ParisHammockStore211069910/24/98LondonHow to Win Foreign FriendsStore51307009/8/98ParisCamel saddleCatalog423927013/29/98LondonExploring in 10 Easy LessonsCatalog51007021/10/98LondonMap caseWeb1157036/19/98ParisHat - polar explorerStore2687043/14/98TokyoGeo positioning systemStore259070512/1/98TokyoHat - polar explorerCatalog41367069/4/98ParisHow to Win Foreign FriendsStore410470711/7/98ParisExploring in 10 Easy LessonsCatalog1207089/23/98LondonGeo positioning systemStore38857094/4/98SydneyGeo positioning systemWeb25907102/1/98LondonExploring in 10 Easy LessonsStore3607114/10/98ParisElephant polo stickStore517607129/24/98SydneyExploring in 10 Easy LessonsStore51007137/19/98LondonHat - polar explorerStore31027146/22/98LondonCompassStore3247156/7/98ParisElephant polo stickCatalog5176071610/29/98New YorkMap caseCatalog5757171/18/98ParisCamel saddleStore423927187/16/98SydneyHammockCatalog52757194/13/98ParisHat - polar explorerCatalog31027206/29/98SydneyHammockStore52757216/7/98ParisHat - polar explorerStore41367229/20/98LondonHow to Win Foreign FriendsCatalog2527239/28/98LondonHow to Win Foreign FriendsStore12672410/30/98LondonHow to Win Foreign FriendsStore513072510/1/98ParisExploring in 10 Easy LessonsStore2407266/16/98ParisMap caseStore34572712/24/98LondonHammockCatalog31657285/29/98LondonHat - polar explorerStore310272911/18/98LondonBoots - snakeproofStore21907307/23/98TokyoElephant polo stickWeb310567315/26/98LondonElephant polo stickStore517607323/4/98LondonGeo positioning systemStore12957331/7/98ParisHat - polar explorerWeb1347347/31/98LondonElephant polo stickCatalog5176073510/12/98TokyoMap caseStore2307362/12/98ParisBoots - snakeproofStore438073711/15/98SydneyGeo positioning systemStore259073810/14/98LondonBoots - snakeproofStore328573912/4/98New YorkHammockStore31657405/15/98SydneyHat - polar explorerCatalog31027414/12/98LondonGeo positioning systemCatalog411807428/31/98New YorkElephant polo stickCatalog27047437/22/98LondonElephant polo stickStore270474411/27/98LondonHat - polar explorerCatalog2687457/17/98ParisCamel saddleStore317947466/20/98LondonHammockCatalog211074710/10/98ParisHow to Win Foreign FriendsStore2527481/4/98ParisMap caseStore23074912/6/98LondonElephant polo stickStore27047507/16/98LondonCamel saddleStore5299075111/25/98LondonHow to Win Foreign FriendsCatalog41047523/22/98TokyoCompassStore187531/23/98LondonElephant polo stickStore310567542/23/98New YorkHat - polar explorerStore310275512/12/98LondonCompassCatalog4327568/14/98ParisMap caseCatalog3457579/8/98ParisExploring in 10 Easy LessonsCatalog4807589/6/98LondonHow to Win Foreign FriendsCatalog41047596/2/98LondonBoots - snakeproofStore21907605/27/98SydneyExploring in 10 Easy LessonsStore4807617/19/98SydneyHow to Win Foreign FriendsWeb41047625/10/98LondonElephant polo stickStore310567639/14/98ParisHat - polar explorerStore51707646/5/98ParisCompassStore54076511/27/98LondonGeo positioning systemStore411807664/3/98ParisHammockWeb211076711/28/98TokyoCamel saddleStore15987689/2/98TokyoHat - polar explorerStore26876911/27/98LondonCompassStore3247705/2/98LondonCamel saddleStore159877112/13/98LondonElephant polo stickStore27047729/16/98LondonExploring in 10 Easy LessonsStore2407739/26/98LondonCompassCatalog2167743/23/98LondonHow to Win Foreign FriendsStore37877510/8/98LondonCompassStore3247762/9/98ParisHat - polar explorerStore517077712/12/98TokyoGeo positioning systemStore514757781/15/98TokyoHow to Win Foreign FriendsCatalog3787799/21/98SydneyElephant polo stickStore270478012/5/98TokyoHat - polar explorerStore310278112/11/98New YorkGeo positioning systemStore411807828/26/98LondonHammockStore42207835/14/98SydneyBoots - snakeproofStore21907846/28/98SydneyGeo positioning systemStore388578510/31/98ParisCamel saddleCatalog3179478612/16/98New YorkElephant polo stickStore270478712/21/98SydneyExploring in 10 Easy LessonsStore4807886/3/98ParisHow to Win Foreign FriendsStore3787896/4/98ParisElephant polo stickCatalog27047901/26/98New YorkGeo positioning systemStore259079110/7/98ParisGeo positioning systemStore259079210/17/98TokyoGeo positioning systemStore38857937/14/98LondonMap caseStore2307947/2/98LondonHat - polar explorerStore51707956/23/98ParisHow to Win Foreign FriendsStore2527963/24/98LondonMap caseCatalog3457978/1/98SydneyMap caseStore2307988/31/98ParisMap caseCatalog4607995/7/98LondonHow to Win Foreign FriendsStore25280011/29/98ParisHat - polar explorerCatalog26880111/9/98ParisHat - polar explorerStore2688024/22/98LondonCamel saddleStore3179480310/24/98New YorkGeo positioning systemStore5147580410/10/98SydneyGeo positioning systemStore411808053/26/98New YorkBoots - snakeproofStore54758068/27/98LondonMap caseStore4608075/19/98SydneyHat - polar explorerStore26880811/1/98TokyoHow to Win Foreign FriendsCatalog2528099/7/98ParisGeo positioning systemWeb38858109/7/98LondonCamel saddleStore211968113/5/98TokyoElephant polo stickStore27048121/21/98New YorkCompassStore4328133/24/98New YorkElephant polo stickStore27048146/13/98TokyoHammockStore211081511/30/98LondonElephant polo stickWeb414088163/17/98New YorkElephant polo stickStore310568175/23/98TokyoCamel saddleStore211968183/4/98ParisExploring in 10 Easy LessonsStore4808199/24/98TokyoExploring in 10 Easy LessonsStore2408203/21/98New YorkBoots - snakeproofStore1958214/29/98TokyoBoots - snakeproofStore32858224/18/98LondonHow to Win Foreign FriendsCatalog1268233/3/98ParisElephant polo stickStore414088244/20/98New YorkGeo positioning systemWeb411808254/23/98New YorkElephant polo stickStore270482612/28/98TokyoMap caseCatalog4608277/23/98ParisGeo positioning systemCatalog411808282/10/98ParisHow to Win Foreign FriendsCatalog2528291/21/98ParisHammockStore52758301/11/98ParisHow to Win Foreign FriendsStore2528312/27/98LondonCompassCatalog2168325/10/98ParisGeo positioning systemStore388583311/30/98ParisCompassStore5408343/4/98TokyoHat - polar explorerStore31028355/10/98LondonElephant polo stickWeb13528365/22/98ParisCamel saddleStore317948375/30/98ParisMap caseStore3458382/8/98LondonCamel saddleStore529908395/31/98LondonCompassStore4328401/31/98LondonGeo positioning systemStore514758413/26/98TokyoCompassStore32484211/6/98TokyoCompassStore54084312/1/98LondonElephant polo stickStore414088441/19/98SydneyMap caseCatalog5758457/29/98ParisBoots - snakeproofCatalog54758469/25/98LondonMap caseStore4608478/7/98LondonHammockCatalog31658488/2/98LondonHat - polar explorerStore41368491/15/98LondonElephant polo stickStore414088503/14/98SydneyHow to Win Foreign FriendsCatalog41048513/23/98LondonExploring in 10 Easy LessonsStore4808525/13/98New YorkCamel saddleStore2119685310/25/98LondonCamel saddleStore15988543/29/98LondonElephant polo stickStore4140885510/27/98LondonGeo positioning systemStore25908565/6/98ParisElephant polo stickStore5176085711/13/98ParisHat - polar explorerStore413685812/5/98LondonHammockStore31658595/5/98LondonHow to Win Foreign FriendsWeb3788603/29/98TokyoHow to Win Foreign FriendsWeb410486110/15/98ParisCamel saddleStore3179486211/12/98LondonMap caseStore2308633/1/98New YorkExploring in 10 Easy LessonsStore24086410/17/98LondonCamel saddleCatalog529908653/11/98SydneyHammockStore52758663/9/98LondonMap caseStore5758678/16/98LondonHammockCatalog42208686/19/98ParisCamel saddleWeb4239286912/4/98LondonHow to Win Foreign FriendsWeb25287010/20/98ParisHat - polar explorerStore31028716/18/98LondonCompassStore5408723/22/98LondonGeo positioning systemStore25908738/21/98LondonGeo positioning systemStore25908747/28/98LondonCompassStore1887511/12/98LondonElephant polo stickStore310568764/28/98New YorkGeo positioning systemStore388587710/8/98LondonHammockStore1558787/6/98ParisGeo positioning systemCatalog25908798/12/98LondonHow to Win Foreign FriendsStore25288012/30/98ParisExploring in 10 Easy LessonsStore3608818/11/98LondonCamel saddleStore2119688210/23/98ParisCompassStore54088312/6/98SydneyCompassStore3248849/14/98TokyoHow to Win Foreign FriendsStore41048854/13/98LondonCamel saddleStore15988864/11/98LondonElephant polo stickStore13528879/10/98New YorkGeo positioning systemStore4118088812/9/98LondonGeo positioning systemCatalog411808895/29/98New YorkExploring in 10 Easy LessonsCatalog2408904/7/98SydneyGeo positioning systemStore38858914/19/98ParisHow to Win Foreign FriendsStore41048926/10/98LondonCompassStore3248933/10/98LondonHat - polar explorerStore1348949/2/98LondonCompassStore43289512/27/98TokyoElephant polo stickStore27048969/3/98ParisCamel saddleCatalog211968975/30/98ParisHow to Win Foreign FriendsCatalog2528983/25/98ParisExploring in 10 Easy LessonsStore24089912/3/98LondonHow to Win Foreign FriendsWeb2529007/13/98ParisHow to Win Foreign FriendsStore3789017/13/98New YorkCompassStore43290212/25/98LondonHammockStore42209034/20/98SydneyCamel saddleCatalog15989049/2/98LondonCompassCatalog5409059/16/98LondonCompassStore189063/25/98New YorkExploring in 10 Easy LessonsStore4809071/15/98LondonElephant polo stickStore310569089/13/98SydneyCompassStore5409097/9/98ParisCamel saddleStore529909103/5/98ParisHammockCatalog52759118/8/98ParisMap caseCatalog4609127/5/98LondonCompassStore4329135/11/98LondonCompassStore43291410/25/98LondonMap caseStore2309153/14/98LondonGeo positioning systemStore38859167/25/98New YorkGeo positioning systemStore388591710/1/98LondonGeo positioning systemStore514759187/30/98LondonElephant polo stickStore27049198/18/98ParisCamel saddleCatalog317949202/9/98LondonElephant polo stickStore414089218/1/98LondonCamel saddleCatalog423929224/16/98LondonHow to Win Foreign FriendsStore41049238/5/98New YorkGeo positioning systemStore25909243/24/98LondonBoots - snakeproofStore328592511/23/98LondonElephant polo stickStore27049269/20/98LondonExploring in 10 Easy LessonsStore48092712/24/98LondonCamel saddleStore529909286/21/98LondonBoots - snakeproofStore32859295/16/98LondonElephant polo stickStore414089304/29/98ParisGeo positioning systemStore411809318/27/98LondonHow to Win Foreign FriendsStore3789329/3/98New YorkHammockStore42209334/1/98LondonCompassStore4329346/1/98ParisGeo positioning systemStore38859359/18/98LondonExploring in 10 Easy LessonsStore36093612/7/98SydneyGeo positioning systemStore12959376/11/98LondonHat - polar explorerStore31029388/6/98ParisGeo positioning systemStore38859397/27/98SydneyCamel saddleStore3179494012/28/98TokyoExploring in 10 Easy LessonsStore4809411/15/98LondonCamel saddleStore423929421/26/98LondonHat - polar explorerStore13494312/13/98ParisHammockStore1559444/25/98LondonCompassStore3249457/11/98LondonCamel saddleWeb423929461/30/98LondonExploring in 10 Easy LessonsWeb510094712/16/98LondonHat - polar explorerStore31029487/27/98ParisHat - polar explorerStore1349495/19/98New YorkExploring in 10 Easy LessonsStore51009507/8/98ParisHammockWeb52759516/12/98LondonCompassStore32495210/22/98LondonElephant polo stickStore310569535/9/98ParisGeo positioning systemStore411809543/12/98New YorkHow to Win Foreign FriendsCatalog3789558/19/98ParisElephant polo stickStore27049568/20/98ParisElephant polo stickStore310569574/13/98LondonExploring in 10 Easy LessonsStore3609584/29/98SydneyHammockStore1559599/14/98LondonHammockStore15596010/2/98TokyoGeo positioning systemStore4118096112/21/98New YorkMap caseCatalog2309624/1/98SydneyExploring in 10 Easy LessonsStore1209635/30/98ParisCompassStore3249645/10/98LondonBoots - snakeproofWeb19596510/31/98LondonGeo positioning systemStore38859666/5/98SydneyHat - polar explorerStore13496712/25/98SydneyCompassCatalog3249688/28/98ParisGeo positioning systemStore25909692/5/98SydneyExploring in 10 Easy LessonsStore36097011/22/98ParisCompassStore1897110/1/98LondonGeo positioning systemStore38859722/2/98ParisElephant polo stickStore517609734/13/98TokyoGeo positioning systemStore25909748/7/98ParisHammockStore1559756/22/98ParisHat - polar explorerStore31029762/14/98TokyoGeo positioning systemCatalog25909774/16/98LondonHat - polar explorerCatalog517097811/28/98ParisHat - polar explorerCatalog51709793/30/98SydneyHat - polar explorerStore2689806/3/98SydneyHow to Win Foreign FriendsStore1269813/28/98TokyoCamel saddleStore317949825/20/98TokyoHat - polar explorerStore41369834/26/98LondonElephant polo stickStore3105698411/14/98SydneyCamel saddleStore423929857/4/98LondonCamel saddleStore529909869/21/98LondonHammockStore21109874/8/98LondonCompassCatalog2169881/24/98ParisElephant polo stickStore414089896/11/98LondonGeo positioning systemCatalog38859901/20/98ParisHammockStore1559915/31/98ParisHat - polar explorerCatalog31029922/23/98New YorkMap caseStore3459933/4/98ParisElephant polo stickStore27049947/13/98LondonGeo positioning systemCatalog38859954/6/98ParisHat - polar explorerStore2689962/9/98LondonCamel saddleCatalog529909973/12/98LondonGeo positioning systemStore411809989/21/98LondonMap caseStore5759994/22/98New YorkExploring in 10 Easy LessonsStore48010007/22/98New YorkBoots - snakeproofWeb4380

Pivot Table ExampleCatalogStoreWebQuestion 1Create a simple pivot showing revenue of city along with channel ?London5031015101513009New York8712280602351Paris321661040837054Sydney5471217692749Tokyo121034261020037,810Question 2What was the value of catalog sales for London in the first quarter?0.01%Question 3What percent of the total annual sales were Tokyo Web sales in the fourth quarter?18.24%Question 4What percent of Sydney's annual sales was its Catalog sales?411Question 5What was the value of catalog sales for London in January and give details of the transactions?Qtr113156Question 6What was the value of Camel saddle sales for Paris in 1998 by quarter?Qtr214950Qtr324518Qtr48372Mar5Question 7How many Elephant polo sticks were sold in New York in each month of 1998?Apr7May2Jul1Aug7Dec7Question 8Create a Pivot showing revenue of each item and Sort the item Alphabatically from A - Z.Question 9Add a calculated field commission which is 5% revenue to the pivot created in question 8Question 10Create a Pivot from external data source i.e. MS Access

Pivot Table ExerciseRow LabelsLondonNew YorkParisSydneyTokyoGrand TotalQuestion 1Create a simple pivot showing revenue of city by month ?Jan8524721016207Feb100684910209Mar10031802349283Apr8835832227255May10736843732296Jun9413882820243Jul89191012118248Aug12023781713251Sep11533611821248Oct9326772031247Nov1088711522224Dec10537441943248Grand Total1204291923239302295929159Question 2What was the value of Store sales for Paris in the Second quarter?0.66%Question 3What percent of the total annual sales were London Web sales in the fourth quarter?21.34%Question 4What percent of Tokyo's annual sales was its Catalog sales?136Question 5What was the value of Web sales for New York in January and give details of the transactions?Qtr140Question 6What was the value of Compass for Sydney in 1998 by quarter?Qtr264Qtr348Qtr4104Jan2816Question 7How many Elephant polo sticks were sold in Paris in each month of 1998?Feb3872Mar2112Apr3520May1760Jun5984Jul1056Aug2816Sep1408Nov1056Dec2816Question 8Create a Pivot showing revenue of each item and Sort the revenue from smallest to largestQuestion 9Add a calculated field Tax Paid which is 10.3% revenue to the pivot created in question 8Question 10Create a Pivot from external data source i.e. MS Access