Equivalent to Joining tables (SQL) in Excel?

[WWGD]

Hi All,
Sorry if this seems pretty lost.
I want to compute an expression of the type A/B with data given in different sheets, i.e., A is given in sheet i and b is given in sheet j. Now, I know that in
SQL Server ( SQL in general), one can join tables. Is there a similar way of
getting data from different sheets, i.e., something similar to joins, in Excel?

 

[Greg Bernhardt]

Something like this?
https://www.ablebits.com/office-addins-blog/2015/12/08/excel-reference-another-sheet-workbook/

 

[WWGD]

Something like this?
https://www.ablebits.com/office-addins-blog/2015/12/08/excel-reference-another-sheet-workbook/

You and your devious tricks of looking things up, Greg ;). Sorry for my laziness.

 

[Greg Bernhardt]

beat you to it @jedishrfu ! :)

 

[WWGD]

beat you to it @jedishrfu ! :)

Thanks, Greg ( Jedishfru, in intent :) ). There are a lot of ways of making things work this way within Excel.
Still, it seems it would be easier to export the Excel files into SQL Server using the Import/Export Wizard
Just wondering: Do you think it helps to learn these commands in Excel when it could all be done relatively easily by exporting data to SQL Server? I mean, is there any advantage to doing it with Excel over using SQL Server?

 

[BWV]

Generally best practice to store data in sql and use excel as a reporting / analysis tool

 

[WWGD]

Generally best practice to store data in sql and use excel as a reporting / analysis tool

Problem is when the connection between Excel and Microsoft Query is not working well . I need to do a query on my Excel data ( as I stated, with data from different sheets ), and the connection has been hanging for a while now. I am trying all sorts of hacks to get it done.

 

[BWV]

A pivot table won’t work?

 

[WWGD]

A pivot table won’t work?

Will it allow me to do a ratio of fields in different sheets ( EDIT In same document)?

 

[BWV]

You can consolidate multiple data sources.

https://support.office.com/en-us/ar...votTable-3AE257D2-CA94-49FF-A481-E9FC8ADEEEB5

 

[WWGD]

You can consolidate multiple data sources.

https://support.office.com/en-us/ar...votTable-3AE257D2-CA94-49FF-A481-E9FC8ADEEEB5

Thanks. In order to practice, I will try both the Pivot table as well as exporting data from Excel to SQL, so that I can join the tables containing the two fields.

 

[WWGD]

You can consolidate multiple data sources.

https://support.office.com/en-us/ar...votTable-3AE257D2-CA94-49FF-A481-E9FC8ADEEEB5

Do you know why I am able to connect to my (Local)SQL Server without any problem, import data from Excel to SQL Server , yet I am not able to obtain a connection to use Microsoft Query? I am kind of puzzled by this.

 

[WWGD]

In case anyone is interested , this is how I did it -- the actual formula to figure out a ratio A/B , where A,B are fields in different sheets in same document.
1) Align ordering of common fields in two sheets:
2) Then , say A,B are in columns A,B respectively in Sheets A,B, with values starting at A1,B1 resp.

3) Select empty cell, say , F, then use formula: =A_table!A1/B_table!B1

Then extend to other cells.

 

[WWGD]

A pivot table won’t work?

You can consolidate multiple data sources.

https://support.office.com/en-us/ar...votTable-3AE257D2-CA94-49FF-A481-E9FC8ADEEEB5

Thanks. Is it possible to do ratios A,B within Pivot? My solution was done using standard Excel, though I just realized I made a mistake.

 

[BWV]

Yes, its called a calculated field and you should be able to do any simple operation possible on two fields in SQL

http://www.contextures.com/excel-pivot-table-calculated-field.html

 

[WWGD]

Yes, its called a calculated field and you should be able to do any simple operation possible on two fields in SQL

http://www.contextures.com/excel-pivot-table-calculated-field.html

Thanks, but by certain reasons, I need to do it with Excel.

 

[BWV]

That is excel, but if you don’t want to calculate within the pivot table you can simply enter the formula in a column of cells referencing the two columns in the pivot table

 

[WWGD]

That is excel, but if you don’t want to calculate within the pivot table you can simply enter the formula in a column of cells referencing the two columns in the pivot table

Sorry, I misread your post above.

 

[WWGD]

One last question, please. With Pivot, is it possible to filter along an equality of rows? I mean, I have data from Sheet 1, Sheet 2, with a common field F ( in both sheets), say F1, F2 in sheets 1,2 respectively. Problem is that, while F1, F2 do overlap, not all values of F1 equal those of F2 , i.e., there is $f_{1j} \in F1$ which is not in F2 , and there are values $f_{2k} \in F2$ which are not in F1. How can I then use Pivot to calculate a new field that uses fields in Sheet1, Sheet2? Can I do an equivalent to an SQL join through filtering or otherwise?
EDIT: Basically, I am trying to find a sum of expressions f1, f2 in Sheet1, Sheet 2 respectively. Sheet1, Sheet2 share a fieldf12. Problem is values f12 in Sheet1 and Sheet2 overlap ( i.e., their intersection is not empty ), but are not equal, so I need to match f12-entries which are equal in Sheet1, Sheet2.

 

[BWV]

Backing up, if the pivot table does not allow the correct join (I have not tried this so don’t know the answer) go back to the connection. I don’t understand why you have to do this in excel rather than in a query in sql server. Perhaps the problem is that you are using Microsoft Query which is an outdated crap add-in. If the raw data comes into populate the two worksheets, why can’t the output of a query with the proper join come in instead? Rather than using MS query, you can place a SQL server query directly in the connection and the results will populate a worksheet. You can write and test the query in sql server management studio and then copy and paste it into the connection. The connection has to be updated manually and the data from the last update will remain in the worksheet so if the connection is not working, it will retain the last successful query result.

 

[WWGD]

I have no problem with using SQL Server per se, it is just that doing aggregates becomes complicated, and, worse, I am not getting the right result.

 

[BWV]

Is it a one-to-one or one-to-many relationship on the two sheets?

 

[WWGD]

Is it a one-to-one or one-to-many relationship on the two sheets?

It is 1-1, but not all pairs are related -- and that is the big problem. In sheet 1, I have the common field f1 with, vakues, say, 1,2,3,4,7,9 , and the same field in sheet 2 has values, say 2,3,6,8,9 . So some field values are common, but some are not. That is what makes it complicated.

 

[BWV]

If it’s one-to-one then you can use the vlookup function

 

[WWGD]

If it’s one-to-one then you can use the vlookup function

Yes, thanks, that is what I was thinking. Will check it out and report back here for any third parties that may be interested.

 

[BWV]

But if your query is not aggregating properly and there are a number of tables, the likely culprit is a one-to-many join

 

[WWGD]

But if your query is not aggregating properly and there are a number of tables, the likely culprit is a one-to-many join

I think the problem may be related to the variable defined as a ratio of aggregates. Aggregates are messy in SQL Server, more so if we use joins as well. I am double-checking. This is the reason i tried Excel, because the ration I did : Sum(A)/Count (B) , all done over a common field in two databases, was not outputting the right results.

 

[BWV]

You can nest queries in SQL server so if you can do two separate queries with the correct results they can be combined

 

[WWGD]

You can nest queries in SQL server so if you can do two separate queries with the correct results they can be combined

You're right, those do take some extra work. Will try it , tho. Thanks for all your comments.

 

[WWGD]

Ah, never mind. In case anyone is interested for the SQL format:
We want to compute the ratio SUM(f_1)/COUNT(f_2) , f1 in table 1 (T1), f2 in table 2 (T2);
f_12 a common field , over values in f_12 common to T1, T2.
This runs: EDIT

SELECT A/B FROM
( SELECT SUM(f_1) AS A
FROM T1 JOIN T2 on T1.f_12=T2.f_12 GROUP BY f_12) AS Table1
JOIN
(SELECT COUNT(f_2) AS B FROM T1 JOIN T2 ON T1.f_12=T2.f_12
GROUP BY f_12 ) AS Table2
ON Table1.f_12 =Table2.f_12
GROUP BY f_12

Sorry to all for dragging this along so far.

 

[Tokinabo]

Ah, never mind. In case anyone is interested for the SQL format:
We want to compute the ratio SUM(f_1)/COUNT(f_2) , f1 in table 1 (T1), f2 in table 2 (T2);
f_12 a common field , over values in f_12 common to T1, T2.
This runs: EDIT

SELECT A/B FROM
( SELECT SUM(f_1) AS A
FROM T1 JOIN T2 on T1.f_12=T2.f_12 GROUP BY f_12) AS Table1
JOIN
(SELECT COUNT(f_2) AS B FROM T1 JOIN T2 ON T1.f_12=T2.f_12
GROUP BY f_12 ) AS Table2
ON Table1.f_12 =Table2.f_12
GROUP BY f_12

Sorry to all for dragging this along so far.

Have you found a solution yet?
I do sql in Excel now and then (ODBC) using some time sheets in the same file as tables, but also sometime sheets in differents files.
I know you can do simple join operations. See this link with some .xlsx examples and a ReadMe.txt file:
https://www.dropbox.com/sh/awoes8lgpj76igb/AABZOAEq7pF36kIXKykn3PKga?dl=0

In file T_Queries, I put A and B in different columns and did A/B the old school Excel way in a formula but you can play around with more complex join queries and see how Excel handles it.. (Ribbon > Data > Connections > DoubleClick on Connection > Definition).
Those two queries use tables/sheets being in the same file (T_1_and_2.xlsx).

Another query, just for fun, uses tables/sheets being in 2 different files (T_1.xlsx and T_2.xlsx).
It's cool that one can use a virtual database of excel files being all over the place and query it using our dear friends SQL and ODBC!

Let us know it this helps a little in what you were trying to achieve.

 

[WWGD]

Have you found a solution yet?
I do sql in Excel now and then (ODBC) using some time sheets in the same file as tables, but also sometime sheets in differents files.
I know you can do simple join operations. See this link with some .xlsx examples and a ReadMe.txt file:
https://www.dropbox.com/sh/awoes8lgpj76igb/AABZOAEq7pF36kIXKykn3PKga?dl=0

In file T_Queries, I put A and B in different columns and did A/B the old school Excel way in a formula but you can play around with more complex join queries and see how Excel handles it.. (Ribbon > Data > Connections > DoubleClick on Connection > Definition)

Those two queries use tables/sheets being in the same file (T_1_and_2.xlsx).
Another query, just for fun, uses tables/sheets being in 2 different files (T_1.xlsx and T_2.xlsx).

Let us know it this helps a little in what you were trying to achieve.

Thanks. I was able to do it in SQL , but I had trouble using Microsoft query within Excel; somehow the connection was not working well ( despite the fact that the Wizard had no trouble sending data back-and-forth). Thanks for your refs., will check them out.