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

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

 

[XOR LX]

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

Ahhh!! Spaces! Thanks a lot, Markmzz!!

 

[XOR LX]

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?

Perfect! Although you don't really need the "" at the end.

Cheers

 

[Aladin Akyurek]

@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?
...

I don't follow. The formula does not look at the dates...

[TABLE="width: 478"]
<COLGROUP><COL style="WIDTH: 73pt; mso-width-source: userset; mso-width-alt: 3470" width=98><COL style="WIDTH: 82pt; mso-width-source: userset; mso-width-alt: 3896" width=110><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 119pt; mso-width-source: userset; mso-width-alt: 5660" width=159><COL style="WIDTH: 156pt; mso-width-source: userset; mso-width-alt: 7395" width=208><TBODY>[TR]
[TD="class: xl63, width: 98, bgcolor: white"]Date[/TD]
[TD="class: xl63, width: 110, bgcolor: white"]Category[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]Order[/TD]
[TD="class: xl63, width: 159, bgcolor: white"]First Transaction?[/TD]
[TD="class: xl63, width: 208, bgcolor: white"]Previous Transaction[/TD]
[/TR]
[TR]
[TD="class: xl64, width: 98, bgcolor: white"]4/1/2014[/TD]
[TD="class: xl63, width: 110, bgcolor: white"]A[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]2[/TD]
[TD="class: xl63, width: 159, bgcolor: white"]Y[/TD]
[TD="class: xl63, width: 208, bgcolor: white"]2[/TD]
[/TR]
[TR]
[TD="class: xl64, width: 98, bgcolor: white"]5/1/2014[/TD]
[TD="class: xl63, width: 110, bgcolor: white"]A[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]6[/TD]
[TD="class: xl63, width: 159, bgcolor: white"] [/TD]
[TD="class: xl63, width: 208, bgcolor: white"]4[/TD]
[/TR]
[TR]
[TD="class: xl64, width: 98, bgcolor: white"]6/1/2014[/TD]
[TD="class: xl63, width: 110, bgcolor: white"]A[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]8[/TD]
[TD="class: xl63, width: 159, bgcolor: white"] [/TD]
[TD="class: xl63, width: 208, bgcolor: white"]6[/TD]
[/TR]
[TR]
[TD="class: xl64, width: 98, bgcolor: white"]4/1/2014[/TD]
[TD="class: xl63, width: 110, bgcolor: white"]B[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]1[/TD]
[TD="class: xl63, width: 159, bgcolor: white"]Y[/TD]
[TD="class: xl63, width: 208, bgcolor: white"]1[/TD]
[/TR]
[TR]
[TD="class: xl64, width: 98, bgcolor: white"]5/1/2014[/TD]
[TD="class: xl63, width: 110, bgcolor: white"]B[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]5[/TD]
[TD="class: xl63, width: 159, bgcolor: white"] [/TD]
[TD="class: xl63, width: 208, bgcolor: white"]5[/TD]
[/TR]
[TR]
[TD="class: xl64, width: 98, bgcolor: white"]6/1/2014[/TD]
[TD="class: xl63, width: 110, bgcolor: white"]B[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]7[/TD]
[TD="class: xl63, width: 159, bgcolor: white"] [/TD]
[TD="class: xl63, width: 208, bgcolor: white"]7[/TD]
[/TR]
[TR]
[TD="class: xl64, width: 98, bgcolor: white"]4/5/2014[/TD]
[TD="class: xl63, width: 110, bgcolor: white"]C[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]3[/TD]
[TD="class: xl63, width: 159, bgcolor: white"]Y[/TD]
[TD="class: xl63, width: 208, bgcolor: white"]3[/TD]
[/TR]
[TR]
[TD="class: xl64, width: 98, bgcolor: white"]4/15/2014[/TD]
[TD="class: xl63, width: 110, bgcolor: white"]A[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]4[/TD]
[TD="class: xl63, width: 159, bgcolor: white"] [/TD]
[TD="class: xl63, width: 208, bgcolor: white"]8[/TD]
[/TR]
</TBODY>[/TABLE]

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

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

What am I missing?

 

[markmzz]

Ahhh!! Spaces! Thanks a lot, Markmzz!!

You are welcome.

Markmzz