Excel Spreadsheet Question?

[ATC9001]

I have 2 lists of people in excel spreadsheets
One list has thier SSN and department they belong to (this list contains everyone in the company)
The second has ther SSN listed only if they have completed a specific training program.
How can I sort this easily to see who on the main list has not completed the training? The reason this is difficult is people on the second list have since left the company and it also doesn't have new people who came into the company....
I could sort both lists by SSN and then compare, but our company has close to 5000 people and that would be a little tedious.

How can I sort this so it will show SSN and department of those who are NOT on the second list?

 

[AtlantaBob]

My first thought would be to create a "new" column on the list that has everyone, and then use a combination of if(), isblank(), and lookup() functions to see if the SSN appears in the second (trained) list. It's been a long day, but essentially, if(isblank(lookup(SSN of particular person, SSN of trained people)), "","X") -- the people who appear on the "trained list" should now have an "X" next to their name, then, you can sort by the "X" column; everyone who doesn't have an "X" hasn't been trained.

Hope that helps. If it's not clear, let me know and I'll try to post again.

 

[NeoV]

ok, this is easy

file 1 - column A is SS#, column B is department
file 2 - column A is SS# - only on this list if they have completed training

use a combo of if(iserror) and a vlookup

file 1, column C, put this in:

=if(iserror(vlookup(a1,range in file 2 of SS#'s,1,false)),"No","Yes")

this will return a No if the SS # in file 1 is not found in file 2, and a Yes if the SS # is found.

the "false" part of the statement forces it to find an exact match

 

[AtlantaBob]

Thanks for that NeoV - always good to learn another way to do that. I *think* that the lookup function I posted would fail if it didn't find an exact match, but I'm not certain.

 

[Binky]

Index and match can pull the data for any employee from another worksheet or workbook.

The following assumes that sheet1 is your main list where you want to sort your data in columns A-B (SSN, name). sheet2 has the training information in columns A-C (SSN, name, training status).

=INDEX(Sheet2!$A$1:$C$6,MATCH($A1,Sheet2!$A$1:$A$6,0),3)

This looks at the SSN on sheet1 where you're doing the sorting, and searches for that SSN on the entire sheet2 range. When it finds the SSN, it pulls back the data in the same row from column 3 (the training status). You can then sort this list on sheet1 however you please.

 

[PowerEngineer]

I'm with Binky in the sense that I prefer to use INDEX and MATCH in cases like this, primarily because it does not require that the list be sorted by the lookup column (as VLOOKUP does).

If you're just looking for a yes/no answer, then (using Binky's assumptions) I'd be running the following formula down (the otherwise empty) column C on sheet1:

=IF(ISERROR(MATCH($A1,Sheet2!$A: $A,0)),"No",INDEX(Sheet2!$C: $C,MATCH($A1,Sheet2!$A: $A,0)))

which assumes that the training status for people on the training list is already yes/no.

 

[NeoV]

I'm confused on the difference between a vlookup (or it's lesser equivalent, hlookup) and the index/match functions

why use index/match instead?

 

[PowerEngineer]

I'm confused on the difference between a vlookup (or it's lesser equivalent, hlookup) and the index/match functions

why use index/match instead?

Well, it may owe a lot to personal preference as much as anything else, but...

VLOOKUP (and HLOOKUP) both require that the searched values be in the first column (or row) and that the whole table be sorted in ascending order on the values in the search column (or row) -- even when looking for an exact match. These restrictions can be a real pain, and so I long ago started using MATCH/INDEX instead. You can set up a MATCH for any column (or row) inside a table (or outside), and the column (or row) doesn't have to be orderd by value. You can then use the INDEX function to retrieve the value you want from the same table, a different table, of just some other column (or row). In summary, MATCH/INDEX is much more flexible.

IMHO...

 

[ATC9001]

Hey Everyone, I was able to combine the spreadsheets into one (cut and paste) with SSN of those who have completed training in column A and SSN and department in columns B+C and in D did a =match(B2,A:A,0) then dragged the function throughout. So if they were not trained they got a #N/A next to thier name and I did a sort alphabetically! via column D to seperate trained and untrained...then sorting by department etc.



Thanks for the tips, I didn't even think about match!

 

[NeoV]

thanks power

only thing I would add is that a vlookup does not require the target data to be sorted in any way - it will find an exact match regardless of what order it's in

I'm going to play around with match/index though, thanks.