Apologies if this has already been answered in another post, but I wasn't sure how to word this in my search...
I'm trying to combine two data sets so that I can create a formula to override another column based on the joined files. I don't want to create a lot of If statements to override another column based on several variables.
I have two data sets from excel files I'm trying to join:
Data Set 1 | ||||
Company | Branch | Acct # | Acct#2 | Amount |
Company A | xyz | 1234 | 567 | 5.99 |
Company B | abc | 5678 | 902 | 6.99 |
Company C | def | 9012 | 152 | 7.99 |
Data Set 2 | ||
Company | Acct # | Acct2 |
Company A | 1234 | 123 |
Company A | 5678 | 456 |
Company A | 9012 | 789 |
Company B | 9012 | 682 |
Company B | 1234 | 789 |
Company B | 5678 | 456 |
Company C | 1234 | 234 |
Company C | 5678 | 789 |
Company C | 9012 | 123 |
Output desired | |||||
Company | Branch | Acct # | Acct#2 | Amount | Acct2 |
Company A | xyz | 1234 | 567 | 5.99 | 123 |
Company B | abc | 5678 | 902 | 6.99 | 456 |
Company C | def | 9012 | 152 | 7.99 | 123 |
The formula would then overwrite Acct#2 with Acct2 for an output of
Output desired after formula | ||||
Company | Branch | Acct # | Acct#2 | Amount |
Company A | xyz | 1234 | 123 | 5.99 |
Company B | abc | 5678 | 456 | 6.99 |
Company C | def | 9012 | 123 | 7.99 |
Further down stream these will be sumed on the amount field based on Company, Branch, and Acct #2
Final output downstream | |||
Company | Branch | Acct#2 | Amount |
Company A | xyz | 123 | 5.99 |
Company B | abc | 456 | 6.99 |
Company C | def | 123 | 7.99 |
Solved! Go to Solution.
that this tends to override Acct #1 from the left input. I want that to remain untouched.
@sselby6909 @If you want to include the Acct# then include that field also in the group by of summarize tool
Thanks everyone. I think I figured out the issue. I had duplicates and it wasn't sure which one to use. I've cleaned up the duplicate data. However, now i'm having an issue with writing an if statement.
I'm trying to get something like this
If Opco = A and Function = XYZ and Code = Null, and descr = A or B or C then 1234
I wrote as follows: IF[Ocpo] = "A" AND [FUNCTION]="XYZ" AND [CODE] =null() AND [Descr] = "A" OR "B" OR"C" Then 1234, but it's not recognizing the OR in the description = syntax.
@sselby6909 use [Desc]='A' or [Desc]='B' or [Desc]='C' (OR) [Desc] in ('A','B','C') to use OR function