Joining data sets with multiple criteria

[sselby6909]

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

[Clifford_Coon]

Hi @sselby6909 ,

You can do it all using the "select" features of the join tool.

Happy Solving ;-)

 

[binuacs]

@sselby6909 another option

[sselby6909]

 that this tends to override Acct #1 from the left input. I want that to remain untouched. 

[binuacs · Accepted solution]

@sselby6909 @If you want to include the Acct# then include that field also in the group by of summarize tool

[nagakavyasri]

@sselby6909 

[sselby6909]

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. 

[nagakavyasri · Accepted solution]

@sselby6909 use  [Desc]='A' or [Desc]='B' or [Desc]='C' (OR)  [Desc] in ('A','B','C') to use OR function