Helping in Joining and summarizing

[Feras95p]

Deal all,

I need your help, I stuck in the half of the workflow. What I'm having is that.

I have an excel file which contains of these columns:

Certificate Number: Which is a unique number represented a company.

City: It's clear.

Date: Which it's clear.

Code: This what I'm going to calculated my output depending on it.

What I'm looking to have in my output is that each company which is represented by certificate number I want to know which codes are using and how many times it used this code before 20-6-2020 and how many times it used the code at 20-6-2020 and after 

So the output of the workflow should be like this for example:-

 

Whereas the input is 

I have attached the excel file and the workflow where I left :(.

It's urgent if someone can help it will be much appreciated.

[Qiu]

@Feras95p 
Is this what you want. if yes, please mark it as solution. Thanks.

[Feras95p]

Hi @Qiu ,

Thanks for helping ! Unfortunately I couldn't be able to open the workflow it give me this error.

 

[BrandonB]

This should do it. Workflow is attached. I have saved this as a previous workflow version so it should open. 

 

[BrandonB]

It looks like @Qiu beat me to the solution! I think our answers are pretty much the same so please mark theirs as a solution if it answers your question. 

[Qiu]

@Feras95p 
Thanks for your feedback. You can actually go ahead and import the workflow and it will work anyway.
or you can try the one from @BrandonB they are the same.
Please mark our posts as the solution if you would. Thanks.

[Feras95p]

Hi @BrandonB ,

Thanks for helping ! But there is something wrong with your workflow because it give the output  8 records which are 8 codes but in the input I have 378 codes!

[Feras95p]

As I replied to @BrandonB  there is something wrong with the work flow as it gives me only 8 records while I have in the input 378 codes so each code should be shown how many times is used before and after 20-6-2020 for that company.

[Feras95p]

I want to have all the codes  of the output in one sheet how it possible? Like the output of the left and the right  in one sheet. Is that possible ?

[Feras95p]

@BrandonB  In the workflow I couldn't have all the output in one sheet, I'm looking to have the output of the left and right of the join tool like the output in the J so all the recodes be like the formatting of the J. In summary I want each code to be identified like the output of Join output.