Comparing 2 lists and counting data from the first worksheet to fill up a second worksheet

[stouflette]

Hi Guys!

I'm a noob and need help here. I've got a spreadsheet with 2 worksheets in it.

On the first one we've got:
Name of the agent | petition REf num | Task
Each petition can generate several tasks, one line per task.

John Doe | XXXX-YYYY | NCO
John Doe | | RIL
John Doe | XERT-WWWW | RMT
Jane Doe | QSZE-AQWC | RIL


On the second worksheet:
Complete list of agents | number of petitions | Status

John Doe | 2 | OK
Jane Doe | 1 | [BLANK]

I want to be able to fill in the second worksheet automatically. For each agent in my worksheet 2, I want to check if they appear in worksheet 1 and if so count the number of petitions related.

I don't know where to start :'(

Thanks in advance for your help.

[sktneer]

Hi stouflette,
Welcome to the Forum.

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[stouflette]

Thank you Sktneer!

I've attached a file, I hope it's clear enough now.

[azumi]

Hope this helps


Cheers

[stouflette]

Hello!

You rock Azumi.

Thank you so much for your help, really appreciated.

I changed the tab a bit (the number of lines will changed in the tab week_1_after), I can get my head around the count of unique ref per agent. I managed to find a way to count the "tasks minus noc" and "noc".

[azumi]

Maybe like this? Im simplified the formula but I can't remove 0 results as blank results, hope you see the main formula is...


Cheers

[sktneer]

To hide 0's from the formula cells, custom format formula cells as 0;-0;;@

[stouflette]

Hey!

Thank you for the super fast reply. We are nearly there! The B column is still not returning the number I'm looking for. For instance, Clark Kent should have 4 in the "Ref nb" because he has 4 different refs (140815-001580 ; 140716-001545 ;
140813-002073 ; 140725-002015 ; note that some agents can work on the same REF incident at different stages). The numbers returned in C and D are perfect.

Cheers

[stouflette]

Hello!

finally made it work! *\o/*

Thanks again for your help guys.