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 &"/".
How would you do this? The results desired are in column H. I imagine some sort of IF statement with &"/".
Unique List_Frequency.xlsx | ||||||||||
---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | |||
1 | Type | Order | Amount | Results | Results | Results | Results I want | |||
2 | Sale | 312800 | 200 | Sale | Sale | |||||
3 | Credit | 312900 | 500 | Credit | Sale | Sale/Credit | ||||
4 | Sale | 312900 | 400 | Credit | Sale | Sale/Credit | ||||
5 | Auth | 314200 | 100 | Auth | Sale | Credit | Auth/Sale/Credit | |||
6 | Sale | 314200 | 100 | Auth | Sale | Credit | Auth/Sale/Credit | |||
7 | Credit | 314200 | 300 | Auth | Sale | Credit | Auth/Sale/Credit | |||
8 | Credit | 314200 | 50 | Auth | Sale | Credit | Auth/Sale/Credit | |||
9 | Sale | 315100 | 100 | Sale | Credit | Sale/Credit | ||||
10 | Credit | 315100 | 200 | Sale | Credit | Sale/Credit | ||||
11 | Auth | 318100 | 200 | Auth | Auth | |||||
12 | Auth | 320100 | 500 | Auth | Sale | Auth/Sale | ||||
13 | Sale | 320100 | 500 | Auth | Sale | Auth/Sale | ||||
Sheet1 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
E2:G13 | E2 | =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)),"") |