Need help with VLookUp or need Formula

Closed
Tonya - May 2, 2012 at 01:54 PM
rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
- May 3, 2012 at 06:24 AM
Hello,

I'm trying to compare the data between Worksheet1: S_Subject and Worksheet2: A_Subject.

I need a formula that will allow me to "match" by last name AND determine how many of those actually failed and which ones passed and I want to "count" the number of times each failed and passed.

I have a file that I'd like to submit but I keep getting an error when I access speedyshare.com from work. Is there another file sharing site?

4 replies

tid hash status_desc first mid last
Pending VALDEZ
Fail VALDEZ
Pending BERGEVIN
Fail LYNEMA
Fail BACHLER


Here's the first worksheet...



Here's the 2nd...
Tid Hash status_desc first mid last former
Fail AARONS
Fail ABBOTT
Fail ABDO
Fail ADAMS
Fail ADAMS
Fail ADERHOLT
0
that didn't come out correctly. I will re-send via speedyshare once I get to my other computer and resend. Thanks
0
rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
769
May 2, 2012 at 06:01 PM
there are many site including google docs.
0
Hello,

Can someone help me with this please?

Here is the link to the file via speedyshare.com
<a href="http://speedy.sh/3vXbQ/TEST-Subjects.xlsx">Download at SpeedyShare</a>

Thank you for your help.
0
rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
769
May 2, 2012 at 06:44 PM
In your sample book could you put some sample answer too. There seem to be some thing missing here. Both sheets has name repeating. So where this formula that you are looking for goes.
0
Okay, will do it now
0
Okay,

I've updated the spreadsheet: <a href="http://speedy.sh/W4Bzm/TEST-Subjects.xlsx">Download at SpeedyShare</a>

Thanks again : )
0
rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
769
May 3, 2012 at 06:24 AM
From what I understood from your sheets, the approach that I would suggest would be
1. Copy all the names from sheet "A_Subject" to a new sheet (lets say column A)
2. use "filter-- advance feature" to remove any duplicates (lets say column B)
3. delete column A now
4.you can then use combination of IF, MATCH, SUMPRODUCT function to get what you desired.
0