Recursive filter

[feaugcruz]

Helllo guys.
I am working with "unit cost compositions"; these compositions contain inputs (which are the lowest possible level) and/or may include auxiliary compositions. These auxiliary compositions contain inputs and/or may include other auxiliary compositions, and so on.
My goal is to ensure that only inputs remain. To achieve this, I need to multiply the index of the main composition by the index of the auxiliary composition.
I have achieved this goal in the attached file, but I would like to obtain the result more quickly, without having to follow a step-by-step process.
About the attached file:
In the attached file, in the "DATA" tab, which is my data input, I have the main composition in column "A", the input or auxiliary composition in column "B", and in column "C", the index/incidence of the item in column "B" within "A".
Through the step-by-step process in the following tabs, I reached the desired result in the "END" tab, between columns "E" and "G".
I appreciate your help.


https://docs.google.com/spreadsheets/d/16w67Hcevkc5R7JJSvyrMOpuQ4ekn385h/edit?rtpof=true&sd=true

 

[djc]

Try the following (it returned the desired results in < 8 seconds on my system):

=LET(
   cc, CHOOSECOLS,
   tc, LAMBDA(a,i, TOCOL(TEXTBEFORE(TEXTAFTER(a, "|", i), "|"), 2)),
   rf, LAMBDA(me,array,[k],
      LET(
         field1, cc(array, 1),
         field2, cc(array, 2),
         values, cc(array, 3),
         k, IF(ISOMITTED(k), UNIQUE(SORT(field1)), k),
         b, ISNUMBER(XMATCH(field2, k,, 2)),
         IF(
            OR(b),
            LET(
               arr, FILTER(array, b),
               two, cc(arr, 2),
               lbl, MAP(two, LAMBDA(t, TEXTJOIN("|", 0, "", FILTER(field2, field1 = t, t), ""))),
               val, MAP(two, cc(arr, 3), LAMBDA(t,v, LET(a, FILTER(values, field1 = t, 1) * v, TEXTJOIN("|", 0, ROWS(a), a, "")))),
               n, --TEXTBEFORE(val, "|"),
               j, SEQUENCE(, MAX(n)),
               me(me, VSTACK(HSTACK(TOCOL(IFS(n >= j, cc(arr, 1)), 2), tc(lbl, j), --tc(val, j)), FILTER(array, NOT(b))), k)
            ),
            GROUPBY(TAKE(array,, 2), values, SUM, 0, 0)
         )
      )
   ),
   rf(rf, A2:C2280)
)

 

[p45cal]

In the linked-to workbook below:

• Only your DATA sheet
• A results table at cell E1
• A copy of your 'desired result in the "END" tab, between columns "E" and "G" ' at cell I1 (values only so that I could sort it and the results table in the same way to demonstrate similarity).

This is a Power Query solution. If you change the data in your source data you'll need to refresh the results table by right-clicking somewhere in the results table and choosing Refresh.
You can see how long it takes to refresh (recalculate) by deleting some of the data in the results table, then refreshing it, which will cause the results to reappear (here it was about 4 seconds on a 10 year old PC)
Note that the query doesn't truly recurse; it does like you did and repeats operations until I found no new matches. If your data has more 'depth' to it than your demonstration file it's easy enough to add a few more steps into the query to cater for more depth than you'll ever expect to encounter; it'll make little difference to the speed.
Note also that I've reset calculation to automatic since there are no formulae at all that need recalculating, showing that the workbook is not resource hungry.

Link to workbook: https://app.box.com/s/ztllp5dm7lhmk5h5x7zxybcankdsrgh8


Edit: Oh, I see you've cross posted, here's the message I usually put out when this happens:

cross posted without links:

Recursive data extraction

Hello guys. I'm analyzing some unit price compositions and I need to extract labor hours, equipment hours, and materials. Some compositions have only these 3 items, but most of them have auxiliary compositions, that sometimes have other auxiliary compositions and the process becomes recursive...

www.mrexcel.com

@feaugcruz , for your information, you should always provide links to your cross posts.
This is a requirement, not just a request.
If you have cross posted at other places, please add links to them too.
Why? Have a read of https://excelguru.ca/a-message-to-forum-cross-posters/

 

[djc]

Try the following (it returned the desired results in < 8 seconds on my system)...

Version 2, improved to approx. 4 seconds (see attached):

=LET(
   cc, CHOOSECOLS,
   tc, LAMBDA(a,i, TOCOL(TEXTBEFORE(TEXTAFTER(a, "|", i), "|"), 2)),
   rf, LAMBDA(me,array,[k],
      LET(
         field1, cc(array, 1),
         field2, cc(array, 2),
         values, cc(array, 3),
         k, IF(ISOMITTED(k), UNIQUE(SORT(field1)), k),
         b, ISNUMBER(XMATCH(field2, k,, 2)),
         IF(
            OR(b),
            LET(
               a, FILTER(array, b),
               x, MAP(cc(a, 1), cc(a, 2), cc(a, 3),
                  LAMBDA(one,two,val,
                     LET(
                        c, field1 = two,
                        t, FILTER(field2, c, two),
                        v, FILTER(values, c, 1) * val,
                        n, ROWS(t),
                        o, EXPAND(one, n,, one),
                        TEXTJOIN("|", 0, n, o, "/", t, "/", v, "")
                     )
                  )
               ),
               j, SEQUENCE(, MAX(--TEXTBEFORE(x, "|"))),
               o, tc(TEXTBEFORE(x, "/"), j),
               t, tc(TEXTBEFORE(TEXTAFTER(x, "/"), "/"), j),
               v, --tc(TEXTAFTER(x, "/", 2), j),
               me(me, VSTACK(HSTACK(o, t, v), FILTER(array, NOT(b))), k)
            ),
            GROUPBY(TAKE(array,, 2), values, SUM, 0, 0)
         )
      )
   ),
   rf(rf, A2:C2280)
)