Assigning List item based on Text line items and looking up - COMPLEX

Hi Community,

To give the backstory of this - I have a module, where I first import one data file into, with Loan-Hedge ID List) and line items as the dimensions. The second file I import is a similar one, also with Loan-Hedge ID (List) and line items as the dimensions.

When importing the first file, these Loan-Hedge IDs will have a Stock Symbol assigned to them, and will also show their assigned Hedges, as Loan-Hedge ID, coming in on the Hedged ID column, as text. In the three columns to the left, named Hedge 1, Hedge 2, and Hedge 3, I basically separate them out individually from the Hedged ID column.

Essentially, what I'm trying to do, with a formula, is to populate the hedges with their associated Stock Symbol if the cell is empty.

Below is the end result I'm trying to achieve:

To note: in blue is the dimension of the column, and in yellow is the format of the column.

If needed, more line items can be added to make it easier.

I really hope the community can help me with this!

Thank you!

Best Answers

  • Amaya
    Answer ✓

    Hello @petterssonbeckman ,

    I think you need to set up a process like this (A ~ F)

    1. Save a view with line items 'Stock Symbol' and 'Hedge 1'.
    2. (A) Import 'Hedge 1' into the Loan Hedge ID list.
    3. (B) Import 'Stock Symbol' from the view.
    4. Save another view with 'Stock Symbol' and 'Hedge 2'.
    5. (C) Import 'Hedge 2' into the Loan Hedge ID list.
    6. (D) Import 'Stock Symbol' from the second view.
    7. Do the same with 'Hedge 3'. (E)(F)

    I hope this works well.


  • Hi,

    You can solve this by dividing stock symbol to two line items final and calculated and then using FIRSTNONBLANK summary to find loans related to hedges. My solution shown in screenshots below:

    Let me know if you have any questions.