IF statement- AND & OR Function

[pto160]

I download a receipt report showing receipts with order numbers. Column A has type Sale and Credit. I wrote a IF statement in column E where it tells me that there is a sale that had a credit with the same order number, and if it did not it would say "No Credit". I am combining column A and B because there could be 50K rows in the report. Here it is.

Book1
	A	B	C	D	E
1	Type	Order	Amount	Col A & B
2	Sale	314200	100	Sale-314200	Sale/Credit
3	Sale	312800	200	Sale-312800	No Credit
4	Credit	314200	300	Credit-314200	No Credit
5	Sale	312900	400	Sale-312900	No Credit
Sheet1

Cell Formulas
Range	Formula
D2:D5	D2	=A2&"-"&B2
E2	E2	=IFNA(IF(MATCH(IF(A2="Sale","Credit-"&B2,"Sale-"&B2),$D$2:$D$5,0)>0,"Sale/Credit"),"No Credit")
E3:E5	E3	=IFNA(IF(MATCH(IF(A3="Sale","Credit-"&B3,"Sale-"&B3),D3:D6,0)>0,"Sale/Credit"),"No Credit")

Is this the most efficient formula?

Now the new problem I am having is I am adding "Auth" to the Type column. The IF statement choices are Auth/Sales/Credit if the order numbers have all three types for the same order number. Sale/Credit if if they have Sale/Credit only. The next one is Sale only for the same order number and then Auth only if it doesn't have any credit or sale, and then Auth/Sale. Here is the table. I sorted it to make it easier for someone to help me. My table won't be sorted.

Book1
	A	B	C	D	E
1	Type	Order	Amount	Col A & B	Result I Want
2	Sale	312800	200	Sale-312800	Sale
3	Credit	312900	500	Credit-312900	Sale/Credit
4	Sale	312900	400	Sale-312900	Sale/Credit
5	Auth	314200	100	Auth-314200	Auth/Sale/Credit
6	Sale	314200	100	Sale-314200	Auth/Sale/Credit
7	Credit	314200	300	Credit-314200	Auth/Sale/Credit
8	Sale	315100	100	Sale-315100	Sale/Credit
9	Credit	315100	200	Credit-315100	Sale/Credit
10	Auth	318100	200	Auth-318100	Auth Only
11	Auth	320100	500	Auth-320100	Auth/Sale
12	Sale	320100	500	Sale-320100	Auth/Sale
Sheet1 (2)

Cell Formulas
Range	Formula
D2:D12	D2	=A2&"-"&B2

 

[etaf]

can you have
the following combinations

A countif may work
Auth/Sale/Credit
Sale/Credit
Auth/Sale
Auth/Credit
Sale Only
Auth Only
Credit Only
I assume some are impossible
=IF(COUNTIFS(A:A,"sale",B:B,B2)+COUNTIFS(A:A,"Auth",B:B,B2)+COUNTIFS(A:A,"Credit",B:B,B2)=3,"Auth/Sale/Credit",IF(COUNTIFS(A:A,"sale",B:B,B2)+COUNTIFS(A:A,"Credit",B:B,B2)=2,"Sale/Credit",IF(COUNTIFS(A:A,"sale",B:B,B2)+COUNTIFS(A:A,"Auth",B:B,B2)=2, "Sale/Auth",IF(COUNTIFS(A:A,"Auth",B:B,B2)+COUNTIFS(A:A,"Credit",B:B,B2)=2, "Auth/Credit",IF(COUNTIFS(A:A,"Auth",B:B,B2)=1,"Auth Only",IF(COUNTIFS(A:A,"Credit",B:B,B2)=1,"Credit Only",IF(COUNTIFS(A:A,"sale",B:B,B2)=1,"Sale Only","")))))))

I have only used column A & B and not combined with a combined countif
COUNTIFS(A:A,"sale",B:B,B2)+COUNTIFS(A:A,"Auth",B:B,B2)+COUNTIFS(A:A,"Credit",B:B,B2)=3,"Auth/Sale/Credit",
COUNTIFS(A:A,"sale",B:B,B2)+COUNTIFS(A:A,"Credit",B:B,B2)=2,"Sale/Credit",
COUNTIFS(A:A,"sale",B:B,B2)+COUNTIFS(A:A,"Auth",B:B,B2)=2, "Sale/Auth",
COUNTIFS(A:A,"Auth",B:B,B2)+COUNTIFS(A:A,"Credit",B:B,B2)=2, "Auth/Credit"
COUNTIFS(A:A,"Auth",B:B,B2)=1,"Auth Only"
COUNTIFS(A:A,"Credit",B:B,B2)=1,"Credit Only"
COUNTIFS(A:A,"sale",B:B,B2)=1,"Sale Only"

Book2
	A	B	C	D	E	F
1	Type	Order	Amount	Col A & B	Result I Want	Formula
2	Sale	312800	200	Sale-312800	Sale	Sale Only
3	Credit	312900	500	Credit-312900	Sale/Credit	Sale/Credit
4	Sale	312900	400	Sale-312900	Sale/Credit	Sale/Credit
5	Auth	314200	100	Auth-314200	Auth/Sale/Credit	Auth/Sale/Credit
6	Sale	314200	100	Sale-314200	Auth/Sale/Credit	Auth/Sale/Credit
7	Credit	314200	300	Credit-314200	Auth/Sale/Credit	Auth/Sale/Credit
8	Sale	315100	100	Sale-315100	Sale/Credit	Sale/Credit
9	Credit	315100	200	Credit-315100	Sale/Credit	Sale/Credit
10	Auth	318100	200	Auth-318100	Auth Only	Auth Only
11	Auth	320100	500	Auth-320100	Auth/Sale	Sale/Auth
12	Sale	320100	500	Sale-320100	Auth/Sale	Sale/Auth
Sheet1

Cell Formulas
Range	Formula
D2:D12	D2	=A2&"-"&B2
F2:F12	F2	=IF(COUNTIFS(A:A,"sale",B:B,B2)+COUNTIFS(A:A,"Auth",B:B,B2)+COUNTIFS(A:A,"Credit",B:B,B2)=3,"Auth/Sale/Credit",IF(COUNTIFS(A:A,"sale",B:B,B2)+COUNTIFS(A:A,"Credit",B:B,B2)=2,"Sale/Credit",IF(COUNTIFS(A:A,"sale",B:B,B2)+COUNTIFS(A:A,"Auth",B:B,B2)=2, "Sale/Auth",IF(COUNTIFS(A:A,"Auth",B:B,B2)+COUNTIFS(A:A,"Credit",B:B,B2)=2, "Auth/Credit",IF(COUNTIFS(A:A,"Auth",B:B,B2)=1,"Auth Only",IF(COUNTIFS(A:A,"Credit",B:B,B2)=1,"Credit Only",IF(COUNTIFS(A:A,"sale",B:B,B2)=1,"Sale Only","")))))))

 

[Peter_SSs]

Would this suffice (if you have the FILTER function)?

pto160.xlsm
	A	B	C	D
1	Type	Order	Amount	Result
2	Sale	312800	200	Sale
3	Credit	312900	500	Credit/Sale
4	Sale	312900	400	Credit/Sale
5	Auth	314200	100	Auth/Sale/Credit
6	Sale	314200	100	Auth/Sale/Credit
7	Credit	314200	300	Auth/Sale/Credit
8	Sale	315100	100	Sale/Credit
9	Credit	315100	200	Sale/Credit
10	Auth	318100	200	Auth
11	Auth	320100	500	Auth/Sale
12	Sale	320100	500	Auth/Sale
Sheet1

Cell Formulas
Range	Formula
D2:D12	D2	=TEXTJOIN("/",1,FILTER(A$2:A$12,B$2:B$12=B2))

 

[pto160]

Great. Thank you so much. That filter function works great . It probably is one of the best Excel functions out there. I wrapped it in an unique function because sometimes there would be two credits that would equal the sale amount, and it worked. I suppose in Excel 2016, the function would be very long.

 

[Peter_SSs]

Great. Thank you so much.

You're welcome. Thanks for the follow-up.

I wrapped it in an unique function because sometimes there would be two credits that would equal the sale amount

I did wonder about that possibility so, yes, good idea.
Another possibility might be to include the SORT() function as well so that any combination of Types always come in the same order so you wouldn't end up with, for example
Auth/Sale/Credit
Sale/Auth/Credit
Credit/Auth/Sale
etc in various rows

I suppose in Excel 2016, the function would be very long.

??

 

[pto160]

That was exactly my next question. The order should be Auth/Sale/Credit. Sale would come after Auth and credit would come after Sale.
Is that possible?

Maybe add a helper column with 1=Auth, 2=Sale and 3=Credit. Then use the sortby function I imagine.

 

[Peter_SSs]

Is that possible?

Yes, that is possible & you can use the helper column or you can build the list into the formula - especially if the list is small like in this case.

IF you have the LET function you can use column D formula with the helper column or column F without the helper.
If you do not yet have the LET function then you can use the column E formula with helper column or a similar modification to above to use without helper.

pto160.xlsm
	A	B	C	D	E	F	G	H
1	Type	Order	Amount	Result 1	Result 2	Result 3		Sort Order
2	Sale	312800	200	Sale	Sale	Sale		Auth
3	Credit	312900	500	Sale/Credit	Sale/Credit	Sale/Credit		Sale
4	Sale	312900	400	Sale/Credit	Sale/Credit	Sale/Credit		Credit
5	Auth	314200	100	Auth/Sale/Credit	Auth/Sale/Credit	Auth/Sale/Credit
6	Credit	314200	300	Auth/Sale/Credit	Auth/Sale/Credit	Auth/Sale/Credit
7	Sale	314200	100	Auth/Sale/Credit	Auth/Sale/Credit	Auth/Sale/Credit
8	Credit	314200	300	Auth/Sale/Credit	Auth/Sale/Credit	Auth/Sale/Credit
9	Sale	315100	100	Sale/Credit	Sale/Credit	Sale/Credit
10	Credit	315100	200	Sale/Credit	Sale/Credit	Sale/Credit
11	Auth	318100	200	Auth	Auth	Auth
12	Auth	320100	500	Auth/Sale	Auth/Sale	Auth/Sale
13	Sale	320100	500	Auth/Sale	Auth/Sale	Auth/Sale
Sheet2

Cell Formulas
Range	Formula
D2:D13	D2	=TEXTJOIN("/",1,LET(UL,UNIQUE(FILTER(A$2:A$13,B$2:B$13=B2)),SORTBY(UL,MATCH(UL,H$2:H$4,0))))
E2:E13	E2	=TEXTJOIN("/",1,SORTBY(UNIQUE(FILTER(A$2:A$13,B$2:B$13=B2)),MATCH(UNIQUE(FILTER(A$2:A$13,B$2:B$13=B2)),H$2:H$4,0)))
F2:F13	F2	=TEXTJOIN("/",1,LET(UL,UNIQUE(FILTER(A$2:A$13,B$2:B$13=B2)),SORTBY(UL,MATCH(UL,{"Auth","Sale","Credit"},0))))

 

[pto160]

Fantastic. Thanks so much. The formula works great.

 

[Peter_SSs]

Cheers.