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.

Please post a sample sheet showing some data and expected results. Thx

__________________
Using O365 v2503 - Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

Attached is a quick demo. There are 3 worksheets, the first i the master data list. The next 2 are 2 different scenarios to demonstrate what I want to do.

In Scenario 1, the first item (12345) uses options B and D, so the first footnote would be Option B, the second Option D (as in column C), etc down the line:
1: Option B
2: Option D
3: Option C
4: Option A

In Scenario 2, the first item (12348) uses option A, the second uses option C, so the footnotes would change as follows
1: Option A
2: Option C
3: Option B
4: Option D

I hope this illustrates what I'm trying to do. These footnotes would ultimately be added at the end of the description in column B. Ideally, if I can generate a legend, as I did in rows 11-15, that would be great too, but not as important. And if so, it would be great if it only included the footnotes for options that were used in the list.

Thanks for looking at this. If there's anything else that needs clarification, please let me know.

Attached Files

FootnoteDemo.xls (51.0 KB, 17 views)

What I've been trying to think of is if I can add a column which has all of the footnote values ('Option B, Option D', etc.) for that item, then create a list of unique values from that column, and add the index(es) of that list to the description cell for each value in the footnotes column. But I don't know if this will work or not.

I think I've figured out an outline of what I need to do, now I just need to figure out how to do it.

I have added lookup columns on each scenario worksheet for each option. this will give me a range that I can then do an array formula to get the unique items in the order they appear in the list. I have gotten a test to do this, but I haven't gotten it working in the actual files yet. I will need to do some more reading and experimenting to get a handle on the whole array formula concept.

Once I get that working, that will give me the legend showing each footnote with it's number and text.*

From there, I will need to use the values in each rows option lookups to get that text's index in the legend list to add the footnote numbers to the actual row description.


I am pretty confident this will do what I need. Unfortunately, these steps are a little below my current competency level in Excel, so it will take some time learning/experimenting to get this working.

*Edit: I also need to make sure the list includes only non-empty values, which is more complicated than what I have so far.

So far, I have gotten this partially working to where it's usable for now.

I have columns using INDEX/MATCH to get the values of the Options into columns in the scenario worksheets, and I created a range to store the legend (which I enter manually based on their order in the options columns)

From there, I get the proper footnote symbol from the legend using another lNDEX/MATCH for each option column, and this is working properly for me.

The only part that isn't working yet is the compilation of the legend itself (getting the unique non-empty values from all of the option columns). I do want to try to get this working in the future, and I have had some success with a formula in tests, but getting it to work in the actual worksheet hasn't happened yet. For now, it's easy to enter the values in the legend list manually so that i can get this task done.