Formula to return the previous sequential number from a group

c_fink

New Member
Joined
Oct 22, 2008
Messages
9
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

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Date[/TD]
[TD]Category[/TD]
[TD]Order[/TD]
[TD]First Transaction?[/TD]
[TD]Previous Transaction[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]4/1/14[/TD]
[TD]A[/TD]
[TD]2[/TD]
[TD]Y[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]5/1/14[/TD]
[TD]A[/TD]
[TD]6[/TD]
[TD][/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]6/1/14[/TD]
[TD]A[/TD]
[TD]8[/TD]
[TD][/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]4/1/14[/TD]
[TD]B[/TD]
[TD]1[/TD]
[TD]Y[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]5/1/14[/TD]
[TD]B[/TD]
[TD]5[/TD]
[TD][/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]6/1/14[/TD]
[TD]B[/TD]
[TD]7[/TD]
[TD][/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]4/5/14[/TD]
[TD]C[/TD]
[TD]3[/TD]
[TD]Y[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]4/15/14[/TD]
[TD]A[/TD]
[TD]4[/TD]
[TD][/TD]
[TD]2[/TD]
[/TR]
</tbody>[/TABLE]

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!
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
Hi,

If you're using Excel 2010 or later:

Code:
=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>

Code:
[/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))
 
Last edited:
Upvote 0
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)))
 
Upvote 0
No idea what the editor's playing at with my last post!

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

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

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

Thanks a lot.
 
Upvote 0
@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!
 
Upvote 0
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
 
Upvote 0
@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!
 
Upvote 0
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?
 
Upvote 0
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):

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

Markmzz
 
Upvote 0

Forum statistics

Threads
1,223,718
Messages
6,174,082
Members
452,542
Latest member
Bricklin

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
Back
Top