Forum Discussion

Patrick2788's avatar
Patrick2788
Silver Contributor

anupambit1797 

It's a bit of slog to get through this because the data arrangement is not ideal so I must make the data whole with arrays. Even then things may not be perfect.

 

Output1:

=LET(
    bwcs, "BandwidthCombinationSet:",
    band, SCAN("", Table1[Column1], LAMBDA(a, v, IF(LEFT(v, 16) = "Band Combination", v, a))),
    sets, IF(LEFT(Table1[Column1], 24) = bwcs, TEXTAFTER(Table1[Column1], bwcs), ""),
    rptfilter, sets <> "",
    result, GROUPBY(band, sets, ARRAYTOTEXT, , 0, , rptfilter),
    result
)

Output 2:

=LET(
    bwcs, "BandwidthCombinationSet:",
    filldown, LAMBDA(a, v, IF(v = "", a, v)),
    band, SCAN("", Table1[Column1], LAMBDA(a, v, IF(LEFT(v, 16) = "Band Combination", v, a))),
    sets, IF(LEFT(Table1[Column1], 24) = bwcs, TEXTAFTER(Table1[Column1], "'"), ""),
    sets_filled, SCAN("", sets, filldown),
    rptfilter, sets_filled <> "",
    result, PIVOTBY(band, sets_filled, sets_filled, COUNTA, , , , , , rptfilter),
    result
)
anupambit1797's avatar
anupambit1797
Iron Contributor
Oct 16, 2024

Thanks Patrick2788 ,if we use anyother function will it somehow help to reduce the formula, for the Output-1? for example, IFS B=3 then pick 1st 3 from Column "I", and so on 

Br,

Anupam

 

  • Patrick2788's avatar
    Patrick2788
    Silver Contributor
    Oct 16, 2024
    What is the desired return in J?
    • anupambit1797's avatar
      anupambit1797
      Iron Contributor
      Oct 17, 2024

      HiPatrick2788 , it should be the same as in Col-M in your previous results for Output-1

      Also, I noticed that , there seems some issue in the formula as if there's 3 Feature set , then in Col-M it must have 3 entries , but seems there are 4..

       

      Br,

      Anupam

       

      • Patrick2788's avatar
        Patrick2788
        Silver Contributor
        Oct 17, 2024

        anupambit1797 

        If you'd like the aggregation in output 2 to be like the one in output 1 then I switch from PIVOTBY to GROUPBY to aggregate:

        =LET(
    bwcs, "BandwidthCombinationSet:",
    filldown, LAMBDA(a, v, IF(v = "", a, v)),
    band, SCAN("", Table1[Column1], LAMBDA(a, v, IF(LEFT(v, 16) = "Band Combination", v, a))),
    sets, IF(LEFT(Table1[Column1], 24) = bwcs, TEXTAFTER(Table1[Column1], "'"), ""),
    sets_filled, SCAN("", sets, filldown),
    rptfilter, sets_filled <> "",
    result, GROUPBY(band, sets_filled, ARRAYTOTEXT, , 0, , rptfilter),
    result
)

         

        Also, I noticed that , there seems some issue in the formula as if there's 3 Feature set , then in Col-M it must have 3 entries , but seems there are 4..

        Similar to a pivottable, PIVOTBY takes a column field and will only show unique items left-to-right. I believe there's a fourth item (blank) because the data in that column starts with a blank. SCAN was used to fill in the gaps in the data (e.g. If the cell is blank, fill in with accumulated value from above). There's an intial series of blanks until the first feature set comes in and the fill-from-above pattern really begins.

Resources