looking for duplicate rows with same values in certain columns in multiple excels

[kimdelgadillo]

I have one global file with a lot of lines containing inputs to be processed by a team. Every line is & correction.

To check the processing I receive many project files (1 by project) that should contain a line with the input correction.

So I expect the individual project files to contain the input corrected line in the global file, among many other lines.

I would like to see if the line in the global file exists in the project file with following fields from the global file being identical: TS PERIOD / FMNO / PROJECT / HOURS /PER DIEM

with the following fields from the individual project files:

Project number/Fmno/Hours/WIP per diem/Time-sheet period

More specifically, I want to see the lines that exist in global file but do not appear with the fields above with identical values. This basically means it was not processed correctly.

On top of this, when TS PERIOD, FMNO, PROJECT and PER DIEM are the same in the global file, I would expect the sum of hours to be found back in the individual project file. If hours are 10 and -5 in the global file I would expect hours 5 in the project file. To see that this was not processed correctly, I would need to see that the line in the project file containing 5 not exists so that i know it has not been processe correctly.

I gave it a try but sth went wrong along the way.

Basically I put everything together in 1 file and then added columns with filenames if the values are the same.

Not the best way probably, but if it would work...

Happy to hear any suggestions.

example input files attached (1 global file and 2 project files)

[ponraj]

Here is the solution. 

[kimdelgadillo]

Thank you!

It seems to work

the only problem is that i have around 60 project files so i need to upload them at once, and not one by one.

Pointing at the file and filling *.xlsx loads all the files in a directory but how would this alter the alteryx flow?

Pls note i have version 11.0 but i managed to downgrade the file from 11.7 to 11.0

thanks!

[ponraj]

Uploading the solution with the option to read multiple project files. Below are summary of changes made. 

1. Place project files and global file in two different folder/directory.  

2. Use Input data tool for reading the project files instead of directory tool and configure it with below options. 

Connect a File or Database: <filepath>\*.xlsx

Table or Query: 'Time Details$'

Search SubDirs: To be checked

Output file name as field: File name Only. 

3. Configure two output tool to segregate correctly processed and incorrectly processed from project files. 

[kimdelgadillo]

thank you for the effort and the flow.

Unfortunately it is not providing the solution I need.

I have the impression this scans the project file and checks line by line if this appears in the adjustment file (output tab).

In fact I need to the check in the other direction: going line by line in the output tab and checking if this line exists in the project file. The project file will by default contain many many more lines but that does not matter. The only thing I want to see if the line in the output file exists in the project file.

How this is represented is the same for me, but essentially it is a ok or nok for every line in the output file.

Dont know if this is clear, but would be very happy to have a solution for this one.

regards

Kim

Maybe my requirements are not clear.

[ponraj]

Workflow I am uploading now will split global file into two, namely correctly processed lines and incorrectly processed lines after comparing the global file lines with lines in the project files. 

Hope this meets your requirement. 

[kimdelgadillo]

hello

thank you very much. this is what i need.

the last extra requirement is to sum the hours in the global file if FMNO, timesheet period, project number and per diem is the same.

meaning: in the global file having two lines

TS period 1803     FMNO 123456 per diem 450    hours 5

TS period 1803     FMNO 123456 per diem 450    hours 15

I would expect correct processing in the project file that hours are summed

TS period 1803     FMNO 123456 per diem 450    hours 20

If I have this, that would be perfect. But the current solution is already a very big step forward.

thanks again!

kim

[ponraj]

uploading the workflow which will give you output hours summed...

I am giving two output option. 

Output option 1: output file will have column called "correct/incorrect" which will tell whether a line is correctly processed or not. 

Output option 2: output file will have two sheets, namely correct and incorrect 

Let me know in case you require any further in this regard...

[kimdelgadillo · Accepted solution]

hello

again thanks for the work done, it worked for me as i did some tests. I only change the initial check on null value to the project code field.

I will now send it to other person for testing. Should have an anwser tomorrow or wednesday so that i can accept the solution

again many thanks!!!

[ponraj]

Can you mark the workflow which helped you to resolve your problem as accepted solution ?