IF statement for return multiple values

pto160

Active Member
Joined
Feb 1, 2009
Messages
365
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
I have a spreadsheet that returns multiple results based on an order number in columns E to G. I would like to combine those results into a single cell with "/" as the delimiter into column H and have the sort order Auth/Sale/Credit. Some of the cells have only one result and the other cells could have 2 or more results based on the order number.
How would you do this? The results desired are in column H. I imagine some sort of IF statement with &"/".

Unique List_Frequency.xlsx
ABCDEFGH
1TypeOrderAmountResultsResultsResultsResults I want
2Sale312800200Sale  Sale
3Credit312900500CreditSale Sale/Credit
4Sale312900400CreditSale Sale/Credit
5Auth314200100AuthSaleCreditAuth/Sale/Credit
6Sale314200100AuthSaleCreditAuth/Sale/Credit
7Credit314200300AuthSaleCreditAuth/Sale/Credit
8Credit31420050AuthSaleCreditAuth/Sale/Credit
9Sale315100100SaleCredit Sale/Credit
10Credit315100200SaleCredit Sale/Credit
11Auth318100200Auth  Auth
12Auth320100500AuthSale Auth/Sale
13Sale320100500AuthSale Auth/Sale
Sheet1
Cell Formulas
RangeFormula
E2:G13E2=IFERROR(INDEX($A$2:$A$13,AGGREGATE(15,6,(ROW($A$2:$A$13)-ROW($A$2)+1)/($B$2:$B$13=$B2)/(COUNTIF($D2:D2,$A$2:$A$13)=0),1)),"")
 

Some videos you may like

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

mrshl9898

Well-known Member
Joined
Feb 6, 2012
Messages
1,651
=IF(E2="","",E2)&IF(COUNTA(E2:G2)>1,"/","")&IF(F2="","",F2)&IF(COUNTA(E2:G2)>2,"/","")&IF(G2="","",G2)
 
Solution

pto160

Active Member
Joined
Feb 1, 2009
Messages
365
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Thanks. Using the Sort<Options<Left to Right only allows you to sort each individual row, which would take forever if you had a 1,000 rows. I was thinking to sort the columns using custom lists so the order would be Auth, Sale, Credit. Row 3 and 4 shows Credit/Sale and Row 9 and 10 shows Sale/Credit.

Is there a way to list Credit/Sale as Sale/Credit?

Cell Formulas
RangeFormula
H2:H13H2=IF(E2="","",E2)&IF(COUNTA(E2:G2)>1,"/","")&IF(F2="","",F2)&IF(COUNTA(E2:G2)>2,"/","")&IF(G2="","",G2)



Is there a way to get rid of the "//" at the end if there is only one entry in the range or one "/" at the end if there is two entries?
 

pto160

Active Member
Joined
Feb 1, 2009
Messages
365
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
I set up a lookup table and used your formula as a lookup value to get the results I was looking for.
Thanks for your help.
 

mrshl9898

Well-known Member
Joined
Feb 6, 2012
Messages
1,651
Apologies for missing your replies, have been away from my PC.

Another solution to keep the Auth Sale Credit order:

=IFERROR(IF(AND(OR(IFERROR(MATCH("Sale",E2:G2,0)>0,FALSE),IFERROR(MATCH("Audit",E2:G2,0)>0,FALSE)),IFERROR(MATCH("Auth",E2:G2,0)>0,FALSE)),"Auth/",IF(MATCH("Auth",E2:G2,0)>0,"Auth","")),"")&IFERROR(IF(AND(IFERROR(MATCH("Sale",E2:G2,0)>0,FALSE),IFERROR(MATCH("Credit",E2:G2,0)>0,FALSE)),"Sale/",IF(MATCH("Sale",E2:G2,0)>0,"Sale","")),"")&IFERROR(IF(IFERROR(MATCH("Credit",E2:G2,0)>0,FALSE),"Credit",""),"")
 

pto160

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

Watch MrExcel Video

Forum statistics

Threads
1,123,380
Messages
5,601,282
Members
414,440
Latest member
Kim0204

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top