I have a formula returning multiple values, but there is duplicate values in the index list. Can I return an unique list for columns E to H? I am getting the duplicate "Credit" in row 5 to 8.
I am using Excel 2016 at work. Here is what I have.
Here are the desired results:
I am using Excel 2016 at work. Here is what I have.
Book1 | ||||||||||
---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | |||
1 | Type | Order | Amount | Results | Results | Results | Results | |||
2 | Sale | 312800 | 200 | Sale | ||||||
3 | Credit | 312900 | 500 | Credit | Sale | |||||
4 | Sale | 312900 | 400 | Credit | Sale | |||||
5 | Auth | 314200 | 100 | Auth | Sale | Credit | Credit | |||
6 | Sale | 314200 | 100 | Auth | Sale | Credit | Credit | |||
7 | Credit | 314200 | 300 | Auth | Sale | Credit | Credit | |||
8 | Credit | 314200 | 50 | Auth | Sale | Credit | Credit | |||
9 | Sale | 315100 | 100 | Sale | Credit | |||||
10 | Credit | 315100 | 200 | Sale | Credit | |||||
11 | Auth | 318100 | 200 | Auth | ||||||
12 | Auth | 320100 | 500 | Auth | Sale | |||||
13 | Sale | 320100 | 500 | Auth | Sale | |||||
Sheet1 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
E2:H13 | E2 | =IFERROR(INDEX($A$2:$A$13,SMALL(IF($B$2:$B$13=$B2,ROW($B$2:$B$13)-ROW($B$2)+1),COLUMNS($E2:E2))),"") |
Here are the desired results:
Book1 | ||||||
---|---|---|---|---|---|---|
E | F | G | H | |||
1 | Results Desired | Results Desired | Results Desired | Results Desired | ||
2 | Sale | |||||
3 | Credit | Sale | ||||
4 | Credit | Sale | ||||
5 | Auth | Sale | Credit | |||
6 | Auth | Sale | Credit | |||
7 | Auth | Sale | Credit | |||
8 | Auth | Sale | Credit | |||
9 | Sale | Credit | ||||
10 | Sale | Credit | ||||
11 | Auth | |||||
12 | Auth | Sale | ||||
13 | Auth | Sale | ||||
Sheet1 (2) |
Cell Formulas | ||
---|---|---|
Range | Formula | |
E2:H4,E5:G13 | E2 | =IFERROR(INDEX($A$2:$A$13,SMALL(IF($B$2:$B$13=$B2,ROW($B$2:$B$13)-ROW($B$2)+1),COLUMNS($E2:E2))),"") |