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 |
