Optimization doing a "vlookup + calculation" function

[Isaac_Juarez]

Dear Alteryx enthusiasts;

I am facing a problem that is beyond my knowledge, I want to ask for your help. 

I am using the Alteryx version 2020.1.5.25447  and the background is as follows:

1. I am processing 6 input files which are going to give 1 final output file.

2. Every file has 843 data in the file, that I need to combine to get the half of the final output file. example: the first output file has the combined data of Part 1-Cav1, Part2-Cav1 and Part3-Cav1; the other half is Part1-Cav2, Part2-Cav2 and Part3-Cav2.

3.  The 1st output file is going to combine and relate by Item1 the Part1,2,3 -Cav1. this file has 835 records.

4. Average Calculation. Out of these 835, I need only 72 records, but I need to do 2 main calculations in some of the records; example the Item 24 with Nominal dimension 2 in the excel file  has 37 records in the output file, for this Item I need to calculate the average value of the column 1-Cav1, 2-Cav1, 3-Cav1.

5. Max-Min Calculation.  Item 29  with Nominal dimension of 28.6 has 37 records; in this case I need to search the minimum value and maximum for each part in cavity 1. 

6. Rest of records.  based on Item and nominal dimension; do a kind of "vlookup" with no calculation.

please find in the attachments.

1. Excel file with the report I am trying to create out of Alteryx.

2. the current workflow I created  to get the 1st half of the report. 

[T_Willins]

Hi @Isaac_Juarez,

We may be able to help, but it will be difficult without sample data.  Are you able to provide sample data for the workflow you provided?

[Isaac_Juarez]

Here are 5 of 6

[Isaac_Juarez]

6 of 6