Get a Table from a Web Page with an XML Request

from www.dailydoesofexcel.com

Get a Table from a Web Page with an XML Request:

I’m tired of trying to remember the library names and the syntax for doing this. I’m putting here so when I search for XMLRequest, Web Request, HTTPRequest, I can find it.

Sub GetData()



Dim oHttp As MSXML2.XMLHTTP

Dim sHtml As String

Dim hDoc As HTMLDocument

Dim hTable As HTMLTable

Dim hRow As HTMLTableRow

Dim hCell As HTMLTableCell

Dim rStart As Range



Const sURL As String = "http://www.contextures.com/xlsampledata01.html"



Set oHttp = New MSXML2.XMLHTTP

Set hDoc = New HTMLDocument

Set rStart = Sheet1.Range("A1")



'Send the web request

oHttp.Open "GET", sURL

oHttp.send



'Give it enough time to process

Do

DoEvents

Loop Until oHttp.readyState = 4



'put the web page into an HTML Document

hDoc.body.innerHTML = oHttp.responseText



'Find the right table and write it to a sheet

For Each hTable In hDoc.all.tags("TABLE")

If hTable.Rows(0).Cells(0).innerText = "OrderDate" Then

For Each hRow In hTable.Rows

For Each hCell In hRow.Cells

rStart.Offset(hRow.RowIndex, hCell.cellIndex).Value = hCell.innerText

Next hCell

Next hRow

End If

Next hTable



End Sub

It sends a request to the sample data page over at Contextures, jams that into an HTML Doc, then writes the table out to a sheet.

Change All Pivot Tables With One Selection

from contextures.com

Change All Pivot Tables With One Selection:

Happy New Year! I hope you had a safe and happy New Year's Eve celebration, and are off to a good start in 2012. Things got a bit rowdy at the Contextures office party, and I found these guys passed out on the floor, the next morning.

Change All Pivot Table Filters

Despite the wild parties, I was able to get some work done over the holidays. There is a new sample file on the Contextures website, that changes all the pivot tables, when you change a report filter in one pivot table.

For example, if you change the "Item" report filter in one pivot table, all the other pivot tables with an "Item" filter will change. They get the same report filter settings that were in the pivot table that you changed.

Select Multiple Items

In this version of the sample file, the "Select Multiple Items" setting is also changed, to match the setting that is in the pivot table that you changed.

In the screen shot below, the Item field has the "Select Multiple Items" setting turned off. If any other pivot tables in the workbook have an "Items" filter, the "Select Multiple Items" setting for those fields will also change.

How It Works

The multiple pivot table filtering works with event programming. There is Worksheet_PivotTableUpdate code on each worksheet, and it runs when any pivot table on that worksheet is changed or refreshed.

For each report filter field, the code checks for the Select Multiple Items setting, and changes it on all the pivot tables with the same report filter field. The code loops through all the worksheets in the file, and through each pivot table on each sheet.

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)

Dim wsMain As Worksheet
Dim ws As Worksheet
Dim ptMain As PivotTable
Dim pt As PivotTable
Dim pfMain As PivotField
Dim pf As PivotField
Dim pi As PivotItem
Dim bMI As Boolean

On Error Resume Next
Set wsMain = ActiveSheet
Set ptMain = Target

Application.EnableEvents = False
Application.ScreenUpdating = False

For Each pfMain In ptMain.PageFields
   bMI = pfMain.EnableMultiplePageItems
   For Each ws In ThisWorkbook.Worksheets
       For Each pt In ws.PivotTables
           If ws.Name & "_" & pt <> wsMain.Name & "_" & ptMain Then
               pt.ManualUpdate = True
               Set pf = pt.PivotFields(pfMain.Name)
                       bMI = pfMain.EnableMultiplePageItems
                       With pf
                           .ClearAllFilters
                           Select Case bMI
                               Case False
                                   .CurrentPage = pfMain.CurrentPage.Value
                               Case True
                                   .CurrentPage = "(All)"
                                   For Each pi In pfMain.PivotItems
                                       .PivotItems(pi.Name).Visible = pi.Visible
                                   Next pi
                                   .EnableMultiplePageItems = bMI
                           End Select
                       End With
                       bMI = False

               Set pf = Nothing
               pt.ManualUpdate = False
           End If
       Next pt
   Next ws
Next pfMain

Application.EnableEvents = True
Application.ScreenUpdating = True

End Sub

Download the Sample File

To test the code, you can download the sample file from the Contextures website. On the Sample Excel Files page, in the Pivot Tables section, look for PT0025 - Change All Page Fields with Multiple Selection Settings. The file will work in Excel 2007 or Excel 2010, if you enable macros.

Watch the Excel Video Tutorial

To see the steps for copying the code into your worksheet, and an explanation of how the code works, you can watch this short Excel video tutorial.

Or watch on YouTube: Change All Pivot Tables When One Changes

______________________

RSS Footer: Thank you for subscribing to the RSS feed for Contextures Blog .

You can read the full article here: Change All Pivot Tables With One Selection

Please visit the Contextures page on Facebook

Use CTRL+Enter to Enter Same Data in to Multiple Cells [Quick Tip]

From chandoo.org

Use CTRL+Enter to Enter Same Data in to Multiple Cells [Quick Tip]:

Here is a quick Excel tip to kick start your week.

Sometimes, we want to enter same data in to several cells. You can use CTRL+Enter to do this in a snap.

1. Select all the cells where you want to enter the same data.


2. Type the data


3. Press CTRL+Enter


4. Done!

See the animation aside to understand how this works.

Using CTRL+Enter to fill blanks with same value

We can use this technique to fill all the blank cells too.

1. Select all the cells, Press F5


2. Select “Special”


3. Select “Blanks” to select all blank cells


4. Now type whatever you want


5. Press CTRL+Enter


6. Done!

See this demo to understand this technique.

That is all for now. I have a lot of work to do since we are re-opening VBA classes this Wednesday (11th Jan). See ya.

Do you use CTRL+Enter? Share your tips & uses?

I use CTRL+Enter often when I need to fill in the same value in multiple places. What about you? Do you use this? How do you use it? Please share using comments.

Some more awesome Excel tips for you:

• Quickly Fill Blank Cells in a Table


• Quick Comparison of Data using Row Differences


• Delete Blank Rows in Excel


• More Excel Quick Tips

Formula Forensics. No 007 – Sumproduct

from chandoo.org

Formula Forensics. No 007 – Sumproduct:

One of the most asked questions within the posts and Forums at Chandoo.org is “How Does Sumproduct work ?”.

Rahul recently asked for an example in Excels Sumproduct Formula post; Comment No. 55.

So today in Formula Forensics we will take a look at just that with a few worked examples.

Sumproduct

Excels help defines Sumproduct as:

So what are these arrays referring to:

An array in Excel can be :

A manual Array: {10;20;30}

A Range: A1:A3

A Named Range: MyRange1

Where MyRange1 is defined as a defined range in the Name Manager.

A Named Formula: MyRange2

Where MyRange2 is defined as a Formula returning a range in the Name Manager.

Lets look at each

You can follow along in the Example file on Sheet1

An Array

In C2 type: =SUMPRODUCT({10;20;30})

Excel will display 60, which is the Sum of the array elements =10+20+30

A Range

C7: =Sumproduct(C4:C6)

Excel displays 60, which is the Sum of the cells from the range C4:C6 =10+20+30

A Named Range

In the Name Manager or Name Box define a Named Range

MyRange1: =Sheet1!$C$4:$C$6

Then in C10 type:

C10: =Sumproduct(MyRange1)

Excel displays 60, which is the Sum of the range elements =10+20+30

A Named Formula

In the Name Manager define a Named Formula

MyRange2 =OFFSET(Sheet1!$C$3,1,0,3,1)

Then in C12 type:

C12: =Sumproduct(MyRange2)

Excel displays 60, which is the Sum of the range elements from cells C4:C6 =10+20+30

You may be asking why use Sumproduct when we can use a simple Sum to add up 3 numbers?

The answer is to show you what Sumproduct is doing, it is Adding up each Array element.

What about the “Product” part of Sumproduct ?

Remember back at the start where we saw the Definition of Sumproduct,

SUMPRODUCT(array1, [array2], [array3], …)

Only Array 1 is required, Array 2, Array 3 etc are optional, that’s what the square brackets [ ] mean.

Multiple Arrays

Goto Sheet 2 in the Example file:

We will look at a simple example using two arrays

The data consists of Sales data.

Often we want to know what the total sales are

We do this by adding a Sales column

Which multiplies the Qty and Price columns

And then Sum (Add) up this new column

Returning our Total Sales of 15,000

Now we can manually check the above as the numbers are simple eg: 100*20 = 2,000 etc

And we can sum up the Sales and see that we in fact had total sales of 15,000

Well this is exactly what Sumproduct is made to do:

In a Blank cell enter: =SUMPRODUCT(D4:D8,E4:E8)

Excel will return 15,000.

So what is Sumproduct doing?

Lets look inside and see what’s going on

In the Example File, Sheet2, H1 there is a copy of the data laid out as below

Note that our formula =SUMPRODUCT(D4:D8,E4:E8)

Has two Arrays

Array 1: D4:D8

Array 2: E4:E8

Note that each corresponding Array Element is multiplied together

100 x 20

20 x 200 etc

These are the products of the two Arrays

Finally the Products are Added together and the correct answer 15,000 is returned.

So Sumproduct is the Sum of the Products of the Arrays

Of course we can extend that to a large number of Arrays, columns in this case, if we wish.

Sumproduct with Logic

In the above two examples we saw that Sumproduct can Sum a single Array and can Sum the Product of two or more Arrays.

We can use that to our advantage and build logic into the arrays, allowing us to optionally include some array elements and leave out others.

How?

Sumproduct will always add up the product of all Arrays.

So by including an Array where the elements within the Array that we don’t want to Sum are Zero and the Elements within the array that we do want to Sum are 1 we can control what is included in the final Summation.

Goto our Example File on Sheet3

Lets say we only want to include the Sales from our Northern Region

One way to do this is to purely delete the other entries

But what if we could do that without altering our worksheet or there are thousands of rows of data?

This is where Sumproduct comes into its own.

What we need to do is add some logic to our equation, effectively doing:

Lets try it with Sumproduct

In Cell F12: type =SUMPRODUCT(D4:D8,E4:E8,{FALSE;TRUE;FALSE;FALSE;TRUE})

Excel displays a -

Excel doesn’t know what to do with the True/False and so converts them to 0

We can force excel to evaluate these as numbers by adding a simple “1*”

In F14: Type =SUMPRODUCT(D4:D8,E4:E8,1*{FALSE;TRUE;FALSE;FALSE;TRUE})

Excel now displays 5,000 the total sales from the North

To see what has happened in F16 type: 1*{FALSE;TRUE;FALSE;FALSE;TRUE}, but don’t press Enter press F9 instead.

Excel displays ={0;1;0;0;1}

The use of the 1* has converted each of the Array elements from a True/False to a 1,0 respectively.

So our 3 arrays are now:

Now adding an Array of 1*{FALSE;TRUE;FALSE;FALSE;TRUE} every time we wanted to add some numbers isn’t a practical solution.

Excel has the ability to work construct an Array on our behalf!

In E18: enter =SUMPRODUCT(D4:D8,E4:E8,1*(C4:C8=”North”))

Excel will display 5,000

So 1*(C4:C8=”North”) is exactly equal to our previous array 1*{FALSE;TRUE;FALSE;FALSE;TRUE}

1*(C4:C8=”North”) = 1*{FALSE;TRUE;FALSE;FALSE;TRUE}

At the heart of this is that Excel is evaluating each cell in the Range: C4:C8 against our required logic =”North” and setting up an Array for us internally.

Simplify

The power of Sumproduct is therefore in that we can now simplify and extend

In cell E20 type: North

In cell F20 type: =SUMPRODUCT(D4:D8,E4:E8,1*(C4:C8=E20))

Excel will display 5,000

This simple addition allows us to vary the Summation based on the value in E20

We don’t need to multiply our logic array by 1, we can actually use any number or another Array.

In cell F22 type: =SUMPRODUCT(D4:D8,(E4:E8)*(C4:C8=E20))

This works as (C4:C8=E20) is returning an Array of True/False which get converted to an array of 1/0’s when subject to any maths.

The Math in this case is the multiplication by the 2nd Array (E4:E8)*(C4:C8=E20)

In Cell F24 type: =SUMPRODUCT(Qty, Price *(Region=SalesRegion))

Excel will display 5,000

But notice that by using Named Ranges/Formula how simple the logic of the equation has now become.

Rahul’s Question (Multiple Criteria):

In Comment No. 55: Rahul asked, “Can you give an example work sheet of above example”

Sheet 4 in the Example File is the answer.

In Cell C23: type: =SUMPRODUCT(- -(A2:A21=”Luke Skywalker”),- -(B2:B21=”West”),C2:C21)

Excel will display 141, which is the sum of the Sales made by Luke Skywalker in the West Region.

However using what was learned above, this is better simplified to:

C26: =SUMPRODUCT((Name=SalesMan)*(Region=SalesRegion)*Sales)

The Double Unary

In the formula above Chandoo has used what is known as a Double Unary, which is 2 – signs next to each other (I have inserted a space above to make it more legible).

Two – signs are the same as saying

- -(A2:A21=”Luke Skywalker”) = -1 x -1 x (A2:A21=”Luke Skywalker”)

-1 x -1 is 1

Technically this is the most efficient way for Excel to perform any maths on the Array

- -(A2:A21=”Luke Skywalker”)

So that the Array of true/Falses made by (A2:A21=”Luke Skywalker”) is converted to an Array of 1/0’s for use in Sumproduct.

At the slight expense of speed but for improved readability and understandability by others I prefer the use of 1* instead of - – and you will mostly see that convention in my posts.

Chandoo: - -(A2:A21=”Luke Skywalker”)

Hui: 1*(A2:A21=”Luke Skywalker”)

In fact any maths performed on the array will convert its contents to an array of 1/0’s, so long as the maths doesn’t change the Arrays values

For a real good discussion on this topic have a look at the post The Venerable SUMPRODUCT at ExcelHero.com

Other Links to Sumproduct

http://chandoo.org/wp/2009/11/10/excel-sumproduct-formula/

http://chandoo.org/wp/2011/05/26/advanced-sumproduct-queries/

http://chandoo.org/wp/tag/sumproduct/

http://www.excelhero.com/blog/2010/01/the-venerable-sumproduct.html

DOWNLOAD

You can download a copy of the above file and follow along, Download Here.

OTHER POSTS IN THIS SERIES

You can learn more about how to pull Excel Formulas apart and what makes them tick in the following post:

Formula Forensic Series:

FORMULA FORENSICS NEEDS YOUR HELP !

I am running out of ideas for Formula Forensics and so I need your help.

If you have a neat formula that you would like to share and explain, try putting pen to paper and draft up a Post as Luke did in Formula Forensics 003. or like above.

If you have a formula that you would like explained but don’t want to write a post also send it in to Chandoo or Hui.

XMAS BREAK

This will be the last Formula Forensics Post for 2011, but rest assured that we will be returning in early 2012.

I’d like to take the opportunity to thank Chandoo for allowing me the space and freedom to post pretty much what ever I’ve wanted at Chandoo.org. I hope you have enjoyed my contributions to the Chandoo.org community over the past year.

On behalf of Eva and myself I’d like to wish you all a very Merry Xmas and a Happy and Safe New Year ahead

Hui…