2D Dynamic Filtering out blanks using ByRow() or Map()

[vharquiteto]

I have a 2D dynamic array done that perfect align the data set between two axes, DATES and OC (thats like serial number), and in the intersection of them, the STATUS of that instance/OC at that DATE; But when there is no change on the STATUS there is all those blank cells i need to take out!

Solved non-dynamically by brute forcing this in all lines:
=IF(BF3<>"";FILTER(CHOOSEROWS($BG$3#;BE3);CHOOSEROWS($BG$3#;BE3)<>"");"")
but i am having huge performance issues, probably because of the number of repeating operations alone!
Also need to solve it with a single formula that can be expanded when data becomes bigger...

I know there is some way of doing this by using MAP() or BYROW() functions, but i am really knew to LAMBDA() functions right now to solve it by myself... (triyed solving for hole two days with no success!, all i got was #CALC and #VALUE errors all day) i don´t even know if this is the correct way to do it, its just a feeling, but i am too young in excel to figure this out w/o your help!

 

[Peter Bartholomew]

If one can get away with a straightforward dynamic array solution that will broadcast over the 2D array of results, that is the simplest solution. In this case it might mean adding a sequence number as a helper column in the original table. A possible solution might then be

= LET(
    idx, SUMIFS(SEQ, DATA, DATAX, OC, OCX),
    IF(idx, INDEX(STATUS, idx), "" )
  )

where DATAX and OCX are row and column headers for your output crosstab.

The problem with Lambda helper function is that they were poorly specified for reasons of backward compatibility and, with the exception of MAP and MAKEARRAY, do not return nested arrays. To get around this I recently wrote a Lambda function that generalises MAP so that it will combine row and column headers to form the criteria for a 2D map. The function XMAP

XMAP(x, y, Fnλ)

= LAMBDA(x, y, Fnλ,
    MAP(Broadcastλ(x, y), Broadcastλ(y, x), Fnλ)
  )

in turn calls a further Lambda function

Broadcastλ

= LAMBDA(x, y,
    IF(SIGN(y), x)
  )

whilst passing the lookup routine itself as a parameter

CrosstabLookupλ

= LAMBDA(mappedDATA, mappedOC,
    LET(
        filteredOC,     FILTER(OC, DATA = mappedDATA),
        filteredStatus, FILTER(STATUS, DATA = mappedDATA),
        XLOOKUP(mappedOC, filteredOC, filteredStatus, "")
    )
  )

The worksheet function is

= XMAP(DATAX, OCX, CrosstabLookupλ)

 

[vharquiteto]

If one can get away with a straightforward dynamic array solution that will broadcast over the 2D array of results, that is the simplest solution. In this case it might mean adding a sequence number as a helper column in the original table. A possible solution might then be

= LET(
    idx, SUMIFS(SEQ, DATA, DATAX, OC, OCX),
    IF(idx, INDEX(STATUS, idx), "" )
  )

where DATAX and OCX are row and column headers for your output crosstab.

The problem with Lambda helper function is that they were poorly specified for reasons of backward compatibility and, with the exception of MAP and MAKEARRAY, do not return nested arrays. To get around this I recently wrote a Lambda function that generalises MAP so that it will combine row and column headers to form the criteria for a 2D map. The function XMAP

XMAP(x, y, Fnλ)

= LAMBDA(x, y, Fnλ,
    MAP(Broadcastλ(x, y), Broadcastλ(y, x), Fnλ)
  )

in turn calls a further Lambda function

Broadcastλ

= LAMBDA(x, y,
    IF(SIGN(y), x)
  )

whilst passing the lookup routine itself as a parameter

CrosstabLookupλ

= LAMBDA(mappedDATA, mappedOC,
    LET(
        filteredOC,     FILTER(OC, DATA = mappedDATA),
        filteredStatus, FILTER(STATUS, DATA = mappedDATA),
        XLOOKUP(mappedOC, filteredOC, filteredStatus, "")
    )
  )

The worksheet function is

= XMAP(DATAX, OCX, CrosstabLookupλ)

OW MAN! THATS BEALTIFUL! i cant wait to test this solution!

 

[Peter Bartholomew]

I have just noticed that Broadcastλ was written for numbers. For text it would need to be

= LAMBDA(x, y,
    IF(y<>"", x)
  )

 

[vharquiteto]

If one can get away with a straightforward dynamic array solution that will broadcast over the 2D array of results, that is the simplest solution. In this case it might mean adding a sequence number as a helper column in the original table. A possible solution might then be

= LET(
    idx, SUMIFS(SEQ, DATA, DATAX, OC, OCX),
    IF(idx, INDEX(STATUS, idx), "" )
  )

where DATAX and OCX are row and column headers for your output crosstab.

The problem with Lambda helper function is that they were poorly specified for reasons of backward compatibility and, with the exception of MAP and MAKEARRAY, do not return nested arrays. To get around this I recently wrote a Lambda function that generalises MAP so that it will combine row and column headers to form the criteria for a 2D map. The function XMAP

XMAP(x, y, Fnλ)

= LAMBDA(x, y, Fnλ,
    MAP(Broadcastλ(x, y), Broadcastλ(y, x), Fnλ)
  )

in turn calls a further Lambda function

Broadcastλ

= LAMBDA(x, y,
    IF(SIGN(y), x)
  )

whilst passing the lookup routine itself as a parameter

CrosstabLookupλ

= LAMBDA(mappedDATA, mappedOC,
    LET(
        filteredOC,     FILTER(OC, DATA = mappedDATA),
        filteredStatus, FILTER(STATUS, DATA = mappedDATA),
        XLOOKUP(mappedOC, filteredOC, filteredStatus, "")
    )
  )

The worksheet function is

= XMAP(DATAX, OCX, CrosstabLookupλ)

All right! checked everything. you did solved the crosstab way better than i did before using
=XLOOKUP(BF3#&$BG$2#;CHOOSECOLS(filtrados_ML;2)&CHOOSECOLS(filtrados_ML;1);CHOOSECOLS(filtrados_ML;12);"")

but the thing i needed was, when that is done, how do i take out all the blanks row-by-row; I took your file thats way simplified than mine to explain:

 

[p45cal]

A start to another possible solution, and a bit of a guess as to what you want:
In column BB and to the right, 3 pivot tables.
I've removed all your formulae because they were recalculating all the time.
I've highlighted 31.21.099373.01 all over the sheet so you can compare.
On the right track?

 

[Peter Bartholomew]

All right! checked everything. you did solved the crosstab way better than i did before using
=XLOOKUP(BF3#&$BG$2#;CHOOSECOLS(filtrados_ML;2)&CHOOSECOLS(filtrados_ML;1);CHOOSECOLS(filtrados_ML;12);"")

but the thing i needed was, when that is done, how do i take out all the blanks row-by-row; I took your file thats way simplified than mine to explain:

In a way the problem is simpler now because the crosstab lookup by date is no longer required. The problem is now that the result is a nested array which, at least for now, is not allowed. I used REDUCE/VSTACK to return the result as a single array,

= StatusHistoryλ(OC, STATUS)

=REDUCE(
    {"Distinct OC", "1º auction", "2º auction", "3º auction"},
    UNIQUE(oc),
    LAMBDA(acc, singleOC,
        LET(
            OCStatus,   FILTER(status, oc = singleOC, ""),
            OCTrimmed,  HSTACK(singleOC, TRANSPOSE(EXPAND(TAKE(OCStatus, -3), 3, , ""))),
            VSTACK(acc, OCTrimmed)
        )
    )
)

It returns the entire result array including headings as one spilt range.

 

[vharquiteto]

A start to another possible solution, and a bit of a guess as to what you want:
In column BB and to the right, 3 pivot tables.
I've removed all your formulae because they were recalculating all the time.
I've highlighted 31.21.099373.01 all over the sheet so you can compare.
On the right track?

Good mourning sir! thx for your time! will check this right now!

A start to another possible solution, and a bit of a guess as to what you want:
In column BB and to the right, 3 pivot tables.
I've removed all your formulae because they were recalculating all the time.
I've highlighted 31.21.099373.01 all over the sheet so you can compare.
On the right track?

Finally checked everything. Pivot table solutions do work! the problem here is ppl need to keep it runing (all the time, like you said) they want all updates to perform live, even thou you only need to press a button to uptade all pivot tables and that actually solves the performance issues, it defeat the point of the live update! The file is actually way deeper than the section i put avaliable here, and it needs to keep runing the calculations from before to after this section i sent here;

Unfortunatelly, i will need to keep searching for solutions to run this with dynamic array formulas for now. But i can´t express in words how thankful i am for your time sir! really appreciate the inside of solving it though pivot tables! Maybe on the future i can convince people here thats the way to go;

 

[vharquiteto]

In a way the problem is simpler now because the crosstab lookup by date is no longer required. The problem is now that the result is a nested array which, at least for now, is not allowed. I used REDUCE/VSTACK to return the result as a single array,

= StatusHistoryλ(OC, STATUS)

=REDUCE(
    {"Distinct OC", "1º auction", "2º auction", "3º auction"},
    UNIQUE(oc),
    LAMBDA(acc, singleOC,
        LET(
            OCStatus,   FILTER(status, oc = singleOC, ""),
            OCTrimmed,  HSTACK(singleOC, TRANSPOSE(EXPAND(TAKE(OCStatus, -3), 3, , ""))),
            VSTACK(acc, OCTrimmed)
        )
    )
)

It returns the entire result array including headings as one spilt range.

waw! bigbrain aswer! can´t wait to check this!

 

[vharquiteto]

In a way the problem is simpler now because the crosstab lookup by date is no longer required. The problem is now that the result is a nested array which, at least for now, is not allowed. I used REDUCE/VSTACK to return the result as a single array,

= StatusHistoryλ(OC, STATUS)

=REDUCE(
    {"Distinct OC", "1º auction", "2º auction", "3º auction"},
    UNIQUE(oc),
    LAMBDA(acc, singleOC,
        LET(
            OCStatus,   FILTER(status, oc = singleOC, ""),
            OCTrimmed,  HSTACK(singleOC, TRANSPOSE(EXPAND(TAKE(OCStatus, -3), 3, , ""))),
            VSTACK(acc, OCTrimmed)
        )
    )
)

It returns the entire result array including headings as one spilt range.

Yep! Definitly solved!
i just need to work on it only working on the inside array results (w/o) headings; because i will have to filter only VEICULO & SUCATA results after this. and sort if by occurrencies! it will work on your formula to make this happen! thx for the solution man! it was awesome to pick your brain a bit and see how you work on this solution! I also learned alot more about LET and LAMBDA functions, also learned alot about excel limitations with nested arrays when it needs to get multiple results between the filters row-by-row; I can only wait for them to solve this in the future. till then i will use different variations of the solution you presented to solve this issue from now on. its not perfect, but it works so, no complains!

 

[Peter Bartholomew]

DROP and TAKE make it easy to recover headers or, alternatively, to strip them off tabular datasets.