Equivalent to Joining tables (SQL) in Excel?

Join the discussion
Ask a follow-up here, or get your own question answered by working scientists, mathematicians and engineers — people, not an autocomplete.
Real named experts · corrections over time · the nuance an AI answer skips
Sign up Log in
31 replies · 4K views
WWGD
Science Advisor
Homework Helper
Messages
7,826
Reaction score
13,153
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?
 
Physics news on Phys.org
Greg Bernhardt said:
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?
 
Generally best practice to store data in sql and use excel as a reporting / analysis tool
 
  • Like
Likes   Reactions: WWGD
BWV said:
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.
 
A pivot table won’t work?
 
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.
 
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
 
BWV said:
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.
 
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.
 
Last edited:
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.
 
  • Like
Likes   Reactions: WWGD
Is it a one-to-one or one-to-many relationship on the two sheets?
 
BWV said:
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.
 
If it’s one-to-one then you can use the vlookup function
 
BWV said:
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.
 
But if your query is not aggregating properly and there are a number of tables, the likely culprit is a one-to-many join
 
BWV said:
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.
 
You can nest queries in SQL server so if you can do two separate queries with the correct results they can be combined
 
BWV said:
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.
 
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.
 
Last edited:
  • Like
Likes   Reactions: BWV

Similar threads

Speeding Up SQL Query: Grouping & Joining
  • BWV
  • · Replies 51 ·
  • Programming and Computer Science
2
Replies
51