FILTER and SCAN function

Question

Dear Experts,&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Greetings!&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; I have a data like below(Input worksheet), in Column1, there are 144 Band Combination(1~144) each with a specific Feature sets(&nbsp;In Output-1, I need like below:-and in Output-2, I need like below:-&nbsp;Thanks in Advance,Br,Anupam

patrick2788 · Answer

anupambit1797&nbsp;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.&nbsp;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 &lt;&gt; "",
    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 &lt;&gt; "",
    result, PIVOTBY(band, sets_filled, sets_filled, COUNTA, , , , , , rptfilter),
    result
)

anupambit1797 · Answer

Thanks&nbsp;Patrick2788&nbsp;,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&nbsp;Br,Anupam&nbsp;

patrick2788 · Answer

What is the desired return in J?

anupambit1797 · Answer

HiPatrick2788 , it should be the same as in Col-M in your previous results for Output-1Also, 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..&nbsp;Br,Anupam&nbsp;

patrick2788 · Answer

anupambit1797&nbsp;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 &lt;&gt; "",
    result, GROUPBY(band, sets_filled, ARRAYTOTEXT, , 0, , rptfilter),
    result
)&nbsp;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.

Forum Discussion

FILTER and SCAN function

Share

Resources