I have a worksheet with a master item list (item number, description, options). There are 4 columns which contain optional parameters (A, B, C, D) for each item.

In another worksheet, I have a subset list of items with several calculations to display data via lookups to the master item list (based on item number column). One of the calculations needs to display a footnote number for each of those parameters, but they need to be numbered based on their appearance in the subset list. i.e. if the first item in the list has options B and D, and the second item has option C, then the footnotes will be as follows:

A: 4
B: 1
C: 3
D: 2



If the order changes, then the footnote order needs to be updated based on the new order.

Is there a way to do this in a calculation, or do I need to script this? Additionally, if I can have a calculated summary at the end with the actual footnotes in order (e.g. 1: B, 2: D, etc.) that would be great, but I could do that manually if necessary.