Merging Data from separate files, looking for the new unique values

[Judge Weebles]

First post, here goes

Scenerio, I have a report daily that shows people who have not taken care of their tickets in one of our software packages. I export that file to excel, which shows me a daily list, names, departments, etc.

I have set up a macro in the 'Data' section - From Other sources - Micro soft query - Excel files - SQL
Which is

Select * from `H:\Master.xlsx`.`tabname$`
union
select * from `H:\Daily.xlsx`.`tabname$`

Which gives me a tab in the master workbook with an ongoing tally of people in the past who have not taken care of their tickets.

What I am trying to do now, using various union, left join, null etc commands but failing to figure out..

I would like to take the sheet from today, with the NEW FOLKS who were not on that list yesterday, and specifically point out those few individuals.

So Yesterday I had 40 people on my list. 10 took care of their reports, and dropped off the list, but 5 new folks joined on today.. I just want a quick little way to figure out who those new 5 are that are on the list today, but are not on that ongoing master list that was updated yesterday?

(hope I gave enough info to help - read through hundreds of threads, knowing this is an easier task, tried to look for previous posts, but found none)
Thanks

 

[Fazza]

Welcome

Maybe have a look in the help section of MS Query on joins? Or join types? There should be a description of a join that does what you want. Maybe a RIGHT OUTER JOIN, and then exclude nulls. I can't be more specific with the information you have. I think there will be some examples of the type of SQL you want in this old thread http://www.mrexcel.com/forum/excel-questions/238791-compare-sheets.html

hth

 

[Judge Weebles]

Yeah thats what I was thinking yesterday with the right/left joins, and party even the outer, but from what I can tell, they all in 1 shape or another displayed only the joined information, which I wasn't too worried about, it was the syntax of the 'exclude nulls' part I think I found no information on. Thanks... I know its close. I will keep plugging away on it..

 

[Judge Weebles]

Checked out the link you offered, and yeah it is along the lines, and I am checking out the VB code, it doesn't look too hard, but I havn't done much with it at all, and not sure exactly where to ender it, unless its pretty much in the same place as the query code goes in..

Though looking over my original question yeah I guess as much as I tried not to be too vauge, looks like I was. I will toss in a bigger pic/example and also explain the ultimate goal, since I was taking this project in steps.

Excel Master Sheet (ongoing list just to keep track over time who all made it on)
Problem ID - Date Reported - Status - Assignee (for headers though there is like 7 more headers they are unimportant - I just use the * )
000002463 - 10/11/2013 - Closed - ABCDEF
000001937 - 10/12/2013 - Closed - JFHGUR
000001425 - 10/13/2013 - Closed - ZMSDUR
000009876 - 10/15/2013 - Closed - WPFHRY

Now a daily list from information I have gathered today (the first person on the list did their work, and came off the list, though 2 new people were added on today as well) (I will take my master sheet, run the query which pulls in this new information from the daily sheet, and gets me a tally list on a new tab..)
000001937 - 10/12/2013 - Closed - JFHGUR
000001425 - 10/13/2013 - Closed - ZMSDUR
000009876 - 10/15/2013 - Closed - WPFHRY
000001638 - 10/16/2013 - Closed - FIRUGO
000009255 - 10/16/2013 - Closed - FGFGYE


So my main sheet keeps track of everyone who has been on the list...
But what also want to do is put out on a separate column/tab just the 2 new folks...
NOW the really cool neat trick to find out is also in a new column, have the date in which they were added to the master sheet... So like at the end of the week I can just click the tab and find out which tickets have been there the longest (to know whos just slacking the longest)


I am fairly sure I am trying to make tons of work over a report that is fairly simple... lol
Ideas?

 

[Fazza]

hi,

As previously, please have a look in MS Query help on joins. It has the info you need.

Info below copied from MS Query help covers it, I think.

regards

Show All
Retrieve records that two tables don't have in common

If you have two tables that have similar data<NBSP />— such as two lists (tables) of customers, and each list has some customers that aren't included on the other list<NBSP />— you can create a query that returns only the records from the first table that do not match the records in the second table. This type of query is called a subtract query or subtract join.

1. In Microsoft Query, create a query and add both tables to it.
2. Join the fields that match between the two tables (for example, the Customer ID field from two customer lists) by dragging the field in the first table to the second table.
3. Double-click the join line between the two tables.
4. In the Joins dialog box, click option 2, click Add, and then click Close.
5. Add the joined field to the query.
6. Add a field other than the joined field from the second table. Select a field that has data for every record in the second table. For example, if you joined the Customer ID fields in the two tables, you might add the Customer Name field from the second table.
7. On the View menu, click Criteria.
8. In the first criteria field, click the field you added in step 6.
9. In the first Value field, type Is Null.
10. To see the records in the first table that aren't included in the second table, click Query Now
    
    .
11. Add any additional fields that you want in the query, and then return the data to Microsoft Excel.</STRONG></STRONG></STRONG></STRONG></STRONG></STRONG></STRONG></STRONG></STRONG>

Note If you also want to see the records in the second table that don't match records in the first table, create a second query in which you follow these same steps, but reverse the order of the tables (make the second table the first table, and vice versa).

 

[Judge Weebles]

Thank you, I did not see this the first time though, but after you responded with this reply, having gone back for more reading, I came across it. Thank you.. I will go give it a try