How to generate a Version Number based on a Key

Comments

2 comments

  • Official comment
    Saurabh Agashe

    With your example data proceed like this:

    Let's name the sheet, which contains your input, Data.

    Create a new sheet, and on the first column GROUPBY(#Data!Id). On the second column use the function GROUPCONCAT(#Data!ChangeDateTime;#Data!ChangeDateTime) This will create a list with the elements corresponding to each Id group sorted by DateTime. Rename this sheet to ListedData

    Create a new sheet and on the first column GROUPBY(#ListedData!Id)

    On the second column use the function EXPAND(#ListedData!ChangeDateTime) This function will kind of re-generate the input list, but sorted by Id and by DateTime.

    On the third column use the function INDEXOF(#ListedData!ChangeDateTime;#ChangeDateTime)+1 This function uses very conveniently the index of the list we created on the sheet ListedData to enumerate the changes sorted by DateTime (the order in which the list was ordered with the GROUPCONCAT() function).

    Whereas sorting the columns Id and ChangeDateTime could have been done by sorting them, the creation of an intermediate list, give us access to the list indexes. These indexed can then be used to create the versionNumber values, which you are looking for.

    Comment actions Permalink
  • Saurabh Agashe

    Brilliant!

    Thanks.

    0
    Comment actions Permalink

Please sign in to leave a comment.