IF statement- AND & OR Function

pto160

Active Member
Joined
Feb 1, 2009
Messages
365
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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
ABCDE
1TypeOrderAmountCol A & B
2Sale314200100Sale-314200Sale/Credit
3Sale312800200Sale-312800No Credit
4Credit314200300Credit-314200No Credit
5Sale312900400Sale-312900No Credit
Sheet1
Cell Formulas
RangeFormula
D2:D5D2=A2&"-"&B2
E2E2=IFNA(IF(MATCH(IF(A2="Sale","Credit-"&B2,"Sale-"&B2),$D$2:$D$5,0)>0,"Sale/Credit"),"No Credit")
E3:E5E3=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
ABCDE
1TypeOrderAmountCol A & BResult I Want
2Sale312800200Sale-312800Sale
3Credit312900500Credit-312900Sale/Credit
4Sale312900400Sale-312900Sale/Credit
5Auth314200100Auth-314200Auth/Sale/Credit
6Sale314200100Sale-314200Auth/Sale/Credit
7Credit314200300Credit-314200Auth/Sale/Credit
8Sale315100100Sale-315100Sale/Credit
9Credit315100200Credit-315100Sale/Credit
10Auth318100200Auth-318100Auth Only
11Auth320100500Auth-320100Auth/Sale
12Sale320100500Sale-320100Auth/Sale
Sheet1 (2)
Cell Formulas
RangeFormula
D2:D12D2=A2&"-"&B2
 

Some videos you may like

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
4,161
Office Version
  1. 365
Platform
  1. MacOS
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
ABCDEF
1TypeOrderAmountCol A & BResult I WantFormula
2Sale312800200Sale-312800SaleSale Only
3Credit312900500Credit-312900Sale/CreditSale/Credit
4Sale312900400Sale-312900Sale/CreditSale/Credit
5Auth314200100Auth-314200Auth/Sale/CreditAuth/Sale/Credit
6Sale314200100Sale-314200Auth/Sale/CreditAuth/Sale/Credit
7Credit314200300Credit-314200Auth/Sale/CreditAuth/Sale/Credit
8Sale315100100Sale-315100Sale/CreditSale/Credit
9Credit315100200Credit-315100Sale/CreditSale/Credit
10Auth318100200Auth-318100Auth OnlyAuth Only
11Auth320100500Auth-320100Auth/SaleSale/Auth
12Sale320100500Sale-320100Auth/SaleSale/Auth
Sheet1
Cell Formulas
RangeFormula
D2:D12D2=A2&"-"&B2
F2:F12F2=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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,502
Office Version
  1. 365
Platform
  1. Windows

pto160

Active Member
Joined
Feb 1, 2009
Messages
365
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Great. Thank you so much. That filter function works great :) (y) . 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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,502
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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.
🤕o_O🤕
 

pto160

Active Member
Joined
Feb 1, 2009
Messages
365
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,502
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
ABCDEFGH
1TypeOrderAmountResult 1Result 2Result 3Sort Order
2Sale312800200SaleSaleSaleAuth
3Credit312900500Sale/CreditSale/CreditSale/CreditSale
4Sale312900400Sale/CreditSale/CreditSale/CreditCredit
5Auth314200100Auth/Sale/CreditAuth/Sale/CreditAuth/Sale/Credit
6Credit314200300Auth/Sale/CreditAuth/Sale/CreditAuth/Sale/Credit
7Sale314200100Auth/Sale/CreditAuth/Sale/CreditAuth/Sale/Credit
8Credit314200300Auth/Sale/CreditAuth/Sale/CreditAuth/Sale/Credit
9Sale315100100Sale/CreditSale/CreditSale/Credit
10Credit315100200Sale/CreditSale/CreditSale/Credit
11Auth318100200AuthAuthAuth
12Auth320100500Auth/SaleAuth/SaleAuth/Sale
13Sale320100500Auth/SaleAuth/SaleAuth/Sale
Sheet2
Cell Formulas
RangeFormula
D2:D13D2=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:E13E2=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:F13F2=TEXTJOIN("/",1,LET(UL,UNIQUE(FILTER(A$2:A$13,B$2:B$13=B2)),SORTBY(UL,MATCH(UL,{"Auth","Sale","Credit"},0))))
 

pto160

Active Member
Joined
Feb 1, 2009
Messages
365
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Fantastic. Thanks so much. The formula works great.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,974
Messages
5,575,308
Members
412,655
Latest member
habercio
Top