User profile for user: frankiecarl
frankiecarl Author
User level: Level 1
8 points

Formula to find row number of previous record of a specific stock/Company

Hi, altogether.

I am using Numbers 4.3.1 to manage my stock portfolio. I used Google sheets before but really like to switch over to Numbers after I got my new Mac. I have solve many formulas already from reading this forum, but I kinda stuck with this one (and I guess I can be stuck with others as well later 🙂 )


In my transaction table, I like to have a cell to show ‘Previous Row’ of last transition of a specific stock. As seen in the image, Johnson & Johnson (C2) are recorded as “0” in H2. In row 8, I like Johnson & Johnson to show “2” in H8.

User uploaded file

In my previous Google sheets the formula in cell H2 is =if(isblank(B2),,arrayformula(max(if($C$1:$C1=C2,row($C$1:$C1))))).

If “Type” of transaction is blank, notting happen. But if something is recorded in B2 it looks for “C2” in a range of cell, and then look for the highest row number it is recorded. I guess you guys can see what the formula is.


Below that cell, in cell H3, the formula changed to =if(isblank(B3),,arrayformula(max(if($C$1:$C2=C3,row($C$1:$C2))))) and so on.

In Numbers, I tried =IF(ISBLANK(B3);"";MAX(IF(C$1:C$2=C3;ROW(C$1:C$2);if-false))), but it is obviously wrong. The message I get is "The range C1:C2 can't be used as a single value." So, how do I get around?


Note: !!The numbers you see in row H is “hard-typed” by me in sake of other formulas.!!


Thank you

frankiecarl

MacBook Pro TouchBar and Touch ID, macOS High Sierra (10.13.2)

Posted on Jan 20, 2018 10:36 PM

Reply
Question marked as Top-ranking reply
User profile for user: Barry
Barry
User level: Level 9
60,023 points

Posted on Jan 21, 2018 4:24 AM

Hi frankiecarl,


Here's a possible solution:



Cell H2 contains the formula below, filled down to the rest of the cells in column H.

User uploaded file

H2: IF(AND(LEN(B2)>0,COUNTIF(C$1:C2,C2)>1),MATCH(C2,C$1:C1,0),"")


IF tests the two conditions in AND. IF both are true, And returns true, and If calls MATCH. IF either is false, AND returns false, and IF returns a null string ( "" ) which appears as an empty cell.


Conditions:

  • The LENgth of the entry in B2 is more than zero characters (ie. something has been entered in the 'type' column)
  • The COUNT of this stock from the top of column C to 'this row' of column C is greater than 1 (ie. there is at least one previous occurrence to search for).


MATCH: MATCH gets the value (stock name) from 'this row' of column C, then searches for it in all rows of column C from row 1 to the row above 'this row', and returns a number indicating the position of the first occurrence it finds (ie. the row number) in the list of values in that range of cells.


I've used MATCH here because (despite what the description in the function browser says) MATCH searches from the top or from the bottom, depending on which matching-method you set. Set to 'find value' or to 'find largest value', where an exact match is always available, MATCH appears to search from the bottom of the column to the top, and return the first exact match it finds. Set to 'find smallest value', the search appears to take place in the opposite direction.


Tested with the added transactions in row 10 (which correctly returns 8 with Match set to 'find value' or 'find largest' but returns 2 with MATCH set to 'find smallest'), and row 11, where there is no result as a type has not been entered.


Regards,

Barry

View in context
2 replies
Question marked as Top-ranking reply
User profile for user: Barry
Barry
User level: Level 9
60,023 points

Jan 21, 2018 4:24 AM in response to frankiecarl

Hi frankiecarl,


Here's a possible solution:



Cell H2 contains the formula below, filled down to the rest of the cells in column H.

User uploaded file

H2: IF(AND(LEN(B2)>0,COUNTIF(C$1:C2,C2)>1),MATCH(C2,C$1:C1,0),"")


IF tests the two conditions in AND. IF both are true, And returns true, and If calls MATCH. IF either is false, AND returns false, and IF returns a null string ( "" ) which appears as an empty cell.


Conditions:

  • The LENgth of the entry in B2 is more than zero characters (ie. something has been entered in the 'type' column)
  • The COUNT of this stock from the top of column C to 'this row' of column C is greater than 1 (ie. there is at least one previous occurrence to search for).


MATCH: MATCH gets the value (stock name) from 'this row' of column C, then searches for it in all rows of column C from row 1 to the row above 'this row', and returns a number indicating the position of the first occurrence it finds (ie. the row number) in the list of values in that range of cells.


I've used MATCH here because (despite what the description in the function browser says) MATCH searches from the top or from the bottom, depending on which matching-method you set. Set to 'find value' or to 'find largest value', where an exact match is always available, MATCH appears to search from the bottom of the column to the top, and return the first exact match it finds. Set to 'find smallest value', the search appears to take place in the opposite direction.


Tested with the added transactions in row 10 (which correctly returns 8 with Match set to 'find value' or 'find largest' but returns 2 with MATCH set to 'find smallest'), and row 11, where there is no result as a type has not been entered.


Regards,

Barry

Reply

This thread has been closed by the system or the community team. You may vote for any posts you find helpful, or search the Community for additional answers.

Formula to find row number of previous record of a specific stock/Company

Welcome to Apple Support Community
A forum where Apple customers help each other with their products. Get started with your Apple Account.
Learn more Sign up