Windows 7, Excel 2007. I need help with a formula to find the next lowest sequential number within a group.
Here's an example data set, which represents transactions by date and category:
<tbody>
</tbody>
If the current line is the first transaction of the category, the formula should return the value in Col C for that line (so E2 would equal the value in C2, which is 2).
So the first part of the formula could be straightforward, using the helper column:
=IF(D2="Y",C2,
Here's the tricky part: If it's not the first occurrence of the category, then it should return the value of Col C for previous transaction (or the next lowest number in Col C that is within the same category).
I'm thinking I need an array formula to find the minimum value within the category, where that value is less than the value of Col C in the current line. Just not sure how to write that...
Any help would be greatly appreciated!
Here's an example data set, which represents transactions by date and category:
- Col A is a user entered Date which will not be in chronological order (unfortunately a sort is not an option)
- Col B is a user entered Category
- Col C is a formula that gives the chronological sort order based on the date
- The formula in C2:C9 is =COUNT($A$2:$A$9)-(RANK(A2,$A$2:$A$9)+COUNTIF($A$2:A2,A2)-1)+1
- Col D is a formula to determine if the row is the first chronological transaction within the Category
- The array formula in D2:D9 is =IF(MIN(IF($B$2:$B$9=B2,$C$2:$C$9))=C2,"Y","")
- This might not be a necessary column, but can be used as a helper
- Col E is where I would like a formula to give the next lowest value in Col C for the Category in Col B (the order number in Col C for the previous transaction within the same category).
- I have entered in the values that the formula should return in Col E in the table below
| A | B | C | D | E | |
| 1 | Date | Category | Order | First Transaction? | Previous Transaction |
| 2 | 4/1/14 | A | 2 | Y | 2 |
| 3 | 5/1/14 | A | 6 | 4 | |
| 4 | 6/1/14 | A | 8 | 6 | |
| 5 | 4/1/14 | B | 1 | Y | 1 |
| 6 | 5/1/14 | B | 5 | 1 | |
| 7 | 6/1/14 | B | 7 | 5 | |
| 8 | 4/5/14 | C | 3 | Y | 3 |
| 9 | 4/15/14 | A | 4 | 2 |
<tbody>
</tbody>
If the current line is the first transaction of the category, the formula should return the value in Col C for that line (so E2 would equal the value in C2, which is 2).
So the first part of the formula could be straightforward, using the helper column:
=IF(D2="Y",C2,
Here's the tricky part: If it's not the first occurrence of the category, then it should return the value of Col C for previous transaction (or the next lowest number in Col C that is within the same category).
- E3 would return 4, since the previous transaction for Category A was line 9
- C3 = 6, and of all the values of Col C for Category A (2, 4, 6, 8), 4 is the next lowest
- E4 would return 6, since the previous transaction for Category A was line 3
- C4 = 8, and of all the values of Col C for Category A (2, 4, 6, 8), 6 is the next lowest
- E5 would return 1, since it is the first transaction in Category B
- E6 would return 1, since the previous transaction for Category B was line 5
- C6 = 5, and of all the values of Col C for Catebory B (1, 5, 7), 1 is the next lowest
- Etc.
I'm thinking I need an array formula to find the minimum value within the category, where that value is less than the value of Col C in the current line. Just not sure how to write that...
Any help would be greatly appreciated!
