Match; inner join and outer joins

[highndry]

Hi

I frequently come accross this situation and I was wondering if there is an easy way to do this. I can do this in Access but not in excel, here is the problem:

In "sheet 1"Column A" I have Jon, Albert, Tim and "Column B" I have 1,2,3; in "Sheet 2" "Column A" I have Tim, Albert, Sam and "Column B" I have 4,5,6.

Sheet 1:

A B
Jon 1
Albert 2
Tim 3

Sheet 2:

A B
Tim 4
Albert 5
Sam 6

My question is is there a formula or an "Excel query" that I can run where in Sheet 3 it will take the Match of Sheet 2 substract Sheet 1 and then include all the non match of the two sheets, like below.

Sheet 3

A B
Jon 1
Albert 3
Tim 1
Sam 6


Thanks in advance.

 

[MNpoker]

First question:

Should Jon be -1 in the final page?

Assuming yes, you can do this using pivot tables.

1) Give Column A the same name in both Sheets
2) Give column B different names
3) Goto Data / Pivot Table
4) Choose Multiple Consolodation Ranges - Click Next
5) Click next
6) Add both ranges (including the column headers) - click next
7) In 'Options' unclick Grand Total For Columns and Grand Total For Rows - Click OK
8) Click Layout and see below [In layout put the row box in the row area, the column box in the column area, and the values in the center area.]
9) Double click on 'Sum of Value' and click options
10) Goto 'Show Data as' and select 'base field' column. Difference from B
11) Click OK - Finish

Will add screen print shortly
SORRY - Colo's download crashed Excel. (It locked up). Not sure why. so no pictures.

 

[highndry]

You are correct, it should be -1. I was just using an example, I'll try your suggestion now.

 

[highndry]

Thanks a lot, it worked. You have saved me a ton of time, in the past I use to do vlookup and then substract it from the 2nd vlookup of the 2nd column and on and on......., wowa this board is great.