Formula to return the previous sequential number from a group

[c_fink]

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:

• 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!

 

[XOR LX]

Hi,

If you're using Excel 2010 or later:

=IF(D2="Y",C2,AGGREGATE(14,6,1/($B$2:$B$9=B2)*($C$2:$C$9<C2)*$C$2:$C$9,1))

<C2)*$C$2:$C$9,1))<C2)*$C$2:$C$9,1))< font><C2)*$C$2:$C$9,1))< font>
Else, array formula**:</C2)*$C$2:$C$9,1))<></C2)*$C$2:$C$9,1))<><C2)*$C$2:$C$9,1))< font><C2)*$C$2:$C$9,1))< font>

[/COLOR]=IF(D2="Y",C2,MAX(IF($B$2:$B$9=B2,IF($C$2:$C$9<C2,$C$2:$C$9))))[COLOR=#0000cd]

</C2)*$C$2:$C$9,1))<><C2)*$C$2:$C$9,1))< font><C2,$C$2:$C$9))))< font>
</C2,$C$2:$C$9))))<></C2)*$C$2:$C$9,1))<></C2)*$C$2:$C$9,1))<><C2)*$C$2:$C$9,1))< font><C2)*$C$2:$C$9,1))< font><C2,$C$2:$C$9))))< font>
Regards</C2,$C$2:$C$9))))<></C2)*$C$2:$C$9,1))<><C2)*$C$2:$C$9,1))< font><C2,$C$2:$C$9))))< font>

</C2,$C$2:$C$9))))<></C2)*$C$2:$C$9,1))<><C2)*$C$2:$C$9,1))< font><C2,$C$2:$C$9))))< font>
**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).</C2,$C$2:$C$9))))<></C2)*$C$2:$C$9,1))<></C2)*$C$2:$C$9,1))<><C2)*$C$2:$C$9,1))< font><C2)*$C$2:$C$9,1))< font><C2,$C$2:$C$9))))< font></SPAN></SPAN></C2,$C$2:$C$9))))<></C2)*$C$2:$C$9,1))<></C2)*$C$2:$C$9,1))<></C2)*$C$2:$C$9,1))

 

[Aladin Akyurek]

F2, control+shift+enter, not just enter, and copy down:

=IF(E2="Y",D2,SMALL(IF($C$2:$C$9=$C2,$D$2:$D$9),COUNTIF($C$2:C2,C2)))

 

[XOR LX]

No idea what the editor's playing at with my last post!

=IF(D2="Y",C2,AGGREGATE(14,6,1/($B$2:$B$9=B2)*($C$2:$C$9<C2)*$C$2:$C$9,1))

=IF(D2="Y",C2,MAX(IF($B$2:$B$9=B2,IF($C$2:$C$9<C2,$C$2:$C$9))))

Regards

 

[XOR LX]

Can someone clarify what's happening to my posts? I used Code tags that time and still it was truncated.

Thanks a lot.

 

[c_fink]

@Aladin: Thanks for the help! I think your formula would work if the data were chronological. It works up until row 6, which is the first out-of-chronological-order date that's not the first transaction within the group. I tried anchoring the COUNTIF statement on the entire column ($B$2:$B$9 instead of $B$2:B2), but it returned incorrect results. Any thoughts?

@XOR: Really wish I could see the rest of the post! Not sure what the issue is... But I'll try to complete the statement; kind of a fun challenge! Thank you!

 

[XOR LX]

Hi,

=IF(D2="Y",C2,AGGREGATE(14,6,1/($B$2:$B$9=B2)*($C$2:$C$9#C2)*$C$2:$C$9,1))

I've tried just about everything to get the editor to accept it. Had to change the "<" sign to a hash "#" - hope it's clear!

Cheers

 

[c_fink]

@XOR: I think I got it! Attempting to paste in below:
=IF(D2="Y",C2,MAX(IF($B$2:$B$9=B2,IF($C$2:$C$9<C2,$C$2:$C$9,""))))

Not sure if I put in the correct FALSE statement at the end (it just returns an empty cell now), but it seems to work. Would love to hear your thoughts. Thank you!

 

[c_fink]

Yeah, the editor doesn't like that less than sign. Using your hash trick, here's what I had (Excel 2007):

=IF(D2="Y",C2,MAX(IF($B$2:$B$9=B2,IF($C$2:$C$9#C2,$C$2:$C$9,""))))
Where the "#" should be swapped with a "<"

Is this how you would structure it for the earlier version of Excel?

 

[markmzz]

Can someone clarify what's happening to my posts? I used Code tags that time and still it was truncated.

Thanks a lot.

Hi XOR LX,

Try this (with spaces - HTML problem):

=IF(D2="Y",C2,MAX(IF($B$2:$B$9=B2,IF($C$2:$C$9 < C2,$C$2:$C$9))))

Markmzz