Expression output based on duplicates

[hermansoa]

Using excel, I am automating the creation of an expression based on values from a table. My table can have duplicates and I want to find the duplicates, use only the first instance of the duplicate, then use that to search and return multiple values from another column that match. This will be used to create an output string. Unfortunately, I have no idea how to accomplish my scenarios below. I have tried indexes, lookups, matches, filters, choosecols, etc, but not sure how to fit it all together to achieve my end result. Unfortunately I can’t use Macro’s because this sheet will be given to people whose security policy won’t let macro enabled spreadsheets to be used. Any help is greatly appreciated.

EXAMPLE:

Row Number	DI Transaction Type	Where description Contains	Where description does not contain	Transaction Code
1​	ATM_WITHDRAWAL		Withdrawal at ATM BP	227​
2​	POS_PURCHASE	DBT CRD		229​
3​	POS_PURCHASE	B/P		229​
4​	ELECTRONIC_TRANSFER_CREDIT	Zelle		56​
5​	ELECTRONIC_TRANSFER_DEBIT	Zelle		56​
6​	POS_PURCHASE	POS DEB		228​
7​	POS_PURCHASE	IBT DEB		228​

Scenarios:

• Column B and E match, then I want to return the values in Column C (multiple).
• Column E and C match, then I want to return the values in Column B (multiple).

I will reference the separators “&,!,||,? And others from cells in the spreadsheet

Output would look like this in a String:

Row Number	Output String
1	ATM_WITHDRAWAL && !withdrawal at ATM BP?227
2	POS_PURHCASE && DBT CRD||B/P?229
4	ELECTRONIC_TRANSFER_CREDIT || ELECTRONIC_TRANSFER_DEBIT && Zelle?56
6	POS_PURCHASE && POS DEB||IBT DEB?228

TranCodeExpressionCreation.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Row Number	DI Transaction Type	Where description Contains	Where description does not contain	Transaction Code
2	1	ATM_WITHDRAWAL		Withdrawal at ATM BP	227
3	2	POS_PURCHASE	DBT CRD		229
4	3	POS_PURCHASE	B/P		229
5	4	ELECTRONIC_TRANSFER_CREDIT	Zelle		56
6	5	ELECTRONIC_TRANSFER_DEBIT	Zelle		56
7	6	POS_PURCHASE	POS DEB		228
8	7	POS_PURCHASE	IBT DEB		228
9
10	#transactions.get(0)['transactionType']=='ATM_WITHDRAWAL' && !#transactions.get(0)['displayDescription'].contains('Withdrawal at ATM BP')?'227':(#transactions.get(0)['transactionType']=='POS_PURCHASE' && (#transactions.get(0)['displayDescription'].contains('DBT CRD')||#transactions.get(0)['displayDescription'].contains('B/P'))?'229':((#transactions.get(0)['transactionType']=='ELECTRONIC_TRANSFER_CREDIT' || #transactions.get(0)['transactionType']=='ELECTRONIC_TRANSFER_DEBIT') && #transactions.get(0)['displayDescription'].contains('ZELLE')?'56':(#transactions.get(0)['transactionType']=='POS_PURCHASE' && (#transactions.get(0)['displayDescription'].contains('POS DEB')||#transactions.get(0)['displayDescription'].contains('IBT DEB'))?'228':(''))))
11
12	Separators				Contains	Does not contain
13		#transactions.get(0)['transactionType']=='	'	&&	#transactions.get(0)['displayDescription'].contains('	!#transactions.get(0)['displayDescription'].contains('	')	?	'	:(	||	''	)
14
15
16	Output Example:
17	1	#transactions.get(0)['transactionType']=='ATM_WITHDRAWAL'&&!#transactions.get(0)['displayDescription'].contains('Withdrawal at ATM BP')?'227'
18	2	#transactions.get(0)['transactionType']=='POS_PURCHASE'&&#transactions.get(0)['displayDescription'].contains('DBT CRD')||#transactions.get(0)['displayDescription'].contains('B/P')?'229'
19	4	#transactions.get(0)['transactionType']=='ELECTRONIC_TRANSFER_CREDIT'||#transactions.get(0)['transactionType']=='ELECTRONIC_TRANSFER_DEBIT'&&#transactions.get(0)['displayDescription'].contains('Zelle')?'56'
20	6	#transactions.get(0)['transactionType']=='POS_PURCHASE'&&#transactions.get(0)['displayDescription'].contains('POS DEB')||#transactions.get(0)['displayDescription'].contains('IBT DEB')?'228'
Sheet1

Cell Formulas
Range	Formula
B17	B17	=B13&B2&C13&D13&F13&D2&G13&H13&I13&E2&I13
B18	B18	=B13&B3&C13&D13&E13&C3&G13&K13&E13&C4&G13&H13&I13&E3&I13
B19	B19	=B13&B5&C13&K13&B13&B6&C13&D13&E13&C5&G13&H13&I13&E5&I13
B20	B20	=B13&B7&C13&D13&E13&C7&G13&K13&E13&C8&G13&H13&I13&E7&I13

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B6:B8	Cell Value	=""	text	NO
B3:B6	Cell Value	=""	text	NO
B2,B7:B8	Cell Value	=""	text	NO