Combining Two Excel Files

[ebledsoe22]

Hello,

I have attached two input files and a desired output file. I removed the descriptions as to not share any proprietary information. I only want the "CSHSWPX" columns from the Input 2 file, but I need them to be aligned with the appropriate rows from the Input 1 file without creating duplicates. I tried using the union tool, which seemed to help, but instead of having values for each respective row for each column, a new row was being created. See error received attachment for an example of the issue I'm having. You can see that the year, unit, account, affiliate, and sum total amt rows were duplicated for the different CSHSWPX columns, instead of showing the amount for CSHSWP1 and CSHSWP2B on the same row. How can I eliminate this issue? I tried the join tool, but I needed more than 1 matching criteria. I tried the append and find and replace tools, but they didn't yield the desired outcome either. I thought the funny match tool would work, but I am unsure how to set it up to yield the desired outcome if possible. 

Please let me know if more information is needed.

Thank you in advance for your help.

Kindly,

Erin

[Prometheus · Accepted solution]

@ebledsoe22 You can start out by summarizing Input 1 so you don't end up creating a one to many join at the Join tool, which comes next. A one to many join will create those extra rows that you don't want. In the Join tool, I joined on Unit, Account, Affiliate, and Year. After the Join tool drop in a Union tool and connect the J output of the Join tool then connect the L output of the Join tool to create a left join or left outer join (different people call it different things). After that, I used a Multi-Field Formula tool to replace all nulls in numeric fields with 0 so when I use the final Summarize tool, it'll add all the numbers together and nothing weird will happen. 

 

 

 

 

[ebledsoe22]

Thank you for your explanation.

[ebledsoe22]

I have a follow-up scenario. I have two inputs that I am combining using the Union tool. However, now I have duplicate rows because there are several column outputs that correspond to one row. Instead of putting the amounts on the same row, the rows are duplicated for each column. I used the unique tool to remove the duplicates, but now I have 6 transactions that are deemed duplicates in some columns, but not others. There should be a total of 1737 integrated rows as there should be one instance of columns A-F. The remaining columns are output columns.  How do I fix this? See attachments.

[Prometheus · Accepted solution]

@ebledsoe22 You can union the inputs then use a Summarize tool after it. Since columns A-F are good and the semi-duplicates happen in your totals after that, you can group by A-F then sum the rest. It will bring the values for each A-F group into one record. 

 

[ebledsoe22]

This worked. Thank you. I tweaked it a little by grouping by the CSHSWP column and got the results I needed without any duplicates.