Equivalent to Joining tables (SQL) in Excel?

  • Thread starter WWGD
  • Start date
  • #1
WWGD
Science Advisor
Gold Member
5,715
5,978
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?
 

Answers and Replies

  • #5
WWGD
Science Advisor
Gold Member
5,715
5,978
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?
 
  • #6
BWV
1,046
1,119
Generally best practice to store data in sql and use excel as a reporting / analysis tool
 
  • #7
WWGD
Science Advisor
Gold Member
5,715
5,978
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.
 
  • #8
BWV
1,046
1,119
A pivot table won’t work?
 
  • #9
WWGD
Science Advisor
Gold Member
5,715
5,978
A pivot table won’t work?

Will it allow me to do a ratio of fields in different sheets ( EDIT In same document)?
 
  • #13
WWGD
Science Advisor
Gold Member
5,715
5,978
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.
 
  • #17
BWV
1,046
1,119
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
 
  • #18
WWGD
Science Advisor
Gold Member
5,715
5,978
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.
 
  • #19
WWGD
Science Advisor
Gold Member
5,715
5,978
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:
  • #20
BWV
1,046
1,119
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 in to 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.
 
  • #21
WWGD
Science Advisor
Gold Member
5,715
5,978
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.
 
  • #22
BWV
1,046
1,119
Is it a one-to-one or one-to-many relationship on the two sheets?
 
  • #23
WWGD
Science Advisor
Gold Member
5,715
5,978
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.
 
  • #24
BWV
1,046
1,119
If it’s one-to-one then you can use the vlookup function
 
  • #25
WWGD
Science Advisor
Gold Member
5,715
5,978
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.
 

Related Threads on Equivalent to Joining tables (SQL) in Excel?

  • Last Post
Replies
8
Views
1K
  • Last Post
Replies
5
Views
2K
Replies
5
Views
2K
  • Last Post
Replies
12
Views
1K
  • Last Post
Replies
5
Views
2K
Replies
1
Views
807
Replies
8
Views
2K
  • Last Post
Replies
19
Views
2K
Replies
11
Views
899
  • Last Post
Replies
4
Views
4K
Top