How to collate data in Google Spreadsheets?

Question

I have two sheets with data in single Google Spreadsheets document:

SheetA:

Date       Name   CounterA

22.02.2011 Beta   101
22.02.2011 Alpha  2
21.02.2011 Alpha  1

SheetB:

Date       Name   CounterB

22.02.2011 Delta  10
22.02.2011 Gamma  27
21.02.2011 Alpha  310

Data in these sheets is imported with importXML() and is not sorted (i.e. order and number of elements on the first sheet does not match the second sheet).

I need to collate these two sheets to create a summary:

SheetC:

Date       Name   CounterA CounterB B/A

21.02.2011 Alpha         2      310 155
22.02.2011 Alpha         1        0   0
22.02.2011 Beta        101        0   0
22.02.2011 Gamma         0       27 inf
22.02.2011 Delta         0       10 inf

Any hints on how can I do this?

Answer 1

The new pivot table feature can handle your output providing you structure your data correctly.

Counter A or Counter B isn't the correct name for your column with the numbers in it. What are those numbers? Are they an amount? Name the columns "Amount" instead. If you want to assign each row to "Counter A", you need a new column named "Counter" with a Value of A in each row that applies.

If all the data coming in on sheet A is always going to be in counter A, Add a column called Counter and assign everything in that column the value of A.

You can do this by adding an arrayformula() to column d that inserts "A" all the way down where there is data to the left.

=ArrayFormula( IF(C$2:C="","","A"))

Now that your data is structured correctly, you need to combine it on one sheet as if you were doing a union SQL query.

You will need to install a custom function to merge the data from both your sheets into one sheet.

Vmerge is a google apps script function you can install and use to accomplish this. Here is the Help document.

With all you data properly structured into a single table on one sheet. Select it all and Make a Pivot table. From the Menue, choose: Data > Pivot Table Report...

1. Select Date and Name on Rows.
2. Select Counter on Columns.

3. Select Amount for values.

4. Turn on totals for rows.

Answer 2

If you had to do this automatically in Excel, you may have to write a macro to consolidate info from 2 sheets to a new third sheet. Google Docs Spreadsheet has Scripts (from the menu, Tools > Scripts) but I did not see anything in the Script Gallery that can automatically combine info from multiple sheets into a new sheet.

So I think, you could write your own script or build everything manually by referring to each cell in the summary sheet using a syntax like this: =Sheet1!$A$1 to represent the first cell in the first row of Sheet1. Change the formula for each cell in the third sheet according to the range that you need.

Answer 3

You can use vlookup or =CONCATENATE(A5,C5)

Answer 4

=unique({A1:A;B1:B}) ;) 

Works smooth!